|
Materialized views can be encrypted for additional security. For example, if a materialized view contains data that was encrypted in the underlying table, you may want to encrypt the materialized view as well. Table encryption must already be enabled in the database in order to encrypt a materialized view. The encryption algorithm and key specified at database creation are used to encrypt the materialized view. To see the encryption settings in effect for your database, including whether table encryption is enabled, query the Encryption database property using the DB_PROPERTY function, as follows:
SELECT DB_PROPERTY( 'Encryption' );
As with table encryption, encrypting a materialized view can impact performance since the database server must decrypt data it retrieves from the view.
Connect to the database as the DBA.
Open the Views folder and right-click the materialized view.
Select Properties.
The Properties page for the materialized view displays.
On the Miscellaneous tab, place a checkmark next to View data is encrypted, and then choose OK.
Connect to the database as the DBA.
Open the Views folder and right-click the materialized view.
Select Properties.
The Properties page for the materialized view displays.
On the Miscellaneous tab, remove the checkmark next to View data is encrypted, and then choose OK.
Connect to the database as the DBA, or as owner of the materialized view.
Execute an ALTER MATERIALIZED VIEW statement using the ENCRYPTED clause.
Connect to the database as the DBA, or as owner of the materialized view.
Execute an ALTER MATERIALIZED VIEW statement using the NOT ENCRYPTED clause.
The following statement encrypts the EmployeeConfidential materialized view. Note that the database must already be configured to allow encrypted tables for this statement to work:
ALTER MATERIALIZED VIEW EmployeeConfidential ENCRYPTED;
The following statement decrypts the EmployeeConfidential materialized view:
ALTER MATERIALIZED VIEW EmployeeConfidential NOT ENCRYPTED;