Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle Drop Group Policy
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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

     


F
or more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.