|
Security VPD in the
Oracle 10g database
In the Oracle 10g
database, there is a feature called Virtual Private
Database (VPD). It enables you to build applications
that enforce your security policy. When a user
requests a query, the server dynamically modifies
the user�s SQL statement which is not transparent to
the user. The modification is based on a WHERE
clause returned by a function.
Hands-On #1:
Connect as SYSDBA and
grant dba privilege to the ORACLE user.
SQL> CONNECT / AS SYSDBA
SQL> GRANT DBA TO
oracle;
Connect as ORACLE,
insert a record and grant SELECT object privilege to
SCOTT.
SQL> CONNECT
oracle/learning
SQL> INSERT INTO emp
VALUES
(9990, �SCOTT�,�CLERK�,
7698,
TO_DATE(�04-DEC-87�), 765.5, null, 30);
SQL> COMMIT;
SQL> GRANT SELECT ON emp
TO scott;
Create a package that
contains a procedure that it assigns the employee�s
identifier to the EMPNO attribute. In this
procedure, you need to use the
DBM_SESSION.SET_CONTEXT procedure to set the empno
attribute, and the SYS_CONTEXT function to determine
the user�s name.
SQL> CREATE OR REPLACE
PACKAGE my_security
AS
PROCEDURE get_empno;
END;
/
SQL> CREATE OR REPLACE
PACKAGE BODY my_security
AS
PROCEDURE
get_empno
IS
v_empno NUMBER;
BEGIN
SELECT empno INTO v_empno FROM oracle.emp
WHERE ename =
SYS_CONTEXT(�USERENV�,�SESSION_USER�);
DBMS_SESSION.SET_CONTEXT
(�oracle_context�, �empno�,
v_empno);
END;
END
my_security;
/
Then, create
ORACLE_CONTEXT using the MY_SECURITY package.
SQL> CREATE CONTEXT
oracle_context USING oracle.my_security;
Create an AFTER LOGIN
trigger that calls your security package if the
current user is SCOTT.
SQL> CREATE OR REPLACE
TRIGGER check_login
AFTER LOGON
ON DATABASE
BEGIN
IF user IN (�SCOTT�) THEN
oracle.my_security.get_empno();
END IF;
END
check_login;
/
Create a package to
return the ORACLE predicate used by the policy.
SQL> CREATE OR REPLACE
PACKAGE oracle_security
AS
FUNCTION
ck_empno
(x1
VARCHAR2, x2 VARCHAR2) RETURN VARCHAR2;
END;
/
SQL> CREATE
OR REPLACE PACKAGE BODY oracle_security
AS
FUNCTION
ck_empno
(x1
VARCHAR2, x2 VARCHAR2) RETURN VARCHAR2
IS
v_predicate VARCHAR2 (2000);
BEGIN
v_predicate :=
�empno = SYS_CONTEXT(��oracle_context��,��empno��)�;
RETURN v_predicate;
END ck_empno;
END
oracle_security;
/
Now, you can create a
policy
SQL> BEGIN
DBMS_RLS.ADD_POLICY (
OBJECT_SCHEMA => �oracle�,
OBJECT_NAME
=> �emp�,
POLICY_NAME
=> �oracle_policy�,
FUNCTION_SCHEMA => �oracle�,
POLICY_FUNCTION => �oracle_security.ck_empno�,
STATEMENT_TYPES => �select�,
UPDATE_CHECK
=> false,
ENABLE => true,
STATIC_POLICY => false,
POLICY_TYPE
=> DBMS_RLS.DYNAMIC,
LONG_PREICATE => false,
SEC_RELEVANT_COLS => �SAL,COMM�);
END;
/
This policy is
attached to the ORACLE.EMP table, uses the
oracle.oracle_security.ck_empno function, is applied
only for SELECT statement, is a dynamic policy, and
specifies the SAL and COMM columns as the list of
relevant columns.
Now, connect as the
SCOTT user and execute the following SQL statements.
SQL> CONNECT SCOTT/TIGER
SQL> SELECT ename FROM
oracle.emp;
SQL> SELECT sal FROM
oracle.emp;
SQL> SELECT comm FROM
oracle.emp;
|
