Creating a Context
Context is a set of session-based attributes that can be only set by the package specified in CREATE CONTEXT statement.
CREATE CONTEXT app1_ctx USING app1_ctx_package;
In this example, only procedures and functions of app1_ctx_package package are allowed to set or reset the attributes of the app1_ctx context.
Setting Attributes of Context
Only the package specified in CREATE CONTEXT statement can change attributes of the context using DBMS_SESSION.SET_CONTEXT procedure:
CREATE OR REPLACE PACKAGE app1_ctx_package IS
PROCEDURE set_empno (empno NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY app1_ctx_package IS
PROCEDURE set_empno (empno NUMBER) IS
BEGIN
DBMS_SESSION.SET_CONTEXT('app1_ctx', 'empno', empno);
END;
END;
/
Note. You have to create the package (or just its specification) before you create the context.
An application can initialize context by calling the package procedure i.e
CALL app1_ctx_package.set_empno(11);
Restricting Data Access using Context
Assume, you have the following data:
CREATE TABLE orders (price NUMBER(10,2), empno NUMBER(5));
INSERT INTO orders VALUES (295.00, 10);
INSERT INTO orders VALUES (99.00, 11);
INSERT INTO orders VALUES (125.00, 11);
Then you can restrict access to this table, so an employee can see only own data using SYS_CONTEXT function:
SELECT * FROM orders WHERE empno = SYS_CONTEXT('app1_ctx', 'empno');
Result:
| price | empno |
| 99 | 11 |
| 125 | 11 |
This example shows how application can restrict access to data by adding WHERE condition with SYS_CONTEXT function.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7734298/viewspace-709479/ ，如需转载，请注明出处，否则将追究法律责任。
