MySQL, Oracle Abc Wiki

conn sys/ as sysdba
CREATE OR REPLACE FUNCTION SYS.AUTH_POLITY(
  SCHEMA_VAR IN VARCHAR2,
  TABLE_VAR  IN VARCHAR2
)
RETURN VARCHAR2
IS
RETURN_VAL VARCHAR2 (400);
BEGIN
    RETURN_VAL := 'APP_PART = sys_context(''USERENV'',''SESSION_USER'')';
    RETURN RETURN_VAL;
END AUTH_POLITY;
/
-- create policy
BEGIN
  DBMS_RLS.ADD_POLICY (
    OBJECT_SCHEMA    => 'MYUSER',
    OBJECT_NAME      => 'MYTABLE',
    POLICY_NAME      => 'MYPOLICY',
    FUNCTION_SCHEMA  => 'SYS',
    POLICY_FUNCTION  => 'AUTH_POLITY',
    STATEMENT_TYPES  => 'SELECT, INSERT, UPDATE, DELETE'
   );
 END;
/
 
-- drop policies and recover
DROP FUNCTION AUTH_POLITY
/
BEGIN
DBMS_RLS.DROP_POLICY('MYUSER','MYTABLE','MYPOLICY');
END;
/
 
-- test sql
conn myuser/mypass
select * from mytable;
/

conn / as sysdba
 
CREATE USER finance IDENTIFIED BY welcome2;
GRANT RESOURCE TO apps identified by welcome2;
CREATE TABLE apps.benefit (c NUMBER);
 
CREATE CONTEXT appsctx USING apps.apps_security_init;
 
CREATE OR REPLACE PACKAGE apps.apps_security_init IS
PROCEDURE setctx (policy_group VARCHAR2);
END;
/
 
CREATE OR REPLACE PACKAGE BODY apps.apps_security_init AS
PROCEDURE setctx ( policy_group varchar2 ) IS
BEGIN
 
--  Do some checking to determine the current application. 
--  You can check the proxy if using the proxy authentication feature.
--  Then set the context to indicate the current application.
-- .
-- .
-- .
 
DBMS_SESSION.SET_CONTEXT('APPSCTX','ACTIVE_APPS', policy_group);
END;
END;
/
 
BEGIN
    DBMS_RLS.ADD_POLICY_CONTEXT('apps','benefit','APPSCTX','ACTIVE_APPS');
END;
/
 
--CREATE OR REPLACE FUNCTION by_company (sch varchar2, tab varchar2)
--RETURN VARCHAR2 AS
--BEGIN
--    RETURN 'COMPANY = SYS_CONTEXT(''ID'',''MY_COMPANY'')';
--END;
--/
 
--BEGIN
--    DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','SYS_DEFAULT',
--    'security_by_company',
--    'apps','by_company');
--END;
--/
 
CREATE OR REPLACE FUNCTION hr.security_policy(sch varchar2, tab varchar2) 
RETURN VARCHAR2
AS
BEGIN
    --RETURN 'SYS_CONTEXT(''ID'',''TITLE'') = ''MANAGER'' ';
    RETURN 'C>5';
END;
/
 
BEGIN
    DBMS_RLS.CREATE_POLICY_GROUP('apps','benefit','HR');
    DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','HR',
    'hr_security','hr','security_policy');
END;
/
 
grant connect, resource, select any table, select any dictionary to finance identified by welcome2;
/
 
CREATE OR REPLACE FUNCTION finance.security_policy (sch varchar2, tab varchar2) 
RETURN VARCHAR2
AS
BEGIN
    --RETURN ('SYS_CONTEXT(''ID'',''DEPT'') = ''FINANCE'' ');
    RETURN 'C<=5';
END;
/
 
BEGIN
    DBMS_RLS.CREATE_POLICY_GROUP('apps','benefit','FINANCE');
    DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','FINANCE',
    'finance_security','finance', 'security_policy');
END;
/
 
BEGIN
    apps.security_init.setctx('HR');
END;
/
 
 -- drop policies and recover
BEGIN
    DBMS_RLS.DROP_GROUPED_POLICY(
        object_schema=>'apps',
        object_name=>'benefit',
        policy_group=>'HR',
        policy_name=>'hr_security');
    DBMS_RLS.DELETE_POLICY_GROUP('apps','benefit','HR');
END;
/
DROP FUNCTION hr.security_policy
/
BEGIN
    DBMS_RLS.DROP_GROUPED_POLICY(
        object_schema=>'apps',
        object_name=>'benefit',
        policy_group=>'FINANCE',
        policy_name=>'finance_security');
    DBMS_RLS.DELETE_POLICY_GROUP('apps','benefit','FINANCE');
END;
/
DROP FUNCTION finance.security_policy
/
DROP FUNCTION hr.security_policy
/
--DROP FUNCTION apps.default_policy
--/
DROP PACKAGE BODY apps.apps_security_init
/
DROP PACKAGE apps.apps_security_init
/
BEGIN
    DBMS_RLS.DROP_POLICY_CONTEXT('apps','benefit','APPSCTX','ACTIVE_APPS');
END;
/
select * from dba_policies
/
 
drop user hr cascade
/
drop user finance cascade
/
drop user apps cascade
/