Encrypting and decrypting materialized views

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.

Sybase Central

To encrypt a materialized view (Sybase Central)

  1. Connect to the database as the owner of the view, or as a user with DBA authority.

  2. In the left pane, double-click Views.

  3. Right-click the materialized view and choose Properties.

  4. Click the Miscellaneous tab.

  5. Select the Materialized View Data Is Encrypted check box.

  6. Click OK.

To decrypt a materialized view (Sybase Central)

  1. Connect to the database as the owner of the view, or as a user with DBA authority.

  2. In the left pane, double-click Views.

  3. Right-click the materialized view and choose Properties.

  4. Click the Miscellaneous tab.

  5. Clear the Materialized View Data Is Encrypted check box.

  6. Click OK.

SQL statements

To encrypt a materialized view (SQL)

  1. Connect to the database as a user with DBA authority, or as owner of the materialized view.

  2. Execute an ALTER MATERIALIZED VIEW statement using the ENCRYPTED clause.

To decrypt a materialized view (SQL)

  1. Connect to the database as a user with DBA authority, or as owner of the materialized view.

  2. Execute an ALTER MATERIALIZED VIEW statement using the NOT ENCRYPTED clause.

Examples

The following statement creates, initializes, and then encrypts the EmployeeConfid44 materialized view. The database must already be configured to allow encrypted tables for this statement to work: 

CREATE MATERIALIZED VIEW EmployeeConfid44 AS
   SELECT EmployeeID, Employees.DepartmentID, SocialSecurityNumber, Salary, ManagerID,
      Departments.DepartmentName, Departments.DepartmentHeadID
   FROM Employees, Departments
   WHERE Employees.DepartmentID=Departments.DepartmentID;
REFRESH MATERIALIZED VIEW EmployeeConfid44;
ALTER MATERIALIZED VIEW GROUPO.EmployeeConfid44 ENCRYPTED;

The following statement decrypts the EmployeeConfid44 materialized view:

ALTER MATERIALIZED VIEW GROUPO.EmployeeConfid44 NOT ENCRYPTED;
Caution

When you are done with this example, you should drop the materialized view you created. Otherwise, you will not be able to make schema changes to its underlying tables Employees and Departments, when trying out other examples. You cannot alter the schema of a table that has enabled, dependent materialized view. See Dropping materialized views.

See also
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0