Managing Oracle Databases › Registering Oracle Databases › Oracle User Privileges

Oracle User Privileges

If you want to use the Oracle Registration Wizard to create users, you need to create a special Oracle user (login) to allow CA SRM access to the database instance information. To help with this, you can find a sample script, CreateOracleUser.sql, under the CA SRM Data\Database\Configuration directory. Install the CreateOracleUser.sql script file on each managed object that you want to create users for with the wizard.

The following provides version information necessary to remotely create users while running the Oracle Registration Wizard:

• When the user has the Oracle 9.0.1 client, the Oracle ODBC driver version needs to be 9.0.1.3 or higher in order to connect to the Oracle server version 9i or higher.
• When the user has the Oracle 9.2 client, the Oracle ODBC driver version should be 9.2.0.2.0 or higher in order to connect to the Oracle server version 9i or higher.
• You cannot remotely create a user with the wizard when using the 8i client with Oracle 9i database (or above). This is a limitation of the Oracle ODBC driver.

Note: These ODBC driver versions are required only if you want to create a user with the wizard. For data collection these versions are not required.

In order to collect data from the Oracle database, the user needs the following privileges. If you create a user in the Oracle Registration Wizard, these privileges and synonyms are automatically created:

• Connect permission to connect to Oracle
• Create procedure and create table permissions
• Create synonym privilege for CA SRM user
• Unlimited tablespace
• Permission for analyzing any object
• Permission to execute on sys.dbms_space
• Select permission on the following tables:
  • sys.dba_data_files.
  • sys.dba_extents
  • sys.dba_free_space.
  • v_$version.
  • v_$filestat.
  • v_$parameter.
  • v_$log.
  • v_$log_history.
  • v_$logfile.
  • v_$archived_log.
  • v_$backup.
  • sys.filext$.
  • sys.dba_tablespaces.
  • sys.dba_all_tables.
  • sys.dba_clusters.
  • sys.dba_indexes.
  • sys.dba_tab_partitions.
  • sys.dba_ind_partitions.
  • sys.dba_segments.
  • sys.dba_tables.
  • sys_dba_users.
  • sys.dba_temp.
  • sys.v_$tempstat
  • sys.v_$temp_space_header.
  • sys.dba_tab_privs

Without these privileges, the data collection cannot be done successfully.

In addition, create the following synonyms for the user:

• v$version for sys.v_$version;
• v$filestat for sys.v_$filestat
• v$parameter for sys.v_$parameter.
• v$log for sys.v_$log.
• v$log_history for sys.v_$log_history.
• v$logfile for sys.v_$logfile.
• v$archived_log for sys.v_$archived_log.
• v$backup for sys.v_$backup.
• dba_data_files for sys.dba_data_files.
• dba_extents for sys.dba_extents.
• dba_free_space for sys.dba_free_space.
• dba_tablespaces for sys.dba_tablespaces.
• dba_all_tables for sys.dba_all_tables.
• dba_clusters for sys.dba_clusters.
• dba_indexes for sys.dba_indexes.
• dba_tab_partitions for sys.dba_tab_partitions.
• dba_ind_partitions for sys.dba_ind_partitions.
• dba_segments for sys.dba_segments.
• dba_tables for sys.dba_tables.
• dba_users for sys.dba_users;
• dba_temp_files for sys.dba_temp_files;
• v$tempstat for sys.v_$tempstat;
• v$temp_space_header for sys.v_$temp_space_header;
• dba_tab_privs for sys.dba_tab_privs;