AUD$ and LINK$ has always been the favorite for auditor to pick on.
Prior to 9i, Oracle was very promiscuous with regards to the dollar tables in the data dictionary. It was very easy to get access. Quite frankly, you could have your way with the SYS schema, back in those days.
The SELECT ANY TABLE privilege was sufficient to access any dollar table in Oracle 8i. So if you gave someone this access to see any non-SYS table in the database, they also got to see the dollar tables.
In Oracle 9i, the SELECT ANY DICTIONARY privilege was introduced to give access to the dollar tables. The SELECT ANY TABLE privilege no longer gave you the dollar tables, unless you changed O7_DICTIONARY_ACCESSIBILITY to true ( which is usually advise against).
In order to find out how non-DBA database user can directly access to these tables some tests were conducted and conclusion as follow :
Conclusion :
With O7_DICTIONARY_ACCESSIBILITY=false
Granted : AUD$ LINK$
select any table privilege Not accessible Not accessible
select_catalog_role role Not accessible Not accessible
select any dictionary Accessible Insufficient privileges
Explicit grant Accessible Accessible
With O7_DICTIONARY_ACCESSIBILITY=true
Granted : AUD$ LINK$
select any table privilege Accessible Insufficient privileges
select_catalog_role role Not accessible Not accessible
select any dictionary Accessible Insufficient privileges
Explicit grant Accessible Accessible
However we do find out that if EXP_FULL_DATABASE role is granted to a user. The user can access the SYS.LINK$ table on special circumstance via Oracle Export utility and during logon from a third party software call PL/SQL Developer. But direct select on the table will still give
ORA-00942: table or view does not exist ( if select any table privilege not granted )
ORA-01031: insufficient privileges depending ( if select any table privilege not granted )
Audit all on LINK$ will still have an audit trail with returncode=0 for select on SYS.LINK$ if user is granted with EXP_FULL_DATABASE role and is doing a export or using PL/SQL Developer.
So in short O7_DICTIONARY_ACCESSIBILITY=false is recommended and select any dictionary is to be granted with care.
Test results :
With O7_DICTIONARY_ACCESSIBILITY=FALSE
TEST 1 : Grant select any tables
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect /as sysdba
Connected.
SQL> grant select any table to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
TEST 2 : Grant select_catalog_role
SQL> connect /as sysdba
Connected.
SQL> revoke select any table from test;
Revoke succeeded.
SQL> grant select_catalog_role to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
TEST 3 : Grant select any dictionary
SQL> connect /as sysdba
Connected.
SQL> revoke select_catalog_role from test;
Revoke succeeded.
SQL> grant select any dictionary to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP USERID
---------- ---------- ---------- --------- ------------------------------
USERHOST
--------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------
ACTION# RETURNCODE OBJ$CREATOR
---------- ---------- ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------
AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER
---------------- ------------------------------ ------------------------------
NEW$NAME
--------------------------------------------------------------------------------
SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE
------------------- ---------- ------------ ------------ -------------
LOGOFF$DEAD LOGOFF$TI
----------- ---------
COMMENT$TEXT
--------------------------------------------------------------------------------
CLIENTID
----------------------------------------------------------------
SPARE1
--------------------------------------------------------------------------------
SPARE2
----------
OBJ$LABEL
--------------------------------------------------------------------------------
SES$LABEL
--------------------------------------------------------------------------------
PRIV$USED SESSIONCPU
---------- ----------
NTIMESTAMP#
---------------------------------------------------------------------------
PROXY$SID USER$GUID INSTANCE# PROCESS#
---------- -------------------------------- ---------- ----------------
XID
----------------
AUDITID SCN
---------------------------------------------------------------- ----------
DBID
----------
SQLBIND
--------------------------------------------------------------------------------
SQLTEXT
--------------------------------------------------------------------------------
3311417 1 8 TEST
TESTSVR
pts/7
59 0 TEST
TRIG1
TEST
T4
INSERT
oracle
151
15-JUN-10 02.13.30.946734 AM
0 4204
06002A001A450000
0
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
With O7_DICTIONARY_ACCESSIBILITY=true
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
SQL>
SQL> startup force
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 2074048 bytes
Variable Size 285215296 bytes
Database Buffers 230686720 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> revoke select any dictionary from test;
Revoke succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
TEST 1 : Grant select any tables
SQL> connect /as sysdba
Connected.
SQL> grant select any table to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP USERID
---------- ---------- ---------- --------- ------------------------------
USERHOST
--------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------
ACTION# RETURNCODE OBJ$CREATOR
---------- ---------- ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------
AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER
---------------- ------------------------------ ------------------------------
NEW$NAME
--------------------------------------------------------------------------------
SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE
------------------- ---------- ------------ ------------ -------------
LOGOFF$DEAD LOGOFF$TI
----------- ---------
COMMENT$TEXT
--------------------------------------------------------------------------------
CLIENTID
----------------------------------------------------------------
SPARE1
--------------------------------------------------------------------------------
SPARE2
----------
OBJ$LABEL
--------------------------------------------------------------------------------
SES$LABEL
--------------------------------------------------------------------------------
PRIV$USED SESSIONCPU
---------- ----------
NTIMESTAMP#
---------------------------------------------------------------------------
PROXY$SID USER$GUID INSTANCE# PROCESS#
---------- -------------------------------- ---------- ----------------
XID
----------------
AUDITID SCN
---------------------------------------------------------------- ----------
DBID
----------
SQLBIND
--------------------------------------------------------------------------------
SQLTEXT
--------------------------------------------------------------------------------
3311417 1 8 TEST
TESTSVR
pts/7
59 0 TEST
TRIG1
TEST
T4
INSERT
oracbk
151
15-JUN-10 02.13.30.946734 AM
0 4204
06002A001A450000
SQL> select * from sys.link$
2 ;
select * from sys.link$
*
ERROR at line 1:
ORA-01031: insufficient privileges
TEST 2 : Grant select_catalog_role
SQL> connect /as sysdba
Connected.
SQL> revoke select any table from test;
Revoke succeeded.
SQL> grant select_catalog_role to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
TEST 3 : Grant select any dictionary
SQL> connect /as sysdba
Connected.
SQL> revoke select_catalog_role from test;
Revoke succeeded.
SQL> grant select any dictionary to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP USERID
---------- ---------- ---------- --------- ------------------------------
USERHOST
--------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------
ACTION# RETURNCODE OBJ$CREATOR
---------- ---------- ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------
AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER
---------------- ------------------------------ ------------------------------
NEW$NAME
--------------------------------------------------------------------------------
SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE
------------------- ---------- ------------ ------------ -------------
LOGOFF$DEAD LOGOFF$TI
----------- ---------
COMMENT$TEXT
--------------------------------------------------------------------------------
CLIENTID
----------------------------------------------------------------
SPARE1
--------------------------------------------------------------------------------
SPARE2
----------
OBJ$LABEL
--------------------------------------------------------------------------------
SES$LABEL
--------------------------------------------------------------------------------
PRIV$USED SESSIONCPU
---------- ----------
NTIMESTAMP#
---------------------------------------------------------------------------
PROXY$SID USER$GUID INSTANCE# PROCESS#
---------- -------------------------------- ---------- ----------------
XID
----------------
AUDITID SCN
---------------------------------------------------------------- ----------
DBID
----------
SQLBIND
--------------------------------------------------------------------------------
SQLTEXT
--------------------------------------------------------------------------------
3311417 1 8 TEST
TESTSVR
pts/7
59 0 TEST
TRIG1
TEST
T4
INSERT
oracbk
151
15-JUN-10 02.13.30.946734 AM
0 4204
06002A001A450000
0
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
SPECIAL TEST : granted with RESOURCE, CONNECT, EXP_FULL_DATABASE, IMP_FULL_DATABASE
TEST 1a : Granted with SELECT ANY DICTIONARY
SQL> grant select any dictionary to test;
Grant succeeded.
SQL> select * from SYS.LINK$;
select * from SYS.LINK$
*
ERROR at line 1:
ORA-01031: insufficient privileges
TEST 1b : Not Granted with SELECT ANY DICTIONARY
SQL> revoke select any dictionary from test;
Revoke succeeded.
SQL> select * from SYS.LINK$;
select * from SYS.LINK$
*
ERROR at line 1:
ORA-00942: table or view does not exist
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment