Section 5.6. Application Contexts
5.6. Application ContextsIn the discussion of row-level security so far, I have assumed a critical factthat the predicate (i.e., the limiting condition that restricts the rows of the table) is constant, or fixed at the time of login. But what if I have a new requirement: users can now see employee records based not on fixed department numbers but on a list of privileges maintained for that reason. A table named EMP_ACCESS maintains the information about which users can access which employee information. SQL> DESC emp_access Name Null? Type ----------------- -------- ------------ USERNAME VARCHAR2(30) DEPTNO NUMBER Here is some sample data. USERNAME DEPTNO ------------------------------ ---------- MARTIN 10 MARTIN 20 KING 20 KING 10 KING 30 KING 40 I observe that Martin can see departments 10 and 20, but King can see 10, 20, 30, and 40. If an employee's name is not present in this table, he cannot see any records. The requirements also state that a user's privilege can be reassigned dynamically by updating the EMP_ACCESS table. The new privileges must take effect immediately, without requiring the user to log off and then log on again. Given these requirements, I cannot depend on a LOGON trigger to set all the values needed for use in the policy function. One possible option to meeting this requirement is to create a package with a variable to hold the predicate and provide the user with a PL/SQL program to assign the value to the variable. The policy function can then use the value cached in the package. Is this an acceptable approach? Consider this situation carefully: if the user can reassign another value to the package variable, what prevents him from assigning a high security level to this value, such as that for King? Martin could log in, set the variable to provide access to all departments, and then SELECT from the table to see all the records. There is no security in this case, and that is unacceptable. This scenario is precisely why we would ordinarily put the code for setting such values in the LOGON trigger, where the user will not have a chance to make such a change. The possibility that a user may change the value of the package variable dynamically requires us to rethink our strategy. We need a way to set a global variable by some secure mechanism so that unauthorized alteration will not be possible. Fortunately, Oracle provides this capability through application contexts. An application context is analogous to a global package variable; once set, it can be accessed throughout the session and can also be reset. An application context is also similar to a structure (struct) in the C language or a record in PL/SQL; it consists of a series of attributes, each of which is made up of a name-value pair. Unlike its counterparts in C and PL/SQL, however, the attributes are not named during the creation of the context; instead, they are named and assigned at runtime. Application contexts reside in the Process Global Area (PGA) . The mechanism by which an application context is set is what makes it more secure than a package variable. You can only change an application context value by calling a specific PL/SQL program. By restricting the way the application context is set to execution of a specific procedure, you can achieve the security needed to implement policies whose values change dynamically within a session. 5.6.1. A Simple ExampleLet's start by using the CREATE CONTEXT command to define a new context named DEPT_CTX. Any user with the CREATE ANY CONTEXT system privilege and EXECUTE privilege on the package DBMS_SESSION can create and set a context. SQL> CREATE CONTEXT dept_ctx USING set_dept_ctx; Context created. Note the clause "USING set_dept_ctx." This clause indicates that an attribute of the dept_ctx context can only be set and changed through a call to the set_dept_ctx procedure . I have not yet specified any attributes of the context; I have simply defined the overall context (its name and the secure mechanism for changing it). So let's create the procedure. Inside this procedure, I will assign values to the context attributes using the SET_CONTEXT function from the built-in package DBMS_SESSION, as shown in the following example. CREATE PROCEDURE set_dept_ctx ( p_attr IN VARCHAR2, p_val IN VARCHAR2 ) IS BEGIN DBMS_SESSION.set_context ('DEPT_CTX', p_attr, p_val); END; Now, if I remain connected to the same session that owns this procedure, I can call it directly to set the attribute named DEPTNO to a value 10, as follows: SQL> EXEC set_dept_ctx ('DEPTNO','10') PL/SQL procedure successfully completed. To obtain the current value of an attribute, you call the SYS_CONTEXT function , which accepts two parametersthe context name and the attribute name. Here is an example: SQL> DECLARE 2 l_ret VARCHAR2 (20); 3 BEGIN 4 l_ret := SYS_CONTEXT ('DEPT_CTX', 'DEPTNO'); 5 DBMS_OUTPUT.put_line ('Value of DEPTNO = ' || l_ret); 6 END; / Value of DEPTNO = 10 You may have noticed that I used the SYS_CONTEXT function earlier in the chapter to obtain the IP address and terminal names of the client. 5.6.2. The Security of Application ContextsThe set_dept_ctx procedure is nothing but an encapsulation of a call to SET_CONTEXT with appropriate parameters. Why not simply call the built-in function directly? Let's see what happens if a user calls the same code segment to set the value of the attribute DEPTNO to 10. SQL> BEGIN 2 DBMS_SESSION.set_context ('DEPT_CTX', 'DEPTNO', 10); 3 END; 4 / begin * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SESSION", line 82 ORA-06512: at line 2 Note the error, ORA-01031: insufficient privileges. That's puzzling, because the user does have the required EXECUTE privilege on DBMS_SESSION. (It would have been impossible to compile set_dept_ctx without that privilege.) The insufficient privilege refers not to the use of DBMS_SESSION, but to the attempt to set the context value outside of the set_dept_ctx procedure . As you can now see, Oracle only "trusts" the set_dept_ctx procedure to set the application context values for DEPT_CTX. In fact, Oracle refers to the program referenced by the USING clause of CREATE CONTEXT as the trusted procedure. The only schemas that can execute a trusted procedure are:
So if you are careful about how you grant that EXECUTE authority, you can tightly control the setting of that context's values.
5.6.3. Contexts as Predicates in RLSSo far you have learned that a procedure must be used to set a context value, which is akin to a global package variable. You might be tempted to ask, "How is that useful? Doesn't it increase the complexity rather unnecessarily without achieving any definite purpose?" No. Because the trusted procedure is the only way to set a context attribute's value, you can use it to maintain execution control. Inside the trusted procedure, I can place all types of checks to ensure that the variable assignments are valid. I can even completely eliminate the passing of parameters and set the values from predetermined values without any input (and therefore influence) from the user. Going back to the requirement for employee access, for instance, I know that we need to set the application context value to a string of department numbers, picked from the table EMP_ACCESS, not passed in by the user. To accomplish this, I will use the application context in the policy function itself. First, I need to modify the policy function. 1 CREATE OR REPLACE FUNCTION authorized_emps ( 2 p_schema_name IN VARCHAR2, 3 p_object_name IN VARCHAR2 4 ) 5 RETURN VARCHAR2 6 IS 7 l_deptno NUMBER; 8 l_return_val VARCHAR2 (2000); 9 BEGIN 10 IF (p_schema_name = USER) 11 THEN 12 l_return_val := NULL; 13 ELSE 14 l_return_val := SYS_CONTEXT ('DEPT_CTX', 'DEPTNO_LIST'); 15 END IF; 16 17 RETURN l_return_val; 18 END; Here the policy function expects the department numbers to be passed through the attribute DEPTNO_LIST of the context DEPT_CTX (line 14). To set this value, I need to modify the trusted procedure of the context. CREATE OR REPLACE PROCEDURE set_dept_ctx IS l_str VARCHAR2 (32767); l_ret VARCHAR2 (32767); BEGIN FOR deptrec IN (SELECT deptno FROM emp_access WHERE username = USER) LOOP l_str := l_str || deptrec.deptno || ','; END LOOP; IF l_str IS NULL THEN -- no access records found, no records -- should be displayed. l_ret := '1=2'; ELSE l_ret := 'DEPTNO IN (' || RTRIM (l_str, ',') || ')'; DBMS_SESSION.set_context ('DEPT_CTX', 'DEPTNO_LIST', l_ret); END IF; END; It's time to test the function. First, Martin logs in and counts the number of employees. Before he issues the query, he needs to set the context. SQL> EXEC rlsowner.set_dept_ctx PL/SQL procedure successfully completed. SQL> SELECT sys_context ('DEPT_CTX','DEPTNO_LIST') FROM dual; SYS_CONTEXT('DEPT_CTX','DEPTNO_LIST') ------------------------------------- DEPTNO IN (20,10) SQL> SELECT DISTINCT deptno FROM hr.emp; DEPTNO ---------- 10 20 Here Martin sees only the employees of departments 10 and 20, as per the EMP_ACCESS table. Suppose now that Martin's access is changed to department 30, accomplished by the following changes to the EMP_ACCESS table: SQL> DELETE emp_access WHERE username = 'MARTIN'; 2 rows deleted. SQL> INSERT INTO emp_access values ('MARTIN',30); 1 row created. SQL> COMMIT; Commit complete. When Martin now issues the same query as before, he will see different results. First he executes the stored procedure to set the context attribute. SQL> EXEC rlsowner.set_dept_ctx PL/SQL procedure successfully completed. SQL> SELECT sys_context ('DEPT_CTX','DEPTNO_LIST') FROM dual; SYS_CONTEXT('DEPT_CTX','DEPTNO_LIST') ------------------------------------------------------------- DEPTNO IN (30) SQL> SELECT DISTINCT deptno FROM hr.emp; DEPTNO ---------- 30 This change takes effect automatically. Note that Martin did not specify which department he was allowed to see; he simply called the stored procedure set_dept_ctx, which set the context attributes automatically. Because Martin can't set the context attributes himself, this arrangement is inherently more secure than setting a global package variable, which can be set directly by Martin to whatever value he chooses. What if Martin does not execute the procedure set_dept_ctx before issuing the SELECT query? When he does execute the query, the attribute DEPTNO_LIST of the application context DEPT_CTX will be set to NULL, and hence the predicate from the policy will not list any department number. As a result, Martin will not be able to see any employees. Analyze the above situation carefully. All I have done so far is create a policy predicate (in other words, a WHERE condition) to be applied to a user's query. I have decided to set the application context attribute first and have the policy function selected from the context attribute instead of from the table EMP_ACCESS. I could have also made the policy function select directly from the table EMP_ACCESS and construct the predicate, which could have made the policy function very easy to write. That way, the user would not have had to execute the policy function every time she logged on. There is, however, an added advantage to having the policy function select from application contexts instead of tables directly. Let's compare the two approaches, using pseudo-code to express the fundamental logic. First, I put logic in the policy function to select from the table EMP_ACCESS and return the predicate string: 1 Get the username 2 Loop 3 Select department numbers from EMP_ACCESS table 4 which are accessible to the username 5 Compile a list of department numbers 6 End loop 7 Return the list as a predicate Now I do the same thing, but in the set_dept_ctx procedure : 1 Get the username 2 Loop 3 Select department numbers from EMP_ACCESS table 4 which are accessible to the username 5 Compile a list of department numbers 6 End loop 7 Set the attrbiute DEPTNO_LIST to the value as in the list above And then the only logic needed inside the policy function is this: 1 Look up the context attribute DEPTNO_LIST 2 Return that as a policy predicate Note the differences in the two approaches. When a user logs in, his username does not change in a session. Consequently, the set_dept_ctx function can be executed just once when the session starts to set the context attribute. The policy function built around this context attribute can then avoid querying the underlying EMP_ACCESS table and rely solely on session memory. If I used the version of the policy function that selects from the table, the SQL statements that trigger use of the policy function would have to do much more work. Thus, policies that access all necessary data via context attributes can increase significantly the performance of SQL statements with RLS policies. In Oracle Database 10g, there is an added advantage to using contexts. You can define a policy as context_sensitive (see the section "RLS in Oracle Database 10g"), which indicates that the policy function is to be executed only when the context changes, not every time. In the above example, it means that the policy function is executed only once (when the user logs in and sets the context); therefore, the policy execution will be extremely fast. If the access requirements change, the user simply re-executes the set_dept_ctx procedure , which will re-execute the policy function. 5.6.4. Identifying Non-Database UsersApplication contexts are useful well beyond the situations we've described so far. A key use of application contexts is to distinguish between different users who cannot be identified through unique sessions. This is quite common in web applications that typically use a connection pool a pool of connections to the database using a single user, named, for example, CONNPOOL. Web users connect to the application server, which, in turn, uses one of the connections from the pool to get to the database. This is shown in Figure 5-2. Figure 5-2. Application users and RLS Here the users Martin and King are not database users, they are web users, and the database has no specific knowledge of them. The connection pool connects to the database using the userid CONNPOOL, which is a database user. When Martin requests something from the database, the pool might decide to use the connection labeled 1 to get it from the database. After the request is complete, the connection becomes idle. If at this point King requests something, the pool might decide to use the same connection (labeled 1). Hence, from the database perspective, a session (which is actually the connection from the pool) is from the user CONNPOOL. As a consequence, the examples we showed earlier (where the USER function identifies the actual schema name) will not work to uniquely identify the user making the calls. The USER function will always return CONNPOOL, because that is the connected user to the database. This is where the application context comes into the picture. Assume that there is a context named WEB_CTX with attribute name WEBUSER. This value is set to the name of the actual user (e.g., Martin) by the connection pool when it gets the request from the client. The RLS policy can be based on this value instead of on the database username. Let's see this in action. Suppose that you have a banking application in which several account managers access the client's records. You need to build an RLS system that allows an account manager to see only her clients, not others. The table has a column, ACC_MGR, that holds the username of the account manager for that account. So in this case, the policy predicate should be ACC_MGR = AccountManagerUserName where the value of AccountManagerUserName should be the Windows userid of the account manager, something the database does not know. This value needs to be passed by the connection pool to the database using contexts. First, I will create the context: CREATE CONTEXT web_ctx USING set_web_ctx; Then, my main procedure to set the context looks like this: CREATE OR REPLACE PROCEDURE set_web_ctx (p_webuser IN VARCHAR2) IS BEGIN DBMS_SESSION.set_context ('WEB_CTX', 'WEBUSER', p_webuser); END; This procedure takes one parameter, the actual user (WEBUSER). This is exactly what will be used by the application to set the application context WEB_CTX. After the procedure is created, I will make sure it works. SQL> EXEC set_web_ctx ('LIZA') PL/SQL procedure successfully completed. SQL> EXEC DBMS_OUTPUT.put_line(sys_context('WEB_CTX','WEBUSER')) LIZA PL/SQL procedure successfully completed. Note that the context setting procedure I have shown here (set_web_ctx) is rudimentary. All it does is set a context attribute. In real life, you will need to write several lines of code to perform various checks to make sure the caller of the procedure is authorized, and so on. For example, a Windows-based application server can extract the username from the client machine directly and pass it to the context using the above procedure. Once the context is set, I can easily use it to build the policy function. My policy function looks like this: CREATE OR REPLACE FUNCTION authorized_accounts ( p_schema_name IN VARCHAR2, p_object_name IN VARCHAR2 ) RETURN VARCHAR2 IS l_deptno NUMBER; l_return_val VARCHAR2 (2000); BEGIN IF (p_schema_name = USER) THEN l_return_val := NULL; ELSE l_return_val := 'acc_mgr = ''' || SYS_CONTEXT ('WEB_CTX', 'WEBUSER') || ''''; END IF; RETURN l_return_val; END; This policy function returns a predicate "acc_mgr = 'the username'", which is applied to the user's queries. Now the user automatically will be able to see only his records. |
