5.1. Introduction to RLSOracle has, for years, provided security at the table level and, to some extent, at the column level. Privileges may be granted to allow or restrict users to access only some tables or columns. You can grant privileges to specific users to insert only into certain tables while allowing them to select from other tables. For example, a user John can be granted SELECT access to the table EMP owned by Scott, which allows John to select any row from the table, but not to update, delete, or insert. Object-level privileges satisfy many requirements, but sometimes they are not granular enough to meet the various security requirements that are often associated with a company's data. A classic example arises from Oracle's traditional human resources demonstration tables. The employee table contains information about all the employees in the company, but a departmental manager should only be able to see information about employees in his department. Historically, database administrators have relied on the creation of views on top of underlying tables to achieve a degree of row-level security. Unfortunately, this approach can result in a multitude of views, which are difficult to optimize and manage, especially because the rules restricting access to rows can change often over the lifetime of an application. This is where RLS comes into play. Using RLS, you can very precisely restrict the rows in a table that a user can see, and that control can be accomplished through the creation of PL/SQL functions that encapsulate complex rules logic and that are much easier to manage than views. At a high level, RLS consists of three main components:
RLS works by automatically applying the predicate to the SQL statement issued by the user, regardless of how that statement was executed. The predicate filters out rows based on the condition defined in the policy function. If you create the condition in such a way that it excludes all rows that should not be seen by a user, you will effectively be establishing security at the row level. Oracle's automatic application of the predicate to a user's SQL statement is a key aspect of what makes RLS so secure and comprehensive. 5.1.1. Why Learn About RLS?From this initial description of RLS, you might be thinking that it is a rather specialized security function, one that you are not likely to use in your daily work as a DBA. In fact, the benefits of RLS extend beyond security. I'll take a quick look here at the reasons that DBAs find RLS helpful and discuss these in greater detail throughout the chapter.
5.1.2. A Simple ExampleLet's start with a simple example of using RLS. Here is the definition of the table EMP in the schema HR, created from the example script provided with Oracle software in $ORACLE_HOME/sqlplus/demo/demobld.sql. SQL> DESC emp Name Null? Type ----------------- -------- ------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) The table has 14 rows: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- ---------- ----- --------- ------ ------ ------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1,600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1,250 500 30 7566 JONES MANAGER 7839 02-APR-81 2,975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1,250 1,400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2,850 30 7782 CLARK MANAGER 7839 09-JUN-81 2,450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3,000 20 7839 KING PRESIDENT 17-NOV-81 5,000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1,500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1,100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3,000 20 7934 MILLER CLERK 7782 23-JAN-82 1,300 10 I'll start with a very simple requirement: I want to restrict users to seeing only employees with a salary of 1,500 or less. Using these assumptions, let's suppose that a user enters the query SELECT * FROM emp; I would like RLS to modify this query transparently to: SELECT * FROM emp WHERE sal <= 1500; That is, whenever a user asks for data from the EMP table, Oracle (via the RLS mechanism) will automatically apply the restriction I want. For this to happen, I have to tell Oracle about my requirements. First I need to write a function that builds and returns this predicate as a string. Given the simplicity of this requirement, I will use a standalone function; in production applications, you should define your predicate functions and related functionality in packages. Connecting as HR, I create the function authorized_emps shown here. CREATE OR REPLACE FUNCTION authorized_emps ( p_schema_name IN VARCHAR2, p_object_name IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN RETURN 'SAL <= 1500'; END;
When the function is executed, it will return my predicate: "SAL <= 1500". Let's confirm that using the following test script: DECLARE l_return_string VARCHAR2 (2000); BEGIN l_return_string := authorized_emps ('X', 'X'); DBMS_OUTPUT.put_line ('Return String = ' || l_return_string); END; The output is: Return String = SAL <= 1500 Now that I have a function that returns the required predicate, I can take the next step: set up a security policy, also known as an RLS policy or, simply, a policy. This policy defines when and how the predicate will be applied to SQL statements. To define row-level security for the table EMP, I'll use the following code: 1 BEGIN 2 DBMS_RLS.add_policy (object_schema => 'HR', 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 ); 9 END; Let's look more carefully at what is going on here: I am adding a policy named EMP_POLICY (line 4) on the table EMP (line 3) owned by the schema HR (line 2). The policy will apply the filter coming out of the function AUTHORIZED_EMPS (line 6) owned by schema HR (line 5) whenever any user performs INSERT, UPDATE, DELETE, or SELECT operations (line 7). Once I define my policy, I can immediately test it with a query against the EMP table: SQL>SELECT * FROM hr.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ----- --------- ------ ---------- ------ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7521 WARD SALESMAN 7698 22-FEB-81 1,250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1,250 1400 30 7844 TURNER SALESMAN 7698 08-SEP-81 1,500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1,100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7934 MILLER CLERK 7782 23-JAN-82 1,300 10 Note how only 7 rows are selected, not all 14 of them. If you look closely, you'll notice that all the rows selected have a SAL value less than or equal to 1,500, which was the predicate function. Similarly, if users try to delete from or update all the rows in the table, they will only remove those rows made visible by the RLS policy: SQL> DELETE hr.emp; 7 rows deleted. SQL> UPDATE hr.emp SET comm = 100; 7 rows updated. And because Oracle applies this filtering at a very low level, users are not aware of the filtering; they don't, in effect, know what they are missingand that is another valuable feature of RLS from the standpoint of security. Figure 5-1 illustrates the basic flow of data and filtering as implemented by RLS. Figure 5-1. How row-level-security filters workWhen a user operates on a table that is under the control of row-level security, the SQL statement is intercepted and rewritten by the database engine by adding the output of the policy function. As long as the policy function returns a valid predicate clause, it is applied to the user's original statement.
You can write policy functions that are arbitrarily complex to meet virtually any application requirements. All those functions must, however, conform to these rules:
To see the policies defined on a table, you can check the data dictionary view DBA_POLICIES, which shows the name of the policy, the object on which it is defined (and its owner), the policy function name (and its owner), and much more. See Appendix A for a complete list of the columns in this view. If you want to drop an existing RLS policy, you can do so using the DROP_POLICY program in the DBMS_RLS package. You will see examples of using this program later in this chapter.
|