Tags

,

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