iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 10g New Features

 

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;

 

 

Google
 
Web web site