Rem
Rem $Header: catrls.sql 24-feb-99.15:58:03 rshaikh Exp $
Rem
Rem catrls.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      catrls.sql - Catalog views for Row Level Security
Rem
Rem    DESCRIPTION
Rem      Creates data dictionary views for row level security policies
Rem
Rem    NOTES
Rem      Must be run while connected to SYS
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rshaikh     02/24/99 - change create view to create or replace view
Rem    dmwong      06/16/98 - update all_policies to depend on all_table and al
Rem    clei        03/09/98 - Created
Rem

create or replace view DBA_POLICIES (OBJECT_OWNER, OBJECT_NAME, POLICY_NAME, 
			  PF_OWNER, PACKAGE, FUNCTION, SEL, INS,
			  UPD, DEL, CHK_OPTION, ENABLE) 
as
select u.name, o.name, r.pname, r.pfschma, r.ppname, r.pfname,
       decode(bitand(r.stmt_type,1), 0, 'NO', 'YES'),
       decode(bitand(r.stmt_type,2), 0, 'NO', 'YES'),
       decode(bitand(r.stmt_type,4), 0, 'NO', 'YES'),
       decode(bitand(r.stmt_type,8), 0, 'NO', 'YES'),
       decode(r.check_opt, 0, 'NO', 'YES'),
       decode(r.enable_flag, 0, 'NO', 'YES')
from user$ u, obj$ o, rls$ r
where u.user# = o.owner# 
and r.obj# = o.obj#;
/
drop public synonym DBA_POLICIES
/
create public synonym DBA_POLICIES for DBA_POLICIES
/
comment on table DBA_POLICIES is
'All row level security policies in the database'
/
comment on column DBA_POLICIES.OBJECT_OWNER is
'Owner of the table or view'
/
comment on column DBA_POLICIES.OBJECT_NAME is
'Name of the table or view'
/
comment on column DBA_POLICIES.POLICY_NAME is
'Name of the policy'
/
comment on column DBA_POLICIES.PF_OWNER is
'Owner of the policy function'
/
comment on column DBA_POLICIES.PACKAGE is
'Name of the package containing the policy function'
/
comment on column DBA_POLICIES.FUNCTION is
'Name of the policy function'
/
comment on column DBA_POLICIES.SEL is
'If YES, policy is applied to query on the object'
/
comment on column DBA_POLICIES.INS is
'If YES, policy is applied to insert on the object'
/
comment on column DBA_POLICIES.UPD is
'If YES, policy is applied to update on the object'
/
comment on column DBA_POLICIES.DEL is
'If YES, policy is applied to delete on the object'
/
comment on column DBA_POLICIES.CHK_OPTION is
'Is check option enforced for this policy?'
/
comment on column DBA_POLICIES.ENABLE is
'Is this policy is enabled?'
/
grant select on DBA_POLICIES to select_catalog_role
/
create or replace view ALL_POLICIES (OBJECT_OWNER, OBJECT_NAME, POLICY_NAME, 
			  PF_OWNER, PACKAGE, FUNCTION, SEL, INS,
			  UPD, DEL, CHK_OPTION, ENABLE) 
as
select u.name, o.name, r.pname, r.pfschma, r.ppname, r.pfname,
       decode(bitand(r.stmt_type,1), 0, 'NO', 'YES'),
       decode(bitand(r.stmt_type,2), 0, 'NO', 'YES'),
       decode(bitand(r.stmt_type,4), 0, 'NO', 'YES'),
       decode(bitand(r.stmt_type,8), 0, 'NO', 'YES'),
       decode(r.check_opt, 0, 'NO', 'YES'),
       decode(r.enable_flag, 0, 'NO', 'YES')
from all_tables t, rls$ r, obj$ o, user$ u
where u.user# = o.owner#               /* find user name of table owner */
and u.name = t.owner                   /* match user name */
and o.name = t.table_name              /* match table name */
and r.obj# = o.obj#                    /* table has rls */
and o.type# = 2                        /* make sure the object is a table */
union all
select u.name, o.name, r.pname, r.pfschma, r.ppname, r.pfname,
       decode(bitand(r.stmt_type,1), 0, 'NO', 'YES'),
       decode(bitand(r.stmt_type,2), 0, 'NO', 'YES'),
       decode(bitand(r.stmt_type,4), 0, 'NO', 'YES'),
       decode(bitand(r.stmt_type,8), 0, 'NO', 'YES'),
       decode(r.check_opt, 0, 'NO', 'YES'),
       decode(r.enable_flag, 0, 'NO', 'YES')
