Three Tier Oracle Security in London ~ Paul M. Wright

(nix, oracle, java, www, cloud ) intersect (safety, security, reliability, integrity)

Three Tier Oracle Security in London ~ Paul M. Wright RSS Feed
 

DBA_OBJ_PRIVS

It is important for a SYS user to know the identity of a package that they are executing as their privileges will overide the DEFINER rights of the package owner.
The method of identifying an object’s identity in Oracle is traditionally the pairing of the schema.object names.
I have shown in “CREATE USER to SYSDBA” paper that this can played with in such a way as to escalate a user’s privileges to SYSDBA.
The escalation works due to ambiguity of type and namespace when calling an object.
What is needed in the Oracle DB is more accurate and precise communication of object identity and type.
This is especially the case with listing privilege assignments.
We have all had the “privilege” of having to use DBA_TAB_PRIVS to list grants

SQL> desc dba_tab_privs;
--
Name Null? Type
------------------------------------------------------------------------------
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)

Question:Is this view a precise description of specifc object privileges?
I would say not ~ firstly because it relies on the pairing of owner and table_name (object_name) to uniquely identify each object which I have shown to be tenuous.
Secondly the view does not specify the object_type which leads to confusion especially with grants via private synonyms which point to other objects in other schemas.
What is needed is a DBA_OBJ_PRIVS view which contains the object_type in addition to the information in DBA_TAB_PRIVS. This is very easy to do:

--AS SYS
CREATE OR replace VIEW dba_obj_privs AS select ue.name grantee, u.name owner , o.name object_name, ur.name grantor , tpm.name privilege,
decode(mod(oa.option$,2), 1, 'YES', 'NO') grantable,
decode(bitand(oa.option$,2), 2, 'YES', 'NO') heirarchy,
OBJECT_TYPE
from sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
table_privilege_map tpm, DBA_OBJECTS
where oa.obj# = o.obj#
and oa.grantor# = ur.user#
and oa.grantee# = ue.user#
and oa.col# is null
and oa.privilege# = tpm.privilege
and u.user# = o.owner#
AND DBA_OBJECTS.OBJECT_ID=oa.obj#;

Then grants can be selected by object_type:

SQL> SELECT * FROM DBA_OBJ_PRIVS WHERE ROWNUM < 10 AND grantee='SYSTEM' AND OBJECT_TYPE='PACKAGE';
--
GRANTEE OWNER OBJECT_NAME GRANTOR PRIVILEGE GRA HEI OBJECT_TYPE
----------------------------------------------------------------------------------
SYSTEM SYS DBMS_REPCAT SYS EXECUTE NO NO PACKAGE
SYSTEM SYS DBMS_ALERT SYS EXECUTE NO NO PACKAGE
SYSTEM SYS DBMS_DEFER_IMPORT_INTERNAL SYS EXECUTE NO NO PACKAGE
SYSTEM SYS DBMS_SYS_ERROR SYS EXECUTE NO NO PACKAGE
SYSTEM SYS DBMS_AQ_IMPORT_INTERNAL SYS EXECUTE YES NO PACKAGE
SYSTEM SYS DBMS_AQELM SYS EXECUTE YES NO PACKAGE
SYSTEM SYS DBMS_AQ SYS EXECUTE YES NO PACKAGE
SYSTEM SYS DBMS_AQADM SYS EXECUTE YES NO PACKAGE
SYSTEM SYS DBMS_RULE_EXIMP SYS EXECUTE YES NO PACKAGE

This view now lists the object_type of each object in the grant which makes the database a lot easier to manage.

There is still an ambiguous aspect to this view though. The GRANTOR column is defined as:

SQL> SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE OWNER='SYS' AND TABLE_NAME='DBA_TAB_PRIVS' AND COLUMN_NAME='GRANTOR';
--
COMMENTS
--------------------------------------------------------------------------------
Name of the user who performed the grant

But this is incorrect, as the GRANTOR column does not show the Name of the user who performed the grant. It is usually the SCHEMA owner of the granted object OR it is the actual GRANTOR if the grant is done WITH GRANT OPTION. As the majority of grants are done by a power user e.g. DBA with GRANT ANY OBJECT PRIVILEGE system privilege, the GRANTOR column usually shows the schema owner and NOT the user that actually performed the grant.
The GRANTOR column value is also changed automatically by packages such as DBMS_REDEFINITION (More to come on this in a later post). The GRANTOR column is therefore not consistent which is a shame as it is one of the few columns in Oracle which records user actions in a file system like way i.e. this grant was done by this user. This type of metadata would be useful for a post incident forensic analysis but unfortunately the GRANTOR column is not reliable at this current time.

The lack of metadata regarding users actions in Oracle makes the use of a Database Activity Monitoring System(DAMS) more important so as to persist actions in a recorded audit trail. To reduce false positives and false negatives a DAMS should be able to identify owner and type from an SQL statement. Network based systems cannot do this as they use plain pattern matching but some host based systems can.
e.g. alert if a GRANT is made to PUBLIC on a SYS object using Sentrigo Hedgehog.

statement contains 'PUBLIC' and schema='SYS' and cmdtype contains $DCL_CMDTYPES

More detail in the future SANS DAMS Course in London on Saturday 5 December 2009 at the ExCeL centre.

Leave a Reply

You must be logged in to post a comment.