7.7 How do I…Implement record level security using views?Problem
We have certain tables that are used throughout the organization. We want all users to work with the same table, but only see the data for their job function. I know that I can use views to query selective data, but how do I restrict access to data on a record level using views?
Technique
The USER pseudo-column contains the account of the user connected to the database. Most pseudo-columns can be used in views. The USER pseudo-column can restrict access to certain users, and the SYSDATE pseudo-column can restrict access from old data. Subqueries can be used to produce lists of valid values within an IN operator.
Steps
1. Run SQL*Plus and connect as the WAITE user account. CHP7_20.SQL, shown in Figure 7.12, contains a CREATE TABLE statement for building a table that will be restricted by a view.
The SALES_ID column contains the name of the user account that creates the record. The ORDER_DATE column contains the date the order is placed, and the ORDER_DESC column describes the order.
2. Run the statement to create the table.
SQL> START CHP7_20.sql
Table created.
3. Load CHP7_21.SQL into the SQL buffer. The file contains a statement to restrict data based on the ORDER_DATE column. In this example, the customer service department can only work with orders that have an order date of today or later.
SQL> GET CHP7_21.sql
1 CREATE OR REPLACE VIEW SALES_VIEW07 AS
2 SELECT SALES_ID,
3 ORDER_DATE,
4 ORDER_DESC
5 FROM
6 SALES07
7 WHERE
8* ORDER_DATE < SYSDATE
Line 1 contains the CREATE OR REPLACE VIEW keywords to build the view and provides the name of the view. Lines 2 through 8 contain the query that generates the view’s records. The WHERE clause in lines 7 and 8 specifies that the view will only return records where the ORDER_DATE column is less than the SYSDATE.
4. Run the statement to create the view.
SQL> /
View created.
5. Load CHP7_22.SQL into the SQL buffer. The file contains a statement to insert a sample record into the table.
SQL> GET CHP7_22.sql
1 INSERT INTO SALES07 (
2 SALES_ID, ORDER_DATE, ORDER_DESC)
3 VALUES
4* (USER, SYSDATE+5, ‘FIVE DAYS LATER’);
6. This statement inserts a record with an order date after today into the table. Even though the record will exist in the table, it will not appear in the view created in the previous steps. Run the statement to insert the record into the table and commit the transaction.
SQL> /
1 row created.
SQL> COMMIT;
Commit complete
7. Query the record from the SALES07 table. Because the table is not restricted, the row will be displayed.
SQL> SELECT * FROM SALES07;
SALES_ID
ORDER_DATE
ORDER_DESC
--------
-----------
------------------------------
WAITE
07-JAN-98
FIVE DAYS LATER
8. Query the record from the view. Because the view will not contain any rows with the ORDER_DATE column after the system date, no records will be retrieved.
SQL> SELECT * FROM SALES_VIEW07;
no rows selected.
9. Load CHP7_23.SQL into the SQL buffer. The file will replace the SALES_VIEW07 with a view that only returns rows with the SALES_ID column equal to the USER pseudo-column. Using the USER pseudo-column in the WHERE clause causes the view to return different results for each user account.
SQL> GET CHP7_23.sql
Line 1 contains the keywords required to create the view and specifies the name of the view. Lines 2 through 8 contain the query used to generate records for the view. The WHERE clause in lines 7 and 8 returns rows only where the SALES_ID field is equal to the user account.1 CREATE OR REPLACE VIEW SALES_VIEW07 AS
2 SELECT SALES_ID,
3 ORDER_DATE,
4 ORDER_DESC
5 FROM
6 SALES07
7 WHERE
8* SALES_ID = USER
10. Create the view that restricts records based on the user account.
11. Query the view to determine if the record created in steps 5 and 6 exists in the view.
SQL> /
View created.
SQL> SELECT * FROM SALES_VIEW07;
SALES_ID
ORDER_DATE
ORDER_DESC
--------
-----------
------------------------------
WAITE
07-JAN-98
FIVE DAYS LATER
How It Works
Steps 1 and 2 build a table that is the basis for the restricted view. Steps 3 and 4 create a view that only returns data if the ORDER_DATE column is less than SYSDATE. Steps 5 and 6 insert a record into the base table, which violates the WHERE clause of the view. Step 7 queries the table directly to display the data stored in it. Step 8 queries the view to show that the row is not returned by it. Steps 9 and 10 create a view that only lets a user whose account name is the same as the SALES_ID field display data. Step 11 queries the view to show that the user’s data is returned by a query of the view.
Comments
Restricted views should be utilized to limit users to viewing data for their job function only. Restricting views can have a cascading effect. Restricting one table will have the effect of limiting the rows returned by other queries using the table.