from all_views v, rls$ r, obj$ o, user$ u
where u.user# = o.owner#               /* find user name of view owner */
and u.name = v.owner                   /* match view owner name */
and o.name = v.view_name               /* match view name */
and r.obj# = o.obj#                    /* view has rls */
and o.type# = 4                        /* make sure the object is a view */
/

comment on table ALL_POLICIES is
'All policies for objects if the user has system privileges or owns the objects'
/
comment on column ALL_POLICIES.OBJECT_OWNER is
'Owner of the table or view'
/
comment on column ALL_POLICIES.OBJECT_NAME is
'Name of the table or view'
/
comment on column ALL_POLICIES.POLICY_NAME is
'Name of the policy'
/
comment on column ALL_POLICIES.PF_OWNER is
'Owner of the policy function'
/
comment on column ALL_POLICIES.PACKAGE is
'Name of the package containing the policy function'
/
comment on column ALL_POLICIES.FUNCTION is
'Name of the policy function'
/
comment on column ALL_POLICIES.SEL is
'If YES, policy is applied to query on the object'
/
comment on column ALL_POLICIES.INS is
'If YES, policy is applied to insert on the object'
/
comment on column ALL_POLICIES.UPD is
'If YES, policy is applied to update on the object'
/
comment on column ALL_POLICIES.DEL is
'If YES, policy is applied to delete on the object'
/
comment on column ALL_POLICIES.CHK_OPTION is
'Is check option enforced for this policy?'
/
comment on column ALL_POLICIES.ENABLE is
'Is this policy is enabled?'
/
drop public synonym ALL_POLICIES
/
create public synonym ALL_POLICIES for ALL_POLICIES
/
grant select on ALL_POLICIES to PUBLIC with grant option
/
create or replace view USER_POLICIES (OBJECT_NAME, POLICY_NAME, 
			  PF_OWNER, PACKAGE, FUNCTION, SEL, INS,
			  UPD, DEL, CHK_OPTION, ENABLE) 
as
select o.name, r.pname, r.pfschma, r.ppname, r.pfname,
       decode(bitand(r.stmt_type,1), 0, 'NO', 'YES'),
       decode(bitand(r.stmt_type,2), 0, 'NO', 'YES'),
       decode(bitand(r.stmt_type,4), 0, 'NO', 'YES'),
       decode(bitand(r.stmt_type,8), 0, 'NO', 'YES'),
       decode(r.check_opt, 0, 'NO', 'YES'),
       decode(r.enable_flag, 0, 'NO', 'YES')
from user$ u, obj$ o, rls$ r
where u.user# = o.owner# 
and r.obj# = o.obj#
and (o.owner# = userenv('SCHEMAID')) /* user is the owner of the object */
/
comment on table USER_POLICIES is
'All row level security policies for tables or views owned by the user'
/
comment on column USER_POLICIES.OBJECT_NAME is
'Name of the table or view'
/
comment on column USER_POLICIES.POLICY_NAME is
'Name of the policy'
/
comment on column USER_POLICIES.PF_OWNER is
'Owner of the policy function'
/
comment on column USER_POLICIES.PACKAGE is
'Name of the package containing the policy function'
/
comment on column USER_POLICIES.FUNCTION is
'Name of the policy function'
/
comment on column USER_POLICIES.SEL is
'If YES, policy is applied to query on the object'
/
comment on column USER_POLICIES.INS is
'If YES, policy is applied to insert on the object'
/
comment on column USER_POLICIES.UPD is
'If YES, policy is applied to update on the object'
/
comment on column USER_POLICIES.DEL is
'If YES, policy is applied to delete on the object'
/
comment on column USER_POLICIES.CHK_OPTION is
'Is check option enforced for this policy?'
/
comment on column USER_POLICIES.ENABLE is
'Is this policy is enabled?'
/
drop public synonym USER_POLICIES
/
create public synonym USER_POLICIES for USER_POLICIES
/
grant select on USER_POLICIES to PUBLIC with grant option
/
