Section 5.2. Using RLS
5.2. Using RLSNow that you have seen an example of RLS fundamentals, let's look at some examples that take advantage of different aspects of RLS functionality. 5.2.1. Performing an Update CheckLet's consider a slight twist on our earlier example. Instead of updating the COMM column, the user now updates the SAL column. Because SAL is the column used in the predicate, it will be interesting to see the result. SQL> UPDATE hr.emp SET sal = 1200; 7 rows updated. SQL> UPDATE hr.emp SET sal = 1100; 7 rows updated. Only seven rows are updated, as expected. Now, let's change the updated amount. After all, everyone deserves a better salary. SQL> UPDATE hr.emp SET sal = 1600; 7 rows updated. SQL> UPDATE hr.emp SET sal = 1100; 0 rows updated. Note the last update. Why were no rows updated? The answer lies in the first update. The first one updated the SAL column to 1,600, which is not satisfied by the filtering predicate "SAL <= 1500". Thus, after the first update, all of the rows became invisible to the user. This is a potentially confusing situation: the user can execute a SQL statement against rows, and the statement changes the access to those rows. During application development, this seeming data instability may create bugs or at least introduce a degree of unpredictability that makes debugging a challenge. To counter this behavior, we can take advantage of another ADD_POLICY parameter, update_check. Let's take a look at the impact of setting this parameter to TRUE when we create a policy on the table. BEGIN DBMS_RLS.add_policy (object_name => 'EMP', policy_name => 'EMP_POLICY', function_schema => 'HR', policy_function => 'AUTHORIZED_EMPS', statement_types => 'INSERT, UPDATE, DELETE, SELECT', update_check => TRUE ); END; After this policy is placed on the table, if a user attempts to perform the same update, she gets an error. SQL> UPDATE hr.emp SET sal = 1600; update hr.emp set sal = 1600 * ERROR at line 1: ORA-28115: policy with check option violation The ORA-28115 error is raised because the policy now prevents any updates to the value of columns in a row that will cause a change in the visibility of those rows with the specified predicate. Users can still make changes to other columns; these changes do not affect the visibility of the rows: SQL> UPDATE hr.emp SET sal = 1200; 7 rows updated.
5.2.2. Static RLS PoliciesSo far the examples have demonstrated the use of a static policy, which means that the value returned by the predicate function does not change even if the circumstances under which it is called change. Given this fact, RLS need not execute the function every time a query is issued against the table. The value can be determined only once, cached, and then reused from the cache as many times as needed. To make a policy behave that way, you can define it as a static policy to RLS by passing TRUE for the static_policy argument: 1 BEGIN 2 DBMS_RLS.ADD_POLICY ( 3 object_name => 'EMP', 4 policy_name => 'EMP_POLICY', 5 function_schema => 'HR', 6 policy_function => 'AUTHORIZED_EMPS', 7 statement_types => 'INSERT, UPDATE, DELETE, SELECT', 8 update_check => TRUE, 9 static_policy => TRUE 10 ); 11 END; The default value of the static_policy parameter is FALSE: that value makes the policy dynamic rather than static, and it causes the policy function to be called for each operation on the table. I'll describe dynamic policies later in the chapter in the section "Defining a Dynamic Policy." Policy types, in addition to static and dynamic, are supported in Oracle Database 10g. See the section "Other Classes of Dynamism" for more information. There are many situations that call very precisely for a static policy. Consider a merchandise warehouse that is servicing several customers. Here, a predicate might be used to limit the entries to only the relevant records for that customer. For example, the table BUILDINGS may contain a column named CUSTOMER_ID. A predicate "CUSTOMER_ID = customer_id" must be appended to the queries where customer_id is based on the user who is logged in. When a user logs in, his customer ID can be retrieved via a LOGON trigger, and the RLS policy can use that ID to evaluate which rows should be displayed. During a session, the value of this predicate does not change, so it makes sense to set static_policy to TRUE in such a situation. 5.2.2.1. Problems with static policiesStatic policies can enhance performance, but they can also introduce bugs in applications. If the predicate derives from or depends on a changing value, such as time, IP address, client identifier, and so on, you will want to define a dynamic policy rather than a static one. Here is an example that shows why. Let's look again at my original policy function, but let's now assume that the predicate depends on a changing value, such as the value of seconds in the current system timestamp. (This may not be a very likely real-life example, but it is close enough to explain the concept.) SQL> CREATE TABLE trigger_fire 2 ( 3 val NUMBER 4 ); Table created. SQL> INSERT INTO trigger_fire 2 VALUES 3 (1); 1 row created. SQL> COMMIT; Commit complete. SQL> 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_return_val VARCHAR2 (2000); 8 PRAGMA AUTONOMOUS_TRANSACTION; 9 BEGIN 10 l_return_val := 'SAL <= ' || TO_NUMBER (TO_CHAR (SYSDATE, 'ss')) * 100; 11 12 UPDATE trigger_fire 13 SET val = val + 1; 14 15 COMMIT; 16 RETURN l_return_val; 17 END; 18 / Function created. In this example, the function takes the seconds part of the current time (line 10), multiplies it by 100, and returns a predicate that shows the value of the column SAL less than or equal to this number. Because the seconds part will change with time, consecutive executions of this function will yield different results. Now let's define a policy on the EMP table with this function as the policy function. As the policy already exists, I need to drop it first. SQL> BEGIN 2 DBMS_RLS.drop_policy (object_name => 'EMP', policy_name => 'EMP_POLICY') ; 3 END; 4 / PL/SQL procedure successfully completed. SQL> BEGIN 2 DBMS_RLS.add_policy (object_name => 'EMP', 3 policy_name => 'EMP_POLICY', 4 function_schema => 'HR', 5 policy_function => 'AUTHORIZED_EMPS', 6 statement_types => 'INSERT, UPDATE, DELETE, SELECT', 7 update_check => TRUE, 8 static_policy => FALSE 9 ); 10 END; 11 / PL/SQL procedure successfully completed. It's time to test the policy. The user Lenny tries to find out the number of employees in the table. SQL> SELECT COUNT(*) FROM hr.emp; COUNT(*) ---------- 0 Because the table is under row-level security, the policy function is invoked to provide the predicate string to be applied to the query. It depends on the seconds part of the current timestamp, so it is some value between 0 and 60. In this particular case, the value was such that none of the records matched the predicate; hence, no rows were satisfied. Because the policy function updates the column VAL in the table TRIGGER_FIRE, I can check to see how many times the function was called. As the user HR, let's check the value of VAL in the table TRIGGER_FIRE. SQL> SELECT * FROM trigger_fire; VAL ---------- 3 Because the policy function was called twiceonce during the parse phase and once during the execution phasethe value was incremented by 2 from 1. Lenny issues the query again to know the number of employees. SQL> SELECT COUNT(*) FROM hr.emp COUNT(*) ---------- 10 This time the policy function returned the predicate, which was satisfied by 10 records in the table. Again, let's check the value of VAL in the table TRIGGER_FIRE. SQL> SELECT * FROM trigger_fire; VAL ---------- 5 The value is incremented by 2 from 3proof that the policy function was executed multiple times. You can repeat the exercise as many times as you wish, to verify that the policy function is executed each time the operation occurs on the table. Now, declare the policy as static and repeat the test. Because there is no RLS operation or API to alter a policy, you will need to drop it and then re-create it. SQL> BEGIN 2 DBMS_RLS.drop_policy (object_name => 'EMP', policy_name => 'EMP_POLICY'); 3 END; 4 / PL/SQL procedure successfully completed. Now let's see the effect. 1 BEGIN 2 DBMS_RLS.add_policy (object_name => 'EMP', 3 policy_name => 'EMP_POLICY', 4 function_schema => 'HR', 5 policy_function => 'AUTHORIZED_EMPS', 6 statement_types => 'INSERT, UPDATE, DELETE, SELECT', 7 update_check => TRUE, 8 static_policy => TRUE 9 ); 10 END; 11 / PL/SQL procedure successfully completed. SQL> -- Reset the table TRIGGER_FIRE SQL> UPDATE trigger_fire SET val = 1; 1 row updated. SQL> COMMIT; Commit complete. As user Lenny, I select the number of rows from the table. SQL> SELECT COUNT(*) FROM hr.emp; COUNT(*) ---------- 8 As user HR, I check the value of the column VAL in TRIGGER_FIRE. SQL> SELECT * FROM trigger_fire; VAL ---------- 2 The value was incremented by 1, because the policy function was executed only once, not twice as it was before. I'll repeat the selection from the table EMP several times as user Lenny. SQL> SELECT COUNT(*) FROM hr.emp; COUNT(*) ---------- 8 SQL> SELECT COUNT(*) FROM hr.emp; COUNT(*) ---------- 8 SQL> SELECT COUNT(*) FROM hr.emp; COUNT(*) ---------- 8 In all cases, the same number is returned. Why? It's because the policy function was executed only once and the predicate that was used by the policy was cached. Because the policy function was never executed after the first execution, the predicate did not change. To confirm that, I'll select from the table TRIGGER_FIRE as user HR. SQL> SELECT * FROM trigger_fire; VAL ---------- 2 The value is still 2; it has not been incremented at all since the first time it was called. This output confirms that the policy function was not called during subsequent SELECTs on the table EMP. By declaring a policy as static, I have effectively instructed the policy function to execute only once, and the policy to reuse the predicate originally created, even though the predicate might have changed in the course of time. This behavior might produce unexpected results in your application, so you should use static policies with great caution. The only time you're likely to want to use static policies is the case where the function positively results in a definitive predicate regardless of any of the variables, except those set at session startup and never changedfor example, the username. 5.2.2.2. Using a pragmaAnother way to enforce the logic we require is to use a packaged function along with a pragma declaration to suppress any database operations. Here is the package specification. CREATE OR REPLACE PACKAGE rls_pkg AS FUNCTION authorized_emps ( p_schema_name IN VARCHAR2, p_object_name IN VARCHAR2 ) RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES (authorized_emps, WNDS, RNDS, WNPS, RNPS); END; / And here is the package body. CREATE OR REPLACE PACKAGE BODY rls_pkg AS FUNCTION authorized_emps ( p_schema_name IN VARCHAR2, p_object_name IN VARCHAR2 ) RETURN VARCHAR2 IS l_return_val VARCHAR2 (2000); BEGIN l_return_val := 'SAL <= ' || TO_NUMBER (TO_CHAR (SYSDATE, 'ss')) * 100; RETURN l_return_val; END; END; / In the package specification, I have defined a pragma to bring this function to these purity levels:
When this package body is compiled, the pragma will be violated and the compilation will fail with the following message. Warning: Package Body created with compilation errors. Errors for PACKAGE BODY RLS_PKG: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/4 PLS-00452: Subprogram 'AUTHORIZED_EMPS' violates its associated pragma Declaring a pragma protects you from falling into potentially erroneous situations. However, it's still a good idea to use static policies as long as they are being applied only to deterministic situations, such as the warehouse example we discussed earlier.
5.2.3. Defining a Dynamic PolicyIn the previous sections, I talked about a policy that returns a predicate string that is constantfor example, SAL <= 1500. In real life, such a scenario is not very common, except in some specialized applications such as goods warehouses. In most cases, you will need to build a filter based on the user issuing the query. For instance, the HR application may require that users see only their own records, not all records in a table. This is a dynamic requirement, as it needs to be evaluated for each employee who logs in. The policy function can be rewritten as follows. 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_return_val VARCHAR2 (2000); 8 BEGIN 9 l_return_val := 'ENAME = USER'; 10 RETURN l_return_val; 11 END; 12 / In line 9, the predicate will compare the ENAME column with the USERthat is, the name of the currently logged-in user. If the user Martin (remember that Martin is the name of an employee in the table EMP) logs in and selects from the table, he sees only one rowhis own. SQL> CONN martin/martin Connected. SQL> SELECT * FROM hr.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ------ --------- ------ ------ ------ 7654 MARTIN SALESMAN 7698 28-SEP-81 1,250 1,400 30 Now let's expand this model to let Martin show more recordsnot just his own, but his entire department's records. The policy function now becomes the following. 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 SELECT deptno 11 INTO l_deptno 12 FROM emp 13 WHERE ename = USER; 14 15 l_return_val := 'DEPTNO = ' || l_deptno; 16 RETURN l_return_val; 17 END; 18 / I also need to perform an additional step here. In the preceding code, the function selects from the table EMP (lines 1013). However, the table is protected by the RLS policy whose policy function is owned by the user HR. When the function executes under the privileges of the user HR, it will not find any rows, because there is no employee with the name HRmaking the predicate an incorrect one. To prevent this from happening, there are two options:
If I use the first approach, I will not need to change the policy function. As a DBA user, I must grant the special privilege to HR as follows: GRANT EXEMPT ACCESS POLICY TO hr; This removes the application of any RLS policies from the user HR. Because no policyregardless of which table it is defined onwill be applied, you should use this approach with great caution. In fact, considering the breach it places in the security model, we do not recommend this approach for regular schema owners. The other approach is to have a special schema named, say, RLSOWNER, which creates all the RLS policies and owns all the policy functions. Only this user, and no others, is granted the EXEMPT ACCESS POLICY system privilege . Because the policy function is owned by RLSOWNER, the policy-creation PL/SQL block looks like the following. This block can be run by any user that has EXECUTE privileges on the DBMS_RLS package . 1 BEGIN 2 DBMS_RLS.add_policy (object_name => 'EMP', 3 policy_name => 'EMP_POLICY', 4 function_schema => 'RLSOWNER', 5 policy_function => 'AUTHORIZED_EMPS', 6 statement_types => 'INSERT, UPDATE, DELETE, SELECT', 7 update_check => TRUE 8 ); 9 END; 10 / Using the second approach, the policy function has to include the logic to bypass the filter for the schema owner, as shown below. Again, this block may be run by any user with EXECUTE privileges on the package DBMS_RLS. 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 SELECT deptno 15 INTO l_deptno 16 FROM emp 17 WHERE ename = USER; 18 19 l_return_val := 'DEPTNO = ' || l_deptno; 20 END IF; 21 22 RETURN l_return_val; 23 END; 24 / This version of the function is very similar to the previous ones; the new lines are shown in bold (line 10). Here I am checking to see if the calling user is the owner of the table; if so, I return NULL (line 12). A NULL value in the predicate returned by the function is equivalent to no policy at allthat is, no rows are filtered. Now Martin executes the same query as before: SQL> SELECT * FROM hr.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ------ --------- ------ ------ ------ 7499 ALLEN SALESMAN 7698 20-FEB-81 1,600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1,250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1,250 1,400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2,850 30 7844 TURNER SALESMAN 7698 08-SEP-81 1,500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30 6 rows selected. Note how all of the returned rows are from Martin's department (30). As you can see, the policy function is crucial in building the RLS policy. The policy will be able to place filters on the rows with whatever predicate value the function can spin out, as long as it is syntactically correct. You can create quite elaborate and sophisticated predicates using policy functions. Following the same approach, you can have the RLS filters applied to any table in the database. For instance, you could have a policy on the table DEPT as shown below. 1 BEGIN 2 DBMS_RLS.add_policy (object_schema => 'HR', 3 object_name => 'DEPT', 4 policy_name => 'DEPT_POLICY', 5 function_schema => 'RLSOWNER', 6 policy_function => 'AUTHORIZED_EMPS', 7 statement_types => 'SELECT, INSERT, UPDATE, DELETE', 8 update_check => TRUE 9 ); 10 END; 11 / Here the same functionAUTHORIZED_EMPSis used as the policy function. Because the function returns a predicate "DEPTNO = deptno", it can easily be used in the table DEPT as well as any other table containing a DEPTNO column. A table that does not have a DEPTNO column probably has another column through which it has a foreign-key relationship with the EMP table. For instance, the table BONUS has a column ENAME through which it is tied to the EMP table. So I can rewrite my policy function as follows: CREATE OR REPLACE FUNCTION allowed_enames ( p_schema_name IN VARCHAR2, p_object_name IN VARCHAR2 ) RETURN VARCHAR2 IS l_deptno NUMBER; l_return_val VARCHAR2 (2000); l_str VARCHAR2 (2000); BEGIN IF (p_schema_name = USER) THEN l_return_val := NULL; ELSE SELECT deptno INTO l_deptno FROM hr.emp WHERE ename = USER; l_str := '('; FOR emprec IN (SELECT ename FROM hr.emp WHERE deptno = l_deptno) LOOP l_str := '''' || emprec.ename || ''','; END LOOP; l_str := RTRIM (l_str, ','); l_str := ')'; l_return_val := 'ENAME IN ' || l_str; END IF; RETURN l_return_val; END; / If I define a policy on the BONUS table with the following policy function, this places the RLS policy on the BONUS table, as well: BEGIN DBMS_RLS.add_policy (object_schema => 'HR', object_name => 'BONUS', policy_name => 'BONUS_POLICY', function_schema => 'RLSOWNER', policy_function => 'ALLOWED_ENAMES', statement_types => 'SELECT, INSERT, UPDATE, DELETE', update_check => TRUE ); END; / In this manner, I can define RLS polices on all related tables in the database driven from one table. Because the facility I've described in this section essentially provides a private view of the tables in the database based on the user or other parameters (like the time of day or IP address), it is also known as Virtual Private Database (VPD). 5.2.4. Improving PerformanceLet's assume that our requirements have changed again. (That's not surprising in a typical organization, is it?) Now I have to set up the policy in such a way that all employees and departments will be visible to a user who is a manager; otherwise, only the employees of the user's department are visible. To accommodate this requirement, my policy function might look like this. CREATE OR REPLACE FUNCTION authorized_emps ( p_schema_name IN VARCHAR2, p_object_name IN VARCHAR2 ) RETURN VARCHAR2 IS l_deptno NUMBER; l_return_val VARCHAR2 (2000); l_mgr BOOLEAN; l_empno NUMBER; l_dummy CHAR (1); BEGIN IF (p_schema_name = USER) THEN l_return_val := NULL; ELSE SELECT DISTINCT deptno, empno INTO l_deptno, l_empno FROM hr.emp WHERE ename = USER; BEGIN SELECT '1' INTO l_dummy FROM hr.emp WHERE mgr = l_empno AND ROWNUM < 2; l_mgr := TRUE; EXCEPTION WHEN NO_DATA_FOUND THEN l_mgr := FALSE; WHEN OTHERS THEN RAISE; END; IF (l_mgr) THEN l_return_val := NULL; ELSE l_return_val := 'DEPTNO = ' || l_deptno; END IF; END IF; RETURN l_return_val; END; Look at the complexity in selecting the data. This complexity will surely add to the response time (and, of course, in your real-world applications, the logic will be considerably more complex). Can I simplify the code and improve performance? I certainly can. Look at the first requirementchecking to see if the employee is a manager. In the above code, we checked the EMP table for that information, but the fact that an employee is a manager does not change very often. Similarly, a manager's manager might change, but the status of a manager remains the same. So, the title of a manager is actually more like an attribute of the employee when she logs in, not something that changes during a session. Therefore, if we can somehow, during the login process, pass to the database the fact that the user is a manager, that check will not be needed later in the policy function. How do I pass a value of this kind? Global variables come to mind. I could assign the value "Y" or "N" to designate the manager status and create a package to hold the variable. CREATE OR REPLACE PACKAGE mgr_check IS is_mgr CHAR (1); END; The policy function looks like this. CREATE OR REPLACE FUNCTION authorized_emps ( 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 SELECT DISTINCT deptno INTO l_deptno FROM hr.emp WHERE ename = USER; IF (mgr_check.is_mgr = 'Y') THEN l_return_val := NULL; ELSE l_return_val := 'DEPTNO = ' || l_deptno; END IF; END IF; RETURN l_return_val; END; Notice how much less code is now required to check for manager status. It merely checks the status from a global packaged variable. This variable has to be set during the login process and thus is a perfect job for an AFTER LOGON database trigger. CREATE OR REPLACE TRIGGER tr_set_mgr AFTER LOGON ON DATABASE DECLARE l_empno NUMBER; l_dummy CHAR (1); BEGIN SELECT DISTINCT empno INTO l_empno FROM hr.emp WHERE ename = USER; SELECT '1' INTO l_dummy FROM hr.emp WHERE mgr = l_empno AND ROWNUM < 2; rlsowner.mgr_check.is_mgr := 'Y'; EXCEPTION WHEN NO_DATA_FOUND THEN rlsowner.mgr_check.is_mgr := 'N'; WHEN OTHERS THEN RAISE; END; / The trigger sets the value of the packaged variable to designate the manager status of the employee, which is then picked up by the policy function. Let's do a quick test. Connecting as King (who is a manager) and Martin (who is not), I can see that the setup works. SQL> CONN martin/martin Connected. SQL> SELECT COUNT(*) FROM hr.emp; COUNT(*) ---------- 262145 SQL> CONN king/king Connected. SQL> SELECT COUNT(*) FROM hr.emp; COUNT(*) ---------- 589825 Martin's query retrieves fewer employees, as expected, whereas King's query retrieves all rows. You can often use this packaged variable approach to improve performance. In the first example, where the check for manager status was done inside the policy function, the query took 199 centiseconds. Using the global variable approach, it took only 132 centiseconds, which represents a significant improvement. 5.2.5. Controlling the Type of Table AccessRLS has many uses beyond security and the simplification of application development models. RLS is also very helpful if you need to switch a table between read-only and read/write status, as determined by a variety of circumstances. Without RLS, DBAs could make an entire tablespacebut not the individual tables inside itread-only or read/write. Even if a DBA wanted to take this approach, a tablespace could not be made read-only if it had any active transactions. As it may be impossible to find a period of time during which there are no transactions in a database, a tablespace may never actually be able to be made read-only. In such cases, RLS is the only viable solution. Now, to be honest, RLS does not actually make a table read-only; it simply allows us to emulate that behavior by denying any attempts to change the contents of the table. The simplest way to do this is to apply a predicate to any UPDATE, DELETE, and INSERT that will always evaluate to FALSEfor example, 1=2. Here is an example of making the EMP table read-only with this most basic of predicate functions: CREATE OR REPLACE FUNCTION make_read_only ( p_schema_name IN VARCHAR2, p_object_name IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN -- Only the owner of the predicate policy function can change -- the data in the table. IF (p_schema_name = USER) THEN RETURN NULL; ELSE RETURN '1=2'; END IF; END; Using this policy function, I can create an RLS policy on the table EMP for the DML statements that change data: INSERT, UPDATE, and DELETE. BEGIN DBMS_RLS.add_policy (object_name => 'EMP', policy_name => 'EMP_READONLY_POLICY', function_schema => 'HR', policy_function => 'MAKE_READ_ONLY', statement_types => 'INSERT, UPDATE, DELETE', update_check => TRUE ); END; In the line shown in bold, note that the statement_types parameter does not include the SELECT statement, as that statement will be freely allowed. And now it is only possible to query information on the EMP table if the current user is not the owner of the policy's function: SQL> SHOW user USER is "MARTIN" SQL> DELETE hr.emp; 0 rows deleted. SQL> SELECT COUNT(*) FROM hr.emp; COUNT(*) ---------- 14 When the time comes to make the table read/write again, I can simply disable the policy. BEGIN DBMS_RLS.enable_policy (object_name => 'EMP', policy_name => 'EMP_READONLY_POLICY', ENABLE => FALSE ); END; / Now non-HR users can successfully complete DML operations on the table.
The power of this feature is not limited to just making tables read-only or read/write on demand; it can be created dynamically and applied automatically based on any user-defined conditions that you wish. For instance, you could write a policy function that makes the table read-only between 5:00 P.M. and 9:00 A.M. for all users, except for the batch job user, BATCHUSER, and read-only for the batch user between 9:00 A.M. and 5:00 P.M. The body of such a function might look like this: BEGIN IF (p_schema_name = USER) THEN l_return_val := NULL; ELSE l_hr := TO_NUMBER (TO_CHAR (SYSDATE, 'HH24')); IF (USER = 'BATCHUSER') -- you can list all users here that should be -- read only during the daytime. THEN IF (l_hr BETWEEN 9 AND 17) THEN -- make the table read only l_return_val := '1=2'; ELSE l_return_val := NULL; END IF; ELSE -- users which need to be read only during after-hours IF (l_hr >= 17 AND l_hr <= 9) THEN -- make the table read only l_return_val := '1=2'; ELSE l_return_val := NULL; END IF; END IF; END IF; RETURN l_return_val; END; Based on the timestamp, you can let the table be controlled granularly on multiple fronts. The example shown here can be extended to cover other events as well (e.g., IP address, authentication type, client information, terminal, OS user, and many others). All you have to do is get the appropriate variable from the system context (SYS_CONTEXT; this feature is explored later in the chapter) of the session and check it. For example, assume that you have a requirement that the user King (the president of the company) is allowed to see every record only if he does both of the following:
The policy function would now look like this: CREATE OR REPLACE FUNCTION emp_policy ( 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; ELSIF (USER = 'KING') THEN IF ( -- check client machine name SYS_CONTEXT ('USERENV', 'HOST') = 'ACMEBANK\KINGLAP' OR -- check OS username SYS_CONTEXT ('USERENV', 'OS_USER') = 'king' OR -- check IP address SYS_CONTEXT ('USERENV', 'IP_ADDRESS') = '192.168.1.1' ) THEN -- all checks satisfied for KING; allow unrestricted access. l_return_val := NULL; ELSE -- return the usual predicate l_return_val := 'SAL <= 1500'; END IF; ELSE -- All other users l_return_val := 'SAL <= 1500'; END IF; RETURN l_return_val; END; Here I am using the built-in function SYS_CONTEXT to return the context attributes. I'll discuss the use of system contexts later (see the "Application Contexts" section); all you need to understand now is that the function call returns the name of the client terminal from which the user is connected. The other lines using the function call also return the appropriate values. You can use SYS_CONTEXT to get a variety of information about the user connection. By using this information, you can easily customize your policy function to build a filter to cater to your specific needs. For a complete list of attributes available via SYS_CONTEXT, refer to Oracle's SQL Reference Manual. |
