Drop_grouped_policy is a procedure that
drops, or detaches, a policy from the specified
table, view, or synonym and de-associates that
policy with the specified policy group. If no
schema is defined, then the current session’s
schema is assumed.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
|
POLICY_GROUP
|
VARCHAR2
|
IN
|
SYS_DEFAULT
|
|
POLICY_NAME
|
VARCHAR2
|
IN
|
|
Table 6.13:
Drop_grouped_policy Statement Types
Drop_policy is a procedure that simply
drops a policy from the specified table, view,
or synonym. If no schema is defined, then the
current session’s schema is assumed.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
|
POLICY_NAME
|
VARCHAR2
|
IN
|
|
Table 6.14:
Drop_policy
Statement Types
Drop_policy_context is a procedure that
drops the application context that drives the
enforcement of policies, i.e. the context that
determines which application is running. If no
schema is defined, then the current session’s
schema is assumed.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
|
NAMESPACE
|
VARCHAR2
|
IN
|
|
|
ATTRIBUTE
|
VARCHAR2
|
IN
|
|
Table 6.15:
Drop_policy_context
Statement Types
Enable_grouped_policy is a procedure that
simply enables or disables a policy associated
with a policy group which causes the current
transaction, if any, to commit.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
|
POLCIY_GROUP
|
VARCHAR2
|
IN
|
SYS_DEFAULT
|
|
POLICY_NAME
|
VARCHAR2
|
IN
|
|
|
ENABLE
|
BOOLEAN
|
IN
|
TRUE
|
Table 6.16:
Enable_grouped_policy
Statement Types
Enable_policy is a procedure that simply
enables or disables a policy from the specified
table, view, or synonym. If no schema is
defined, then the current session’s schema is
assumed.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
|
POLICY_NAME
|
VARCHAR2
|
IN
|
|
|
ENABLE
|
BOOLEAN
|
IN
|
TRUE
|
Table 6.17:
Enable_policy
Statement Types
Refresh_grouped_policy is a procedure that
invalidates all cursors and reparses the SQL
statements associated with that policy.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
POLCIY_GROUP
|
VARCHAR2
|
IN
|
NULL
|
|
POLICY_NAME
|
VARCHAR2
|
IN
|
NULL
|
Table 6.18:
Refresh_grouped_policy
Statement Types
Refresh_policy is a procedure that
invalidate all cursors and reparses the SQL
statements associated with that policy.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
OBJECT_SCHEMA
|
VARCHAR2
|
IN
|
NULL
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
POLICY_NAME
|
VARCHAR2
|
IN
|
NULL
|
Table 6.19:
Refresh_policy
Statement Types
The virtual_priv_db_demo.sql
SQL*Plus script is a very simple example of
creating a row-level VPD in the SCOTT schema,
where user BERT can see everything and user
BERT2 can only see the rows for people working
in department 10.
virtual_priv_db_demo.sql script
connect bert/bert
set term off
drop user bert2;
drop package
emp_ctx_pkg;
drop package
boday emp_ctx_pkg;
drop context
set_emp_ctx;
drop trigger
emp_ctx_trg;
drop function set_emp_prd;
BEGIN
DBMS_RLS.DROP_POLICY (
object_schema
=> 'scott',
object_name
=> 'emp',
policy_name
=> 'emp_policy'
);
END;
/
set term on
grant connect to bert2
identified by bert2;
grant select on scott.emp
to bert2;
CREATE OR REPLACE PACKAGE
emp_ctx_pkg
IS
PROCEDURE
set_emp_ctx;
END;
/
CREATE OR REPLACE PACKAGE
BODY emp_ctx_pkg
IS
PROCEDURE
set_emp_ctx
AS
BEGIN
DBMS_SESSION.SET_CONTEXT('EMP_CTX', 'DNO',
10);
END;
END;
/
CREATE OR REPLACE CONTEXT
emp_ctx USING emp_ctx_pkg;
CREATE OR REPLACE TRIGGER
emp_ctx_trig
AFTER LOGON ON DATABASE
BEGIN
if (USER =
'BERT2') then
bert.emp_ctx_pkg.set_emp_ctx;
end if;
END;
/
CREATE OR REPLACE
FUNCTION set_emp_prd(
p_schema
IN VARCHAR2,
p_table
IN VARCHAR2
) RETURN
VARCHAR2
AS
emp_pred
VARCHAR2 (400);
BEGIN
if (USER =
'BERT2') then
emp_pred := 'deptno
= SYS_CONTEXT(''emp_ctx'', ''DNO'')';
else
emp_pred :=
'1=1';
end if;
RETURN emp_pred;
END;
/
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema
=> 'scott',
object_name
=> 'emp',
policy_name
=> 'emp_policy',
function_schema
=> 'bert',
policy_function
=> 'set_emp_prd',
statement_types
=> 'select'
);
END;
/
-- bert/bert
select * from scott.emp;
connect bert2/bert2;
select * from scott.emp;
The
output from the virtual_priv_db_demo.sql
SQL*Plus script looks like this:
virtual_priv_db_demo.sql output
SQL> -- bert/bert
SQL> select * from
scott.emp;
EMPNO ENAME
JOB
MGR HIREDATE
SAL
COMM DEPTNO
---------- ---------
--------- ---------- --------- ------- -----
------
7369 SMITH
CLERK
7902 17-DEC-80
800
20
7499 ALLEN
SALESMAN
7698 20-FEB-81
1600
300
30
7521 WARD
SALESMAN
7698 22-FEB-81
1250
500
30
7566 JONES
MANAGER
7839 02-APR-81
2975
20
7654 MARTIN
SALESMAN
7698 28-SEP-81
1250
1400
30
7698 BLAKE
MANAGER
7839 01-MAY-81
2850
30
7782
CLARK
MANAGER
7839 09-JUN-81
2450
10
7788 SCOTT
ANALYST
7566 19-APR-87
3000
20
7839 KING
PRESIDENT
17-NOV-81
5000
10
7844 TURNER
SALESMAN
7698 08-SEP-81
1500
0
30
7876
ADAMS
CLERK
7788 23-MAY-87
1100
20
7900 JAMES
CLERK
7698 03-DEC-81
950
30
7902 FORD
ANALYST
7566 03-DEC-81
3000
20
7934 MILLER
CLERK
7782 23-JAN-82
1300
10
14 rows selected.
SQL>
SQL> connect bert2/bert2;
Connected.
SQL> select * from
scott.emp;
EMPNO ENAME
JOB
MGR HIREDATE
SAL
COMM DEPTNO
---------- ---------
--------- ---------- --------- ------- -----
------
7782
CLARK
MANAGER
7839 09-JUN-81
2450
10
7839 KING
PRESIDENT
17-NOV-81
5000
10
7934 MILLER
CLERK
7782 23-JAN-82
1300
10
