|
‘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
Introduction
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-Implementing
VPD
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_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;
|
“When you are right
you cannot be too radical; when you are wrong, you cannot be too
conservative.” Martin Luther King Jr. |
Questions:
Questions on
Security VPD
in the Oracle 10g database
Q: What is VPD in the
Oracle 10g Database?
|
