Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Relying on the system to grant system objects

Our 9i databases now have the "07_dictionary_compatibility" set to false for security (Sarbanes Oxley) purposes. Our DBAS have the DBA role and select/execute/delete catalog_role as well as select_any_dictionary, yet we are unable to access certain sys tables (ex. sys.link$, aud$) nor certain sys.dbms packages. We also do not have access to the 'sys' / 'system' account nor sysdba, only the install DBA does. However, we need to rely on system to grant us these system objects as we encounter them. Are we missing some role/privilege as a DBA? Is there any easy recommendation/solution/script to address this access issue?
The SELECT ANY DICTIONARY system privilege should allow you to select from any data dictionary object in the SYS schema. If you grant that privilege to a database user, that user should be able to select from any SYS-owned data dictionary object. If you're seeing something different, I'd file a bug with support.

On Unix systems, the SYSDBA privilege is automatically available to any user in the OSDBA group. At install time, the name of the OSDBA and OSOPER groups are chosen and then are linked into the database server. If you're in the group, you should be able to connect to the database using "sqlplus '/ as sysdba'" and not be prompted for a password. Alternatively, if you have an exclusive passwordfile, you should be able to "grant sysdba to dan;" and then connect remotely using "sqlplus 'dan/password@mydb as sysdba'". You should be able to see what OS groups were chosen for OSDBA and OSOPER by viewing the $ORACLE_HOME/rdbms/lib/config.c file on your system. Here's what config.c looks like for me ( on linux):

/***************** start config.c *********************/
/* SS_DBA_GRP defines the UNIX group ID for adminstrative access. */
/* Refer to the Installation and User's Guide for further information. */

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "dba"

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};
/***************** end config.c *********************/

On Windows systems, the ORA_DBA group is the OSDBA group and any member of that group should be able to "sqlplus '/ as sysdba'" to connect. There are ways to use instance-specific groups to allow a user to only use SYSDBA for certain instances too--read the docs if that's necessary.

Dig Deeper on Oracle database security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.