Question 22 - DEA-C01 discussion

Why Should I Use Secure Views?

For a non-secure view, internal optimizations can indirectly expose data.

Some of the internal optimizations for views require access to the underlying data in the base tables for the view. This access might allow data that is hidden from users of the view to be exposed through user code, such as user-defined functions, or other programmatic methods. Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.

For a non-secure view, the view definition is visible to other users.

By default, the query expression used to create a standard view, also known as the view definition or text, is visible to users in various commands and interfaces.

For security or privacy reasons, you might not wish to expose the underlying tables or internal structural details for a view. With secure views, the view definition and details are visible only to authorized users (i.e. users who are granted the role that owns the view).

When Should I Use a Secure View?

Views should be defined as secure when they are specifically designated for data privacy (i.e. to limit access to sensitive data that should not be exposed to all users of the underlying table(s)).

Secure views should not be used for views that are defined solely for query convenience, such as views created to simplify queries for which users do not need to understand the underlying data representation. Secure views can execute more slowly than non-secure views.

Secure views are defined using the SECURE keyword with the standard DDL for views:

To create a secure view, specify the SECURE keyword in the CREATE VIEW or CREATE MA-TERIALIZED VIEW command.

To convert an existing view to a secure view and back to a regular view, set/unset the SECURE keyword in the ALTER VIEW or ALTER MATERIALIZED VIEW command.

The definition of a secure view is only exposed to authorized users (i.e. users who have been granted the role that owns the view). If an unauthorized user uses any of the following commands or in-terfaces, the view definition is not displayed:

· SHOW VIEWS and SHOW MATERIALIZED VIEWS commands.

· GET_DDL utility function.

· VIEWS Information Schema view.

· VIEWS Account Usage view.

For non-materialized views, the IS_SECURE column in the Information Schema and Account Us-age views identifies whether a view is secure.

The internals of a secure view are not exposed in Query Profile (in the web interface). This is the case even for the owner of the secure view, because non-owners might have access to an owner's Query Profile.