Back in the days of Oracle7, Oracle security was a relatively trivial matter. Individual access privileges were granted to individual users, and this simple coupling of privileges-to-users comprised the entire security scheme of the Oracle database. However, with Oracle's expansion into enterprise data security, the scope of Oracle security software has broadened.

Oracle9i has a wealth of security options, and these options are often bewildering to the IT manager who is charged with ensuring data access integrity. These Oracle tools include role-based security, Virtual Private Databases (VPD) security, and grant execute security:

• Role-based security — Specific object-level and system-level privileges are grouped into roles and granted to specific database users. Object privileges can be grouped into roles, which can then be assigned to specific users.
• Virtual private databases — VPD technology can restrict access to selected rows of tables. Oracle Virtual Private Databases (fine-grained access control) allows for the creation of policies that restrict table and row access at runtime.
• Grant-execute security — Execution privileges on procedures can be tightly coupled to users. When a user executes the procedures, they gain database access, but only within the scope of the procedure. Users are granted execute privileges on functions and stored procedures. The grantee takes on the authority of the procedure owner when executing the procedures, but has no access outside the procedure.

Regardless of the tool, it is the job of the Oracle manager to understand the uses of these security mechanisms and their appropriate use within an Oracle environment. At this point, it's very important to note that all of the Oracle security tools have significant overlapping functionality. When the security administrator mixes these tools, it is not easy to tell which specific end users have access to what part of the database.

For example, the end user who's been granted execution privileges against a stored procedure will have access to certain database entities, but this will not be readily apparent from any specific role-based privileges that that user has been granted. Conversely, an individual end user can be granted privileges for a specific database role, but that role can be bypassed by the use of Oracle's Virtual Private Database (VPD) technique.

In sum, each of the three Oracle security methods provides access control to the Oracle database, but they each do it in very different ways. The concurrent use of any of these products can create a nightmarish situation whereby an Oracle security auditor can never know exactly who has access to what specific database entities.

Let's begin by reviewing traditional role-based Oracle security.

Traditional Oracle Security

Data-level security is generally implemented by associating a user with a "role" or a "subschema" view of the database. These roles are profiles of acceptable data items and operations, and the role profiles are checked by the database engine at data request time (refer to figure 1).

Oracle's traditional role-based security comes from the standard relational database model. In all relational databases, specific object- and system-level privileges can be created, grouped together into roles, and then assigned to individual users. This method of security worked very well in the 1980s and 1990s, but has some significant shortcomings for individuals charged with managing databases with many tens of thousands of users, and many hundreds of data access requirements.

Figure 1: Traditional relational security.

Without roles, each individual user would need to be granted specific access to every table that they need. To simplify security, Oracle allows for the bundling of object privileges into roles that are created and then associated with users. Below is a simple example:

create role cust_role;
grant select on customer to cust_role;
grant select, update on orders to cust_role;

grant cust_role to scott;

Privileges fall into two categories, system privileges and object privileges. System privileges can be very broad in scope because they grant the right to perform an action, or perform an action on a particular TYPE of object. For example, "grant select any table to scott" invokes a system-level privilege.

Because roles are a collection of privileges, roles can be organized in a hierarchy, and different user can be assigned roles according to their individual needs. New roles can be created from existing roles, from system privileges, from object privileges, or any combination of roles (refer to figure 2).

Figure 2: A sample hierarchy for role-based Oracle security.

While this hierarchical model for roles may appear simple, there are some important caveats that must be considered.

Concerns About Role-based Security

There are several areas in which administrators get into trouble. These are granting privileges using the WITH ADMIN option, granting system-level privileges, and granting access to the special PUBLIC user. One confounding feature of role-based security is the cascading ability of GRANT privileges. For example, consider this simple command:

grant select any table to JONES with GRANT OPTION;

Here we see that the JONES user has been given a privilege with the "GRANT OPTION," and JONES gains the ability to grant any of their privileges to any other Oracle users.

When using grant-based security, there is a method to negate all security for a specific object. Security can be explicitly turned off for an object by using "PUBLIC" as the receiver of the grant. For example, to turn off all security for the CUSTOMER table, we could enter:

grant select on customer to PUBLIC;

Security is now effectively turned off for the CUSTOMER table, and restrictions may not be added with the REVOKE command. Even worse, all security can be negated with a single command:

Grant select any table to PUBLIC;

Closing the Back Doors

As we know, granting access to a table allows the user to access that table anywhere, including ad-hoc tools such as ODBC, iSQL, and SQL*Plus. Session-level security can be enforced within external Oracle tools as well as within the database.

Oracle provides their PRODUCT_USER_PROFILE table to enforce tool-level security, and the user may be disabled from updating in SQL*Plus by making an entry into this table: For example, to disable updates for user JONES, the DBA could state:

INSERT INTO 
   PRODUCT_USER_PROFILE 
   (product, userid, attribute, char_value)
VALUES
   ("SQL*Plus", "JONES", "UPDATE", "DISABLED");

