oracle oem roles

Hi
How to assign roles in Oracle OEM for a developer to view explain plans?
The developer should not be able to modify anything.

Can this be done?

Thanks
Adi
aadityaraavulaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
1. Create a PLAN_TABLE using the script ORACLE_HOME/rdbms/admin/utlxplan.sql to create the table in the data owner's schema.

Or you can grant privileges on the PLAN_TABLE from the data owner schema to the developer, and create a public synonym or private synonym in the data owner schema.

 2. grant PLUSTRACE role to schema owner
0
Mark GeerlingsDatabase AdministratorCommented:
I agree with step #1 that dvz suggested.  Then, instead of "Or you can grant privileges ...", I think you need to grant select, insert and delete on the PLAN_TABLE to the developer.  And, I would suggest granting the PLUSTRACE role to both the schema owner and the developer.

As long as you don't give the developer the resource role, and don't grant the developer any insert, update or delete privileges on any tables, the developer won't be able to modify any data.  (This assumes of course that there are no tables with PUBLIC privileges.)
0
aadityaraavulaAuthor Commented:
Can we do this directly in OEM by setting any roles?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
In new versions of Oracle the PLAN_TABLE is accessible by PUBLIC.  There is no longer a need to create it and do all the grants.

There really should be nothing to do either from the command line or OEM.

I'm guessing the developer tried to generate a plan and it failed?

What is the exact version of Oracle (all 4 numbers please)?
0
Mark GeerlingsDatabase AdministratorCommented:
I think you will need to create a PLAN_TABLE in some schema in your database (unless someone has done this for you already).  After that is done, you should be able to grant the PLUSTRACE role via OEM.  I'm not sure if that by itself is enough without explicit grants on the PLAN_TABLE, but you can try.
0
aadityaraavulaAuthor Commented:
we have an exadata running multiple databases with 11.2.0.3 mostly.
OEM is in 12.1.0.3.

We want the developers to view explain plans without being able to modify
0
DavidSenior Oracle Database AdministratorCommented:
The tricky thing about any GUI such as OEM is that it may add complexity and thus, mask errors. I'm curious to see where slight goes with this.

Obviously, in order for one account (say, developer) to view the objects in someone else's schema, that first account must be given SELECT rights on the object.  In a production instance, one might grant a developer some kind of read-only role when needed.  Generally, my customers' requirements prevent developers from owning any objects in Production.

The PLAN_TABLE object is refreshed each time it is accessed.  A developer cannot do an EXPLAIN_PLAN unless s/he is writing to the PLAN_TABLE.  The catch-22 is that with multiple developers sharing a common PLAN_TABLE, it's very easy for one's results to be overlaid without notice.

.
0
slightwv (䄆 Netminder) Commented:
>>We want the developers to view explain plans without being able to modify

Have you tried it?

Again, it has been created for you:
http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5152.htm
PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans.

While a PLAN_TABLE table is automatically set up for each user, you can use the SQL script utlxplan.sql to manually create a local PLAN_TABLE in your schema.
0
aadityaraavulaAuthor Commented:
I tried creating a DEV admin account with Connect target read only priv, and EM_ALL_VIEW role.
But obviously, there is more needed for this.
0
slightwv (䄆 Netminder) Commented:
For 11.2 the PLAN_TABLE is there and accessible.

Here is my test case:
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> create user dev_test identified by dev_test;

User created.

SQL> grant create session to dev_test;

Grant succeeded.

SQL> conn dev_test/dev_test
Connected.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> explain plan for select sysdate from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

8 rows selected.

SQL>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.