A few months ago, I wrote an article on the Virtual Private Database (VPD) feature in Oracle Magazine, available on the Oracle Technology Network. The article had a good reception from the readers. Of course, I’m not going to reproduce the article here; from the feedback of the readers, I came to realize another powerful concept is grossly overlooked and not well understood by most users. This feature — application contexts — allows you to transfer sensitive bits of information securely across a session, and is the focus of this article.

A database with VPD restricts the rows a user sees based on his credentials. The restriction is enforced by a WHERE clause automatically appended to the query. This clause, called a predicate, is generated by a user-defined function called policy function. A VPD policy on the table EMP may create a predicate in such a way that the query

SELECT * FROM ACCOUNTS

becomes

SELECT * FROM ACCOUNTS
WHERE AM_NAME = USER

In this case, we assume that the logged-in user is also an account manager (AM). Hence, only those rows that record her username (returned by the function USER) as the Account Manager (shown by the column AM_NAME) are displayed; nothing else. So, if Scott logs in and issues this query, the query effectively becomes

SELECT * FROM ACCOUNTS
WHERE AM_NAME = ‘SCOTT’

We wanted to make sure that when the user Scott logs in, he sees only those records for which he is the account manager, not others. The automatic application of the predicate via the clause WHERE AM_NAME = ‘SCOTT’ guaranteed that. This is what we wanted, right?

Let’s pause for a moment. In this case, the requirement was rather simple: The Account Manager’s Oracle login ids were used to identify the accounts they represent. This makes sense in a client-server application in which a stand-alone client application connects to the database. Suppose the bank had a different requirement; instead of a stand-alone client application, the bank used a Web-based application in which the users log in to the application server. The application server, such as WebSphere, Weblogic, or Oracle Application Server, connected to the database using a generic userid (such as CONNPOOL) to form a connection pool, and the regular user’s browser simply used one of the existing connections from the pool. In such a case, the Oracle userid will be CONNPOOL, not SCOTT. The VPD policy would not return a useful predicate, and the query would always be rewritten to

SELECT * FROM EMP
WHERE AM_NAME = ‘CONNPOOL’

regardless of which user is logged in to the application server (e.g., SCOTT). As the readers of my earlier article pointed out, a VPD policy would be quite useless here. This simply gives credence to the model in which the application, not the database, enforces the security, they asserted.

Not so fast, I responded. I have never been a proponent of application-based security models. The application is just one of the gateways to the data; there are plenty more, including the ubiquitous SQL*Plus. What prevents a user from circumventing the application to get to the data directly? To enable the database-centric security model, the predicate must be different. What if there were a way to pass the information about the logged-in user to the database? Wouldn’t that make it possible to use VPD in all cases? For instance, if the query were rewritten to

SELECT * FROM EMP
WHERE (
AM_NAME = <oracle userid>
OR
AM_NAME = <application user id>
)

wouldn’t it enforce the same type of security? In fact, it would be far safer than application-based security enforcement. Regardless of how users connected to the database — using the application server, Web browser, SQL*Plus, TOAD, SQL Worksheet, even homegrown tools — this VPD policy would still be invoked and applied. So, the trick is to pass the extra information — the application userid — to the database reliably and securely. Note the importance of the words, the information must be passed with assurance that it has not been tampered; otherwise, Joe can pass himself off as Scott and access all that Scott has access to.

So, we need a sort of global variable to pass the information on from the application user to the database. A mechanism such as a package variable comes to mind. Here is one example:

create or replace package myvars
as
   app_userid varchar2(20);
end;

Then you simply assign the app userid to this package through

begin
   myvars.app_userid := 'SCOTT';
end;

This assignment could be done in the application after the user is authenticated. In the policy predicate, the package variable would then be referenced:

SELECT * FROM EMP
WHERE (
AM_NAME = USER
OR
AM_NAME = MYVARS.APP_USERID
)

Since a package variable, once set in a session, persists throughout the session, this works to enforce the security.

However, there is a huge security hole. The assignment of the package variable is done in the application, but what if I log in to the database as a non-critical user, and then set the package variable myself?

execute myvars.app_userid := ‘SCOTT’