User JONES could still performs updates within the application, but would be prohibited from updating while in the SQL*Plus tool. To disable unwanted commands for end-users, a wildcard can be used in the attribute column. To disable the DELETE command for all users of SQL*Plus, you could enter:

INSERT INTO 
   PRODUCT_USER_PROFILE 
   (product, userid, attribute, char_value)
VALUES 
   ("SQL*Plus", "%", "DELETE", "DISABLED");

Unfortunately, while this is great for excluding all users, we cannot alter the tables to allow the DBA staff to have DELETE authority.

Next, let's examine an alternative to role-based security, Oracle's Virtual Private Databases.

Oracle Virtual Private Databases

Oracle's latest foray into Oracle security management is a product with several names. Oracle has two official names for this product, virtual private databases, or VPD, which as also known as fine-grained access control. To add to the naming confusion, it is also commonly known as Row Level Security, and the Oracle packages have RLS in the name. Regardless of the naming conventions, VPD security is a very interesting new component of Oracle access controls.

At a high-level, VPD security adds a WHERE clause predicate to every SQL statement that is issued on behalf of an individual end user. Depending upon the end user's access, the WHERE clause constrains information to specific rows a within the table, hence the name row-level security.

But we can also do row-level security with views. It is possible to restrict SELECT access to individual rows and columns within a relational table. For example, assume that a person table contains confidential columns such as SALARY. Also assume that this tables contains a TYPE column with the values EXEMPT, NON_EXEMPT and MANAGER. We want our end users to have access to the person table, but we wish to restrict access to the SALARY columns and the MANAGER rows. A relational view could be created to isolate the columns and rows that are allowed:

create view 
   finance_view as
select 
   name, 
   address
from 
   person
where 
   department = 'FINANCE';

We may now grant access to this view to anyone:

grant select on FINANCE_VIEW to scott;

Let's take a look at how VPD works. When users access a table (or view) that has a security policy:

1. The Oracle server calls the policy function, which returns a "predicate." A predicate is a WHERE clause that qualifies a particular set of rows within the table. The heart of VPD security is the policy transformation of SQL statements. At runtime, Oracle produces a transient view with the text:

SELECT * FROM scott.emp WHERE P1

2. Oracle then dynamically rewrites the query by appending the predicate to the user's SQL statements.

The VPD methodology is used widely for Oracle systems on the Web, where security must be maintained according to instantiated users, but at the same time provide a method whereby the data access can be controlled through more procedural methods. Please note that the VPD approach to Oracle security requires the use of PL/SQL functions to define the security logic.

There are several benefits to VPD security:

• Multiple security — You can place more than one policy on each object, as well as stack highly-specific policies upon other base policies.
• Good for Web Apps — In Web applications, a single user often connects to the database. Hence, row-level security can easily differentiate between users.
• No back doors — Users no longer bypass security policies embedded in applications, because the security policy is attached to the data.

To understand how VPD works, let's take a closer look at the emp_sec procedure below. Here we see that the emp_sec function returns a SQL predicate, in this case "ENAME=xxxx," in which XXXX is the current user (in Oracle, we can get a current user ID by calling the sys_context function). This predicate is appended to the WHERE clause of every SQL statement issued by the user when they reference the EMP table.

CREATE OR REPLACE FUNCTION 
   emp_sec 
   (schema IN varchar2, tab IN varchar2)
  RETURN VARCHAR2 AS
