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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation