Q

Relying on the system to grant system objects

This Content Component encountered an error
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 (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

Dig deeper on Oracle database security

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close