BEGIN
  RETURN 
   'ename='''
   || 
   sys_context(
      'userenv',
      'session_user') 
   ||
   '''';
END emp_sec;
/

Once the function is created, we call the dbms_rls (row-level security) package. To create a VPD policy, we invoke the add_policy procedure, and figure 3 shows an example of the invocation of the add_policy procedure. Take a close look at this policy definition:

Figure 3: Invoking the add_policy Procedure.

In this example, the policy dictates that:

1. Whenever the EMP table is referenced
2. In a SELECT query
3. A policy called EMP_POLICY will be invoked
4. Using the SECUSR PL/SQL function.

Internally, Oracle treats the EMP table as a view and does the view expansion just like the ordinary view, except that the view text is taken from the transient view instead of the data dictionary. If the predicate contains subqueries, then the owner (definer) of the policy function is used to resolve objects within the subqueries and checks security for those objects.

In other words, users who have access privilege to the policy-protected objects do not need to know anything about the policy. They do not need to be granted object privileges for any underlying security policy. Furthermore, the users also do not require EXECUTE privileges on the policy function, because the server makes the call with the function definer's right.

In figure 4 we see the VPD policy in action. Depending on who is connected to the database, different row data is display from identical SQL statements. Internally, Oracle is rewriting the SQL inside the library cache, appending the WHERE clause to each SQL statement.

Figure 4: The VPD Policy in Action.

While the VPD approach to Oracle security works great, there are some important considerations. The foremost benefit of VPD is that the database server automatically enforces these security policies, regardless of how the data is accessed, through the use of variables that are dynamically defined within the database user's session. The downsides to VPD security are that VPD security policies are required for every table accessed inside the schema, and the user still must have access to the table via traditional GRANT statements.

Next, let examine a third type of Oracle security, the grant execute method of security.

Procedure Execution Security

Now we visit the third main area of Oracle security, the ability to grant execution privileges on specific database procedures. Under the grant execute model, an individual needs nothing more than connect privileges to attach to the Oracle database. Once attached, execution privileges on any given stored procedure, package, or function can be directly granted to each end user. At runtime, the end user is able to execute the STORE procedure, taking on the privileges of the owner of the STORE procedure.

As we know, one shortcoming of traditional role-based security is that end users can bypass their application screens, and access their Oracle databases through SQL*Plus or iSQL. One benefit of the grant execute model is that you ensure that your end users are only able to use their privileges within the scope of your predefined PL/SQL or Java code. In many cases, the grant execute security method provides tighter control access security because it controls not only those database entities that a person is able to see, but what they're able to do with those entities.

The grant execute security model fits in very nicely with the logic consolidation trend over the decade. By moving all of the business logic into the database management system, it can be tightly coupled to the database and at the same time have the benefit of additional security. The Oracle9i database is now the repository not only for the data itself, but for all of the SQL and stored procedures and functions that transform the data. By consolidating both the data and procedures in the central repository, the Oracle security manager has much tighter control over the entire database enterprise.

There are many compelling benefits to putting all Oracle SQL inside stored procedures, including:

• Better performance — Stored procedures load once into the shared pool and remain there unless they become paged out. The stored procedures can be bundled into packages, which can then be pinned inside the Oracle SGA for super-fast performance. At the PL/SQL level, the stored procedures can be compiled into C executable code where they run very fast compared to external business logic.
• Coupling of data with behavior — Developers can use Oracle member methods to couple Oracle tables with the behaviors that are directly associated with each table. This coupling provides modular, object-oriented code.
• Improved security — By coupling PL/SQL member methods and stored procedures with grant execute access, the manager gains complete access control, both over the data that is accessed and how the data is transformed.
• Isolation of code — Since all SQL is moved out of the external programs and into stored procedures, the application programs become nothing more than calls to generic stored procedures. As such, the database layer becomes independent from the application layer.

The grant execute security can give much tighter control over security than data-specific security. The DBA can authorize the application owners with the proper privileges to perform their functions, and all of the end users will not have any explicit GRANTS against the database. Instead, they are granted EXECUTE on the procedure, and the only way that the user will be able to access the data is though the procedure.

Remember, the owner of the procedure governs the access rights to the data. There is no need to create huge GRANT scripts for each and every end user, and there is no possibility of end users doing an "end-run" and accessing the tables from within other packages.

The grant execute access method has its greatest benefit in the coupling of data access security and procedural security. When an individual end user is granted execute privileges against a store procedure or package, the end user may use those packages only within the context of the application itself. This has the side benefit of enforcing not only table-level security, but column-level security. Inside the PL/SQL package, we can specify individual WHERE predicates based on the user ID and very tightly control their access to virtually any distinct data item within our Oracle database.

The confounding problem with procedures and packages is that their security is managed in an entirely different fashion from other GRANT statements. When a user is given execution privileges on a package, they will be operating under the security domain of the owner of the procedure, and not their defined security domain. In other words, a user who does not have privileges to update employee rows can get this privilege by being authorized to use a procedure that updates employees. From the DBA's perspective, their database security audits cannot easily reveal this update capability.

Conclusion

By themselves, each Oracle security mechanism does an excellent job of controlling access to data. However, it can be quite dangerous (especially from an auditing perspective) to mix and manage between the three security modes. For example, an Oracle shop using role-based security that also decided to use virtual private databases would have a hard time reconciling what users had specific access to what data tables and rows.

Another example would be mixing the grant execute security with either VPD security. The grant execute security takes those specific privileges off the owner of the procedure, such that each user who has been granted access to a store procedure may (or may not) be seeing all off the database entities that are allowed by the owner of the procedure. In other words, only a careful review of the actual PL/SQL or Java code will tell us exactly what a user is allowed to view inside the database.

As Oracle security continues to evolve, we will no doubt see more technical advances in data control methods. For now, it is the job of the Oracle DBA to ensure that all access to data is tightly controlled and managed.

--

Donald K. Burleson is one of the world’s top Oracle Database experts with more than 20 years of full-time DBA experience. He specializes in creating database architectures for very large online databases and he has worked with some of the world’s most powerful and complex systems. A former Adjunct Professor, Don Burleson has written 15 books, published more than 100 articles in national magazines, serves as Editor-in-Chief of Oracle Internals and edits for Rampant TechPress. Don is a popular lecturer and teacher and is a frequent speaker at Oracle Openworld and other international database conferences. Don's Web sites include DBA-Oracle, Remote-DBA, Oracle-training, remote support and remote DBA.