Ask the Expert

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?

    Requires Free Membership to View

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 (9.2.0.5 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.

This was first published in December 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: