ACL — Access control list, a feature provided by oracle. It adds security at row level, and column level.
Prereq for ACL — XDB component should be in valid state.
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL — creates the ACL list
Using DBMS_XDB.setACL privileges are lined up to the users. It associates ACL with ACE elements.
Each ACE element contains a principle and privileges, and ACL contains list of ACE elements. Table xdb$acl contains list of ACE’s, ie:
select * from xdb.xdb$acl;
To retrieve the ACL information:
select A.OBJECT_ID, ANY_PATH, R.* from RESOURCE_VIEW, XDB.XDB$ACL A,
XMLTABLE( xmlNamespaces(
default ‘http://xmlns.oracle.com/xdb/XDBResource.xsd’ — It’s default
),
‘$RES/Resource’
passing RES as “RES”
COLUMNS
OWNER PATH ‘Owner’,
CREATOR PATH ‘Creator’,
DATE_CREATED PATH ‘CreationDate’,
MODIFIER PATH ‘LastModifier’,
DATE_LAST_MODIFIED PATH ‘ModificationDate’
) R
where ref(A) = XMLCast(
XMLQuery(
‘declare default element namespace “http://xmlns.oracle.com/xdb/XDBResource.xsd”; (: 🙂
fn:data($RES/Resource/XMLRef)’
passing RES as “RES”
returning content
) as REF XMLType
)
/
You will see the xml resource file, to view the contents:
set long 10000
select xdburitype(‘/sys/acls/mail_server_permissions.xml’).getclob() from dual;
This will give you ACE information.
Using XDB:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/appaman.htm