Home » RDBMS Server » Enterprise Manager » Able to create custom user to connect db console ? (aws rds oracle instance ( 11.2.0.4.3 ) linux 6.2 )
Able to create custom user to connect db console ? [message #634849] Mon, 16 March 2015 16:17
kesavansundaram
Messages: 181
Registered: October 2007
Location: MUMBAI
Senior Member

Team,
I am exploring OEM option with AWS RDS Oracle instance. After associaitng OEM option to RDS Oracle instance, SYSMAN user is getting poplulated.
In AWS RDS Oracle instance, end user will not have access to SYS & SYSTEM. As an Oracle DBA, I am in a posistion to design OEM db console login credential for our end user ( customers ).

We have the monitoring user DBSNMP in RDS Oracle instance with below set of privileges granted to it. Instead of giving this DBSNMP and its password to our end user, Do we have any possibiltity
to create one custom user and replicate these DBSNMP permissions to that user ? We would like to grant only read-only permission from the below list in order to avoid data corruption in db console repository database.

Please guide me here. I would like to provide one user and password to my end user in order to connect db console using the web url ( e..g https://attdb.********.rds.amazonaws.com:1158/em ) without affecting the repository
data on this rds intance. Is it possible to implement ? Please guide me.

1---privilegs/roles granted to DBSNMP

Select owner, PRIVILEGE, GRANTEE, GRANTABLE, GRANTOR, TABLE_NAME from sys.dba_tab_privs
where grantee = 'DBSNMP' order by PRIVILEGE,table_name;

OWNER PRIVILEGE GRANTEE GRA GRANTOR TABLE_NAME
------------------------------ --------------- --------------- --- --------------- ---------------------
SYS EXECUTE DBSNMP NO SYS DBMS_MANAGEMENT_PACKS
SYS EXECUTE DBSNMP NO SYS DBMS_SERVER_ALERT
SYS EXECUTE DBSNMP YES SYS SYS_PLSQL_8349_835_1
APPQOSSYS SELECT DBSNMP NO APPQOSSYS WLM_CLASSIFIER_PLAN
APPQOSSYS SELECT DBSNMP NO APPQOSSYS WLM_METRICS_STREAM
APPQOSSYS SELECT DBSNMP NO APPQOSSYS WLM_MPA_STREAM
APPQOSSYS SELECT DBSNMP NO APPQOSSYS WLM_VIOLATION_STREAM

7 rows selected.

set lines 1000 pages 200
col grantee for a15
col privilege for a30
SELECT * FROM DBA_SYS_PRIVS WHERE grantee = 'DBSNMP';

GRANTEE PRIVILEGE ADM
--------------- ------------------------------ ---
DBSNMP CREATE PROCEDURE NO
DBSNMP UNLIMITED TABLESPACE NO
DBSNMP SELECT ANY DICTIONARY NO
DBSNMP CREATE TABLE NO

SQL> select * from dba_role_privs where grantee = 'DBSNMP';

GRANTEE GRANTED_ROLE ADM DEF
--------------- ------------------------------ --- ---
DBSNMP OEM_MONITOR NO YES



2---Content of OEM_MONITOR role

SQL> Select owner, PRIVILEGE, GRANTEE, GRANTABLE, GRANTOR, TABLE_NAME from sys.dba_tab_privs
2 where grantee = 'OEM_MONITOR' order by PRIVILEGE,table_name;

OWNER PRIVILEGE GRANTEE GRA GRANTOR TABLE_NAME
--------------- --------------- --------------- --- --------------- ---------------------------------------
SYS DEQUEUE OEM_MONITOR NO SYS ALERT_QUE
DBSNMP EXECUTE OEM_MONITOR NO DBSNMP BSLN
DBSNMP EXECUTE OEM_MONITOR NO DBSNMP BSLN_INTERNAL
DBSNMP EXECUTE OEM_MONITOR NO DBSNMP BSLN_METRIC_SET
DBSNMP EXECUTE OEM_MONITOR NO DBSNMP BSLN_METRIC_T
DBSNMP EXECUTE OEM_MONITOR NO DBSNMP BSLN_OBSERVATION_SET
DBSNMP EXECUTE OEM_MONITOR NO DBSNMP BSLN_OBSERVATION_T
DBSNMP EXECUTE OEM_MONITOR NO DBSNMP BSLN_STATISTICS_SET
DBSNMP EXECUTE OEM_MONITOR NO DBSNMP BSLN_STATISTICS_T
DBSNMP EXECUTE OEM_MONITOR NO DBSNMP BSLN_VARIANCE_SET
DBSNMP EXECUTE OEM_MONITOR NO DBSNMP BSLN_VARIANCE_T
SYS EXECUTE OEM_MONITOR NO SYS DBMS_AQ
SYS EXECUTE OEM_MONITOR NO SYS DBMS_AQADM
SYS EXECUTE OEM_MONITOR NO SYS DBMS_DRS
SYS EXECUTE OEM_MONITOR NO SYS DBMS_MONITOR
SYS EXECUTE OEM_MONITOR NO SYS DBMS_SERVER_ALERT
SYS EXECUTE OEM_MONITOR NO SYS DBMS_SYSTEM
SYS EXECUTE OEM_MONITOR NO SYS DBMS_WORKLOAD_REPOSITORY
DBSNMP EXECUTE OEM_MONITOR NO DBSNMP MGMT_RESPONSE
DBSNMP EXECUTE OEM_MONITOR NO DBSNMP MGMT_UPDATE_DB_FEATURE_LOG
DBSNMP SELECT OEM_MONITOR NO DBSNMP BSLN_BASELINES
DBSNMP SELECT OEM_MONITOR NO DBSNMP BSLN_METRIC_DEFAULTS
DBSNMP SELECT OEM_MONITOR NO DBSNMP BSLN_STATISTICS
DBSNMP SELECT OEM_MONITOR NO DBSNMP BSLN_THRESHOLD_PARAMS
DBSNMP SELECT OEM_MONITOR NO DBSNMP BSLN_TIMEGROUPS
DBSNMP SELECT OEM_MONITOR NO DBSNMP MGMT_BASELINE
DBSNMP SELECT OEM_MONITOR NO DBSNMP MGMT_BASELINE_SQL
DBSNMP SELECT OEM_MONITOR NO DBSNMP MGMT_BSLN_BASELINES
DBSNMP SELECT OEM_MONITOR NO DBSNMP MGMT_BSLN_DATASOURCES
DBSNMP SELECT OEM_MONITOR NO DBSNMP MGMT_BSLN_INTERVALS
DBSNMP SELECT OEM_MONITOR NO DBSNMP MGMT_BSLN_METRICS
DBSNMP SELECT OEM_MONITOR NO DBSNMP MGMT_BSLN_STATISTICS
DBSNMP SELECT OEM_MONITOR NO DBSNMP MGMT_BSLN_THRESHOLD_PARMS
DBSNMP SELECT OEM_MONITOR NO DBSNMP MGMT_HISTORY
DBSNMP SELECT OEM_MONITOR NO DBSNMP MGMT_HISTORY_SQL
DBSNMP SELECT OEM_MONITOR NO DBSNMP MGMT_LATEST
DBSNMP SELECT OEM_MONITOR NO DBSNMP MGMT_LATEST_SQL

37 rows selected.


SQL> SELECT * FROM DBA_SYS_PRIVS WHERE grantee = 'OEM_MONITOR';

GRANTEE PRIVILEGE ADM
--------------- ------------------------------ ---
OEM_MONITOR MANAGE ANY QUEUE NO
OEM_MONITOR CREATE JOB NO
OEM_MONITOR CREATE SESSION NO
OEM_MONITOR ADVISOR NO
OEM_MONITOR SELECT ANY DICTIONARY NO
OEM_MONITOR ANALYZE ANY NO
OEM_MONITOR ANALYZE ANY DICTIONARY NO

7 rows selected.



Thank you very much
Previous Topic: ERROR CREATING REPOSITORY
Next Topic: EnterPrise manager stop
Goto Forum:
  


Current Time: Sun Jun 20 17:43:05 CDT 2021