Oracle Security

Views

To refresh your memory, a view is a description of how data is to be retrieved from the underlying tables. It does not store data but it is treated as though it were a table in SQL statements. Views can be used to perform the following actions:

• Limit the rows accessible to a user (row-level security)

• Limit specific columns accessible to a user (column-level security)

• Pre-join several tables (removing the requirement that the user understand the complexity of joining tables)

Most of the views we discuss here are used for row-level security. One way to achieve row-level security is to use views that include qualifying conditions based on some characteristic of the user login id. If the table is never to be accessed via a query tool, then application-implemented security can be used. Even this form of security usually relies on the same login id characteristics.

In this section we discuss the logic used to determine the structure of the views, and we provide examples of how the logic is incorporated into the view definitions.

View Syntax

The view definitions we provide all use the command “SELECT *” for clarity. The * means retrieve all columns. In a real system, however, you would replace the * with a list of specific column names. The columns in the view take their names from the columns in the tables from which they are selected, but you can provide aliases for these columns. There are two syntax constructs you can use to declare column aliases in a view: with one you declare ...