And that makes me Scott immediately! Not just Scott; I can be anyone I wish to be — Tom, Dick, Harry, anyone, as long as they are named as users of the system. Thus, the security goes down the drain, and it falls back on application-based security for a foolproof implementation, argued the critics.

But, as I already mentioned, I’m not a huge fan of the model; I still think the database should handle authorization, not the application. This is a failure of the reliability aspects of the mechanism; the method of passing the information about the user was neither reliable nor secure enough. We’ve got to find a different way.

A Different Type of Global Variable

This is where application contexts come into the picture. Simply put, application contexts are analogous to global variables in the sense that once a value is assigned, they can be referenced throughout the session. However, that’s where the similarity ends. The biggest differentiator is the way the assignment is made — and that’s where the security is provided.

You may have been using application contexts without realizing it. Note the clause,

am_name = USER

Here the function USER is actually an implementation of

SYS_CONTEXT(‘USERENV’,’SESSION_USER’)

The built-in function SYS_CONEXT is a function that reads a certain part of the memory called Application Context, which can be defined by the user. The context can hold values that are set for a session and can be referenced throughout the session. A different session may hold a different value of the variable inside an application context.

Here an attribute named SESSION_USER of the application context USERENV has been accessed to get the user information. The context SYS_CONTEXT was already defined when the database was created. It has two distinct parts:

1. Namespace
2. Attribute

In this example, the namespace was USERENV and the attribute was SESSION_USER. The value of the attribute is set at the runtime. When the Oracle user Scott logs in, the value is set to SCOTT. Similarly, there are several other attributes of the namespace USERENV as well.

Application Contexts also resemble the table record structure — they have attributes, which are analogous to columns. The difference is the attributes are not named during creation. You define an application context as

create context acc_ctx
using set_acc_ctx;

Note the second line, “using …” carefully. This indicates that the context attributes can only be set by the procedure set_acc_ctx. Obviously, we need to create one such procedure.

     1  create procedure set_acc_ctx
  2  (
  3      p_attribute_name in varchar2,
  4      p_attribute_value in varchar2
  5  ) is
  6  begin
  7      dbms_session.set_context(
  8         'acc_ctx',
  9          p_attribute_name,
 10          p_attribute_value);
 11* end;

Note line 7, through which the context attributes are set. Instead of setting a specific value, we have left a placeholder in form of a parameter, which can be passed to the procedure. Since attributes are not named at runtime, but rather, during the assignment, we can pass any attribute name and its associated value. For instance, we can pass the attribute named app_userid and its associated value, such as “SCOTT” as in:

execute set_acc_ctx (‘app_userid’,’SCOTT’)

This sets the value of the attribute app_userid to SCOTT. If after calling the procedure, you want to check the contexts set in the session, you could issue

select * from session_context
/

NAMESPACE         ATTRIBUTE  VALUE
----------------- ---------- -----
ACC_CTX           APP_USERID SCOTT
ACC_CTX           ATTRIBUTE2 VAL1

As in case of USERENV contexts, you can also check the value of a specific attribute of a specific context in a session. To check the attribute APP_USERID in ACC_CTX, you could use:

SELECT SYS_CONTEXT('ACC_CTX','APP_USERID')
FROM DUAL;

This returns the value of the attribute. If we can set the value to the application userid, the policy function in the VPD policy should return a predicate such as:

SELECT * FROM EMP
WHERE (
AM_NAME = USER
OR
AM_NAME = SYS_CONTEXT(‘ACC_CTX’,’APP_USERID’)
)

Trusted Procedure

The attribute is actually set in line seven by calling the dbms_session.set_context supplied PL/SQL procedure. So, you may ask, what is the big deal about writing another wrapper procedure around it? Can’t we just call dbms_session.set_context directly?

Let’s see ... suppose Joe tries to set the context directly:

