|
Security
VPD in the Oracle 10g database
|
"It is easy
enough to be friendly to one's friends. But to be friend the one who
regards himself as your enemy is the quintessence of true religion. The
other is mere business."
Gandhi
|
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 ISELF user.
SQL>
CONNECT / AS SYSDBA
SQL>
GRANT DBA TO iself;
Connect as ISELF,
insert a record and grant SELECT object privilege to SCOTT.
SQL>
CONNECT iself/schooling
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 iself.emp
WHERE ename =
SYS_CONTEXT("USERENV","SESSION_USER");
DBMS_SESSION.SET_CONTEXT
("iself_context", "empno", v_empno);
END;
END my_security;
/
Then, create
ISELF_CONTEXT using the MY_SECURITY package.
SQL>
CREATE CONTEXT iself_context USING iself.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
iself.my_security.get_empno();
END IF;
END check_login;
/
Create a package to
return the ISELF predicate used by the policy.
SQL>
CREATE OR REPLACE PACKAGE iself_security
AS
FUNCTION ck_empno
(x1 VARCHAR2, x2 VARCHAR2) RETURN VARCHAR2;
END;
/
SQL>
CREATE OR REPLACE PACKAGE BODY iself_security
AS
FUNCTION ck_empno
(x1 VARCHAR2, x2 VARCHAR2) RETURN VARCHAR2
IS
v_predicate VARCHAR2 (2000);
BEGIN
v_predicate :=
"empno = SYS_CONTEXT(""iself_context"",""empno"")";
RETURN v_predicate;
END ck_empno;
END iself_security;
/
Now, you can create
a policy
SQL>
BEGIN
DBMS_RLS.ADD_POLICY (
OBJECT_SCHEMA
=> "iself",
OBJECT_NAME
=> "emp",
POLICY_NAME
=> "iself_policy",
FUNCTION_SCHEMA =>
"iself",
POLICY_FUNCTION
=> "iself_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 ISELF.EMP table, uses the iself.iself_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 iself.emp;
SQL>
SELECT sal FROM iself.emp;
SQL>
SELECT comm. FROM iself.emp;
|