Solved

create a restricted login to Ent. Manager Console

Posted on 2007-03-29
2
1,182 Views
Last Modified: 2013-12-19
I need to lock down our 9.x Enterprise manager. My Developers are running wild !
They were granted SYSDBA access and now I need to restrict them to only submit jobs and review their SQL and create queries. I've tried the SQLADMIN role, but I cannot get a user with only that role to login to the ent. manager console.
0
Comment
Question by:DBA_Frog
2 Comments
 
LVL 7

Expert Comment

by:gattu007
ID: 18824739
Instead of granting default roles..... create custom role with required privileges ... grant only new role...


Hope this helps
0
 
LVL 8

Accepted Solution

by:
YANN0S earned 250 total points
ID: 18842517
From Oracle Documentation:

Before You Use the Oracle TopSessions Chart
Before you can use the TopSessions chart on pre-Oracle 9i databases, you must first run a SQL script that defines the OEM_MONITOR role which contains all the privileges required to use the Diagnostics Pack products. Granting the
OEM_MONITOR role is the best way to ensure that a user has the correct privileges to use the TopSessions functions. The OEM_MONITOR role has been granted more privileges in the 9i version that help to provide better functionality and performance.

For 9i databases, the SQL script, catsnmp.sql, is run by default as part of the 9i database creation process. For all versions of the database prior to 9i, you must manually run a SQL script to define the OEM_MONITOR role and create the necessary views.

For versions 8.1.5, 8.1.6, and 8.1.7 of the database, you must run the script
catsnmp_8i.sql. For versions 8.0.5 and 8.0.6 of the database, you must run the script catsnmp_80.sql. For version 7.3.4 of the database, you must run script
catsnmp_734.sql. The selected script must be executed by a user with SYS privileges.

The sql files are located in the Oracle home of the Oracle Enterprise Manager installation, $ORACLE_HOME/SYSMAN/ADMIN. You must run the appropriate version of the SQL script manually on all pre-9i databases.

The OEM_MONITOR role must be granted to any user wishing to use the Oracle Diagnostics Pack. Performance Manager does not require any of these new views in order to run, but if you attach to a database that does not have the views, or your process does not have the required privileges, or the OEM_MONITOR role does not exist, then an informational message displays.


And in another document:

Granting OEM_MONITOR Role to Database Preferred Credentials

Beginning with Oracle 8.0.6 databases and higher, the OEM_MONITOR role is created by the Oracle database creation scripts. This role permits access to database functionality within Enterprise Manager, such as registering events against a database or browsing through the objects in a database via the Console Navigator tree. These types of functionality require database credentials on which to perform these operations. Rather than granting the powerful DBA role to the database credentials, many administrators prefer to provide only the necessary privileges required to do these operations. Granting the OEM_MONITOR role to the database credentials, ensures that the user has the minimum sufficient privileges required for these operations.

Granting OEM_MONITOR Role to Database Preferred Credentials
Beginning with Oracle 8.0.6 databases and higher, the OEM_MONITOR role is created by the Oracle database creation scripts. This role permits access to database functionality within Enterprise Manager, such as registering events against a database or browsing through the objects in a database via the Console Navigator tree. These types of functionality require database credentials on which to perform these operations. Rather than granting the powerful DBA role to the database credentials, many administrators prefer to provide only the necessary privileges required to do these operations. Granting the OEM_MONITOR role to the database credentials, ensures that the user has the minimum sufficient privileges required for these operations.



--------------------------------------------------------------------------------
Note:
You need to create the OEM_MONITOR role using the SYS account.

--------------------------------------------------------------------------------
 



You must perform the following steps:

Create a role called OEM_MONITOR

drop role OEM_MONITOR;
create role OEM_MONITOR:

Grant the "connect" role to OEM_MONITOR

grant connect to OEM_MONITOR;

Grant the system privileges "analyze any" and "create table" to OEM_MONITOR

grant analyze any to OEM_MONITOR;
grant create table to OEM_MONITOR;

Create the SELECT_CATALOG_ROLE role as defined in sc_role.sql.

Grant the SELECT_CATALOG_ROLE to the OEM_MONITOR role

grant select_catalog_role to OEM_MONITOR;

You are now ready to grant the OEM_MONITOR role to the database user that will be used as "database preferred credentials" in Enterprise Manager. In addition to granting the OEM_MONITOR role to a user, you must also ensure that the QUOTA for the user account is set to UNLIMITED.

The "Continued Row" event test needs to analyze results into a table so it needs both the "analyze any" and "create table" privileges.



--------------------------------------------------------------------------------
Note:
The "analyze any" privilege is used by the "index rebuild" event to compute statistics.

--------------------------------------------------------------------------------
 



0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now