begin 
    dbms_session.set_context(
        namespace => 'acc_ctx',
        attribute => 'app_userid',
        value => 'SCOTT'

He gets an error.

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 78
ORA-06512: at "ANANDA.SET_CLAIM_APP_CTX", line 7
ORA-06512: at line 1

The errors show insufficient privileges on dbms_session. However, granting the execute privileges to the user does not make the error go away. The user simply cannot directly set the context. This makes this procedure a trusted one — meaning that only the procedure can set the associated context. This is a very important concept in a discussion about contexts.

So far you have seen that a context can only be set using its associated trusted procedure, no other way. So, how does that make the context more secure? First, consider the mechanism to authenticate and authorize. Since executing the procedure is the only way to set the context, we can place all types of security check inside this procedure to make sure all the checks are satisfied before the context is set. If any of the checks fail, the procedure raises an error, and the context is never set. These checks can be:

1. Making sure that the connections are coming from the right client (IP, client name, and so on)

2. Making sure that the connections are requesting the type of data usually expected at that time of the day. For instance, brokerage transactions are not executed after the stock market closes. So, those types of transactions can be set to return a null predicate for the policy function. However, some brokerage companies use alternative exchanges such as Instinet and Island, where the transactions are permitted up to a later time; those transactions can be allowed until such time.

3. Making sure the user making a request is supposed to be making requests from those clients. For instance, the connection pool exists for application servers appsvr1 through appsvr5, and they connect to the database using the Oracle userid CONNPOOL. The checks could ensure that the when userid is CONNPOOL, the client machines are between appsvr1 to appsvr5. The same test could be other way around as well, i.e., all connections coming from appsvr1 should be only as user CONNPOOL, and so on.

Other Uses of Application Contexts

In VPD, the predicate string is constructed by the policy function. Suppose, instead of having a dedicated account manager for an account, a department is allocated a chunk of accounts that anyone in the department can view. This makes the policy predicate look like

where account_no in (111,222,333,…etc…)

Sure, the string of account numbers separated by commas can be easily constructed by a PL/SQL procedure by looping through the numbers, but this creates a serious performance problem. Each of the strings will be unique, and a bind variable can’t be used. This increases parsing rate for the statement. As you know from any performance tuning exercise, more parsing leads to more CPU consumption, latch contention and library cache lock waits. One way to reduce is to make the latter part of the string an attribute of a context. For instance, the context acc_ctx could have an attribute named acc_list, which can be populated with the account numbers separated by commas and then the predicate can be written as:

where account_no in sys_context(‘acc_ctx.’,’acc_list’)

When the statement is parsed, it is done so as

where account_no in :b1

or something similar. This reduces hard parses, CPU cycles and shared-pool fragmentations, among other things.

Generic User Privilege Management

Now that you understand how powerful application contexts can be, let’s see more examples of it in action. One of the thorny problems in managing generic users such as those used by connection pools is deciding their privileges. Application users Scott, Joe, and John have three different types of authority levels. Scott has privileges to read any table, but update the ones in Checking Account only; Joe can read checking account customers only, and cannot update anything, and John can update any account. Since the user CONNPOOL must have all the privileges required by the application users, at the minimum, it must have the largest common denominator of the privilege list for the user to work properly. In this case, CONNPOOL must have the privilege to update all the tables, or else John can’t have the privileges he needs. However, that means that Joe also has the same privileges that John has, which Joe does not need. This is unacceptable from a security point of view. What can be done?

The answer lies in using application contexts and roles effectively. Here you need to create three roles with the associated privileges:

1. UPDATE_ANY – update any table
2. SELECT_ANY – select any table
3. SELECT_CHECKING – select checking account tables only

These roles are then granted to the user CONNPOOL:

grant update_any, select_any, select_checking to connpool;

Well, you might ask, this does not resolve the issue. Instead of granting the privileges directly, we assigned it via roles, which means CONNPOOL still has the privilege to update any table, and since Joe uses a connection from the pool, he can update any table — the very thing we are trying to avoid.

The trick lies in making the roles disabled. To use the privileges granted, a user must not only be granted the role, but the role must be enabled as well. If a role is granted but disabled, the privileges are not available. Take the following example:

SQL> connect connpool/******

Connected.

SQL> Update savings.accounts set acc_name = ‘DICK’ where acc_no = 1;

1 row updated.

The user can do the operation. Now disable all the roles and retry.

SQL> set role none;

Role set.

SQL> update savings.accounts set acc_name = ‘DICK’ where acc_no = 1;

ERROR at line 1:
ORA-00942: table or view does not exist

The operation failed, since the role UPDATE_ANY, which allows the update, was not enabled. Now enable it, and retry the operation:

SQL> set role update_any;

Role set.

SQL> update savings.accounts set acc_name = ‘DICK’ where acc_no = 1;

1 row updated.

The operation was successful. The roles must be enabled to have the privileges in place; granting them to a user is not enough. However, we didn’t enable the role during the very first case; so why were the role privileges in place?

This is because of the way a role is defined for a user. A user can have one or more default roles, which means that those roles are automatically enabled when the user logs in. If the user has no default role, then a role granted to him must be explicitly enabled using the SET ROLE command. Be default, the roles granted to a user are default. When CONNPOOL was granted the roles, all of them became default roles. This can be prevented by issuing

alter user CONNPOOL default role none;

Now CONNPOOL has no default role and when it logs in no roles will be enabled. This is the trick we are going to use for the privilege management.

Before we go further, let’s see how to check the roles enabled in a session. We can check it by issuing:

select * from session_roles;

no rows selected

No roles have been enabled in the session — yet. Now, we can build a procedure to set the role:

  1  create or replace procedure set_user_role
  2  authid current_user
  3  is
  4      l_user_role   varchar2(2000);
  5  begin
  6      select sys_context('role_ctx','user_role')
  7      into l_user_role
  8      from dual;
  9      dbms_session.set_role (l_user_role);
 10  end;

In line nine, the equivalent of SET ROLE command, dbms_session.set_role, was used. It does the same thing — it enables a role in the session. Note some very important points about this procedure.

1. First, there are no arguments to the procedure. This means that when the user calls the procedure, he does not specify the role to enable; it will be retrieved from the application context known as ROLE_CTX. The value of the attribute USER_ROLE will be read by this procedure and that role will be enabled by default.

2. Second, the execute privileges for the user on dbms_session package are revoked are revoked; he can’t just call dbms_session.set_role (‘UPDATE_ANY’). Also, the SET command has been disabled by the product user profile tables, so SET ROLE will not work.

3. Third, we will not let the user set the context by calling its trusted procedure. Note that this is a different context — ROLE_CTX, not APP_CTX, so we can control the access. This will be called from the context setting procedure after extensive checks. When checks are successfully completed, the attribute USER_ROLE will be set to UPDATE_ANY when the application user is John. If the application user is Joe, the application context will be set to SELECT_CHECKING.

Now let’s see this in action. Joe is trying to update some records, a clear violation of the policies.

1. First he logs in to the database as user CONNPOOL.

2. Then he tries to update the table, but since there are no default roles for the user CONNPOOL, the session has no privilege to do anything, and the update fails due to insufficient privileges.

3. He tries to set the role by dbms_session.set_role(‘UPDATE_ANY’), but since CONNPOOL has no privileges on that package, it will fail.

4. Then he realizes that application user John has privileges to update any table. He tries to pretend he is John to set the context by calling set_acc_ctx(‘acc_ctx’,’app_userid’,’JOHN’). This operation fails since the checks inside the procedure fail. The application context attribute app_userid is null and so is the attribute user_role of the context role_ctx.

5. Then he tries to set the role by calling set_user_role, but it does nothing, since the value of role_ctx.user_role is null.

At the end of these tries, Joe is still connected as a user CONNPOOL, but with no privileges. He can, of course, log in as himself, which was allowed, anyway.

From this example, you can see how powerful application contexts can be to manage complex requirements such as the user role management.

Conclusion

In summary, application contexts are akin to global variables that are accessible in a session once set. Unlike global variables, they can be set through a special procedure known as trusted procedure, which is defined when the context is created. Since the trusted procedure is the only way to set a context, it can be designed with a variety of checks and balances to prevent unauthorized access. The applications contexts can be used in Virtual Private Databases to provide a predicate string that does not need to be hard parsed, saving considerable CPU resources. Using non-default roles for generic userids, applications can manage varied privileges for diverse sets of application users.

The type of application contexts discussed here are visible only from within a session. Since they reside in PGA, not SGA, other sessions can’t access them, making them highly secure. Another type of application contexts — global contexts — aid in development of secure, Web-based applications. Global contexts will be discussed in a later article.