Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, alvaro.

Asked: June 26, 2001 - 9:37 am UTC

Answered by: Tom Kyte - Last updated: August 03, 2009 - 5:41 pm UTC

Category: Database - Version: 7.3.4.0.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have a user that will be the responsible for granting and revoking application's roles to others users (this user will be the application's administrator) . This task will be done using a package, and I've granted "GRANT ANY ROLE" to this user for this purpose.

I want to know what kind of problems could I have for granting "GRANT ANY ROLE" to a user that is not DBA.

I Know that this user couldn't grant the role DBA to others users, but I'm not sure if there are dangerous exceptions about it.

In addition, this user only will be able to grant and revoke roles through the package.

thanks

Acueto

and we said...

If the GRANT statment is in a package the OWNER of the package needs the ability to grant the role, the user running the package needs no special privs.

The invoker of the procedure does not need this privilege, only the OWNER (definer) does.

and you rated our response

  (23 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

GRANT ANY ROLE

January 03, 2002 - 6:55 pm UTC

Reviewer: Joe from Denver, Co. - USA

I was hoping to understand if I granted GRANT ANY ROLE to a user, if that would create a security problem for me, the Production DBA. I'm responsible for system owned objects, not other schema owned objects in the database. I wouldn't want to give anyone other than sys or system the ability to grant roles such as DBA to themselves or anyone else. Any clarification would be appreciated.

Tom Kyte

Followup  

January 03, 2002 - 7:17 pm UTC

Don't grant "GRANT ANY ROLE", that will create a huge security problem.

I was suggesting to the person here, that asked the question, that they NEED NOT grant this super powerful priv. That all they needed to do was create a package that gave the grants as the owner of the objects -- grant execute on that package to this other user and they are done.

Don't grant GRANT ANY ROLE, that would be a really bad idea.

How Powerfull of GRANT ANY ROLE?

January 03, 2002 - 9:59 pm UTC

Reviewer: Mike from ATLANTA

Case, SYSTEM grant GRANT ANY ROLE to scott. SCOTT no able to grant DBA to self or any other user.

system@o817> grant grant any role to scott;

Grant succeeded.

scott@o817> select * from dba_users;
select * from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist


scott@o817> grant dba to scott;
grant dba to scott
*
ERROR at line 1:
ORA-01031: insufficient privileges


scott@o817> grant dba to SYSTEM;
grant dba to SYSTEM
*
ERROR at line 1:
ORA-01031: insufficient privileges


scott@o817>

Tom Kyte

Followup  

January 04, 2002 - 7:33 am UTC

Try any role other then DBA or RESOURCE. They fail because they implicity attempt to grant the privilege UNLIMITED TABLESPACE in addition to the ROLE (they are "magic", special). You'll find that scott can grant important stuff like JAVASYSPRIV to anyone (including himself). Give me the ability to create a java stored procedure and GRANT ANY ROLE and I'll wreak havoc on your database. Or, if you have DBA-like roles you've created (as you should), they can grant those.


Powerful? Yes, definitely.

Tom is right that GRANT ANY ROLE is Very Dangerous

January 04, 2002 - 12:27 am UTC

Reviewer: Chip from Denver CO

GRANT ANY ROLE does allow 2 roles that are granted to DBA
to be granted: EXP_FULL_DATABASE and IMP_FULL_DATABASE.
The IMP_FULL_DATABASE role has the ability to drop any
database object and create any database object. Also,
the privilege to EXECUTE ANY PROCEDURE opens up more
possibilities (as does BECOME USER).

Tom Kyte

Followup  

January 04, 2002 - 8:02 am UTC

Good point -- if you give me:

o create session
o execute any procedure

I can take over your database and get ANY priv I want. It would take about 30 seconds for me to have DBA and anything else I wanted.

You Think That Is Bad...

January 04, 2002 - 10:25 am UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

Here's a list of the *required* (according to the totally clued-in vendor - sarcasm) of the grants/privileges to be given to the 'SA' account. (Maybe this is a ported MS SQL Slower application?) Vendor's name withheld to protect the guilty. Anyway, here's the list straight off their support website:

=====================
PROBLEM SYMPTOM:
Oracle privileges required for sa account

Customers have requested a list of what privileges the SA account needs for an Oracle database.
=====================
DIAGNOSIS:
List of privileges required for sa account

The "sa" account is created as:
grant dba to sa identified by sa with admin option

Additional System Privileges

alter session
alter user
create cluster
create database link
create procedure
create profile
create public database link
create public synonym
create role
create session
create sequence
create synonym
create table
create trigger
create user
create view
drop profile
drop public database link
drop public synonym
drop user
grant any privilege (or "with admin option")
restricted session

sa must also have access to all of the "DBA_" views.


I don't see any problems there... (big sarcasm)

This vendor needs help. Plus you should see a StatsPack report of their sql executing...

Tom Kyte

Followup  

January 04, 2002 - 7:43 pm UTC

alter user -- hmm, with that I rule the WORLD ;) I can become SYS or anyone I want to....

Curious to know

January 04, 2002 - 10:30 pm UTC

Reviewer: Reader from USA

Tom,

Curious to know, with just "execute any procedure"
How will you get "DBA" priv in about 30
seconds.

Thanks

Tom Kyte

Followup  

January 05, 2002 - 10:53 am UTC

I'd rather not say. The knowledge that it is so should be more then enough hopefully to make people go out there and reconsider how they grant privs and what privs they grant.

Think about it -- you can run ANY procedure in the database, any SYS owned procedures, whatever you want...

RE: I'd rather not say...

January 05, 2002 - 11:07 am UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

Tom:

Thanks for not publishing how to do this... As you know from my previous posting, one of my PROD environments is already way more open than I would prefer. Now, if it was just in a DEV environment, I am much more open...

One of the last things we need is for some script kiddie to read about execute any procedure and start trying to hack around a database.

- Mark

Tom Kyte

Followup  

January 05, 2002 - 11:11 am UTC

Yes, thats they way I feel as well. The knowledge that the ability exists is more then enough knowledge. It's like the guys who find the holes and not only say "hey, there is a hole over there" but then proceed to publish "and here is how to drive the truck right through it" -- they are totally misguided. They believe (or at least CLAIM to believe) they are just being helpful -- all they need to do is publicize that there is a hole, not how to exploit it.

This really isn't a hole, it is a natural side effect of having this very POWERFUL privilege.

RE: RE: I'd rather not say...

January 05, 2002 - 3:11 pm UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

Tom:

100% with you - what a "service" it is to find "exploits" and then broadcast them everywhere.

I don't want to give too much away (because I do know how to do this), but one thing I did want to mention is that setting o7_dictionary_accessibility=false will "close the hole" (even though it really isn't a hole as you point out).

- Mark

Tom Kyte

Followup  

January 05, 2002 - 8:25 pm UTC

Correct -- and hopefully people will live with the 7_dictionary_accessibility=false setting INSTEAD of setting it true (as I see people do)...

Now, give me create session, execute any procedure, xxxxx xxx xxxxxxxxx -- and I'm back in business ;)







RE: RE: RE: I'd rather not say...

January 05, 2002 - 9:27 pm UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

*IF* I was going to give some knowledgeable individual create session, execute any procedure, and xxxxx xxx xxxxxxxxx (left 'blank' on purpose), I might as well just give them DBA... You seem pretty trustworthy, though :) I run my PROD db's with o7_... set to false as a rule (glad to see that is the default in 9i). I regularly check dba_role_privs just to be safe (paranoid?) all the same.

- Mark

October 22, 2002 - 5:56 am UTC

Reviewer: A reader

Hi Tom,

I tried to connect to my instance. But I could not log as even SYS user.

SQL> connect sys/change_on_install as sysdba
ERROR:
ORA-01031: insufficient privileges

I tried with the SYSTEM and INTERNAL user accounts also. But still the same error. 

Then I recreate the password file. but still no help. Could you please tell me what's wrong, that I had done. 

Tom Kyte

Followup  

October 22, 2002 - 7:34 am UTC

without a version
without an os
without knowing who you are logged in as
without knowing how you created the password file
without knowing your setting of the remote_login_passwordfile init.ora setting

nope.


make sure the password you are using for SYS matches the one that you used in creating the password file (thats the password it is looking for)

make sure the password file is named as it should be for your operating system (refer to your OS admin guide)

make sure the remote_login_passwordfile is set



Another parameter to check...

October 22, 2002 - 10:53 am UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

"A reader"... I tried to connect...

In addition to the parameters from Tom, make sure SQLNET.AUTHENTICATION_SERVICES is set correctly if you are using Windows...

HTH,

Mark

Follow-up to myself...

October 22, 2002 - 10:57 am UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

The SQLNET.AUTHENTICATION_SERVICES won't directly affect this situation, but just wanted to throw it out there in case it needed to be looked at too...

Sorry... if that was misleading...

- Mark

Security risk?

January 31, 2003 - 6:29 am UTC

Reviewer: Martin from The Netherlands

Tom,

I noticed in one of our databases that for all (packaged)procedures execute is granted to PUBLIC. Reading about the danger of users having the EXECUTE ANY PROCEDURE privilege I was wondering if this does not mean the same dangerous situation? Should I remove these grants? And if I do won't that mean custom packages that for instance call DBMS_SQL will suddenly fail?

Tom Kyte

Followup  

January 31, 2003 - 8:31 am UTC

EXECUTE ANY is different then having EXECUTE on specific packages.


I cannot tell if you are talking about the supplied DBMS_* packages -- which are MOSTLY granted to public (but some like dbms_sys_sql, dbms_lock, are not)

or your own packages.


If they are you OWN packages -- do what you want as far as securing them.

If they are the DBMS_* packages, just verify that the ones we need to have granted to public are (dbms_output, dbms_sql, etc) and the ones we do not grant by default are not. You can grep the original install scripts in $ORACLE_HOME/rdbms/admin to see which are to be granted to public



$ cd $ORACLE_HOME/rdbms/admin
$ grep -i 'grant.*execute.*on.*to.*public' *


Sorry I didn't state my question very clear

January 31, 2003 - 9:09 am UTC

Reviewer: Martin from The Netherlands

Tom,

What I meant to ask was if ALL packages/procedures/functions in the SYS schema are granted execute to Public, wouldn't that create a similar problem as granting the EXECUTE ANY PROCEDURE to a user or even public. From reading your comment on my original queston I learn that it should be save when I remove those grants which oracle does not grant on installation by default, right?



Tom Kyte

Followup  

January 31, 2003 - 10:37 am UTC

If things like dbms_sys_sql, which are not granted by our stuff in $ORACLE_HOME/rdbms/admin, are granted to public -- you have a problem and need to correct it -- yes.

February 12, 2003 - 1:32 pm UTC

Reviewer: A reader

If O7_DICTIONARY_ACCESS is set to FALSE, then even if we grant EXECUTE ANY PROCEDURE privilege, they won't be able to execute the DBMS_ procedures. Unfotunately, until 9i, the default value for that parameter was TRUE.


Tom Kyte

Followup  

February 12, 2003 - 3:43 pm UTC

as mentioned above -- yes, that helps much

November 21, 2003 - 2:39 am UTC

Reviewer: reader from India

Hi Tom,
while reading Oracle8i,Administrator’s Guide,Release 2 (8.1.6),Part No. A76956-01 I got this point "Privileges and roles can also be granted to and revoked from the user group PUBLIC. Because PUBLIC is accessible to every database user, all privileges and roles granted to PUBLIC are accessible to every database user." I want to know what exactly is user group and PUBLIC, apart from public are there any other user groups and what's the advantage of having user groups, also how does granting of privileges and roles to a user group can be made accessible to all.

Thanks in advance for your help.


Tom Kyte

Followup  

November 21, 2003 - 4:48 pm UTC

the "user group public" is simply a set of privs that EVERYONE has.

if you "grant create select any table to public", everyone - everyone that can log in has "select any table" (so -- do NOT do that!)



Become a DBA

November 21, 2003 - 6:13 pm UTC

Reviewer: A reader

Hi Tom,

The 'O7_dictonary_accessibility' parametere must be TRUE for a normal user who is assigned 'Execute Any Procedure' privilege to get DBA privileges.

Is this correct?

Tom Kyte

Followup  

November 21, 2003 - 10:37 pm UTC

to get DBA, you must be granted DBA-- that is all.

GRANT ANY ROLE and REVOKE DBA FROM USER

February 04, 2005 - 9:02 am UTC

Reviewer: Alexey Yatsenko from Russia

Oracle8i SQL Reference says:
"You can revoke any role if you have the GRANT ANY ROLE system privilege."

Let's create a user U who will try to revoke some roles from user A:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
JServer Release 8.1.7.4.1 - Production

SQL> connect sys
Enter password:
Connected.

SQL> create user a identified by a;

User created.

SQL> grant dba, exp_full_database to a;

Grant succeeded.

SQL> create user u identified by u;

User created.

SQL> grant create session, grant any role to u;

Grant succeeded.

SQL> connect u/u
Connected.

SQL> revoke exp_full_database from a;

Revoke succeeded.

SQL> revoke dba from a;
revoke dba from a
*
ERROR at line 1:
ORA-01031: insufficient privileges

Why can Grant-Any-Role-User revoke EXP_FULL_DATABASE role but cannot revoke DBA role? Are there logical reasons for that? 

Tom Kyte

Followup  

February 04, 2005 - 11:57 am UTC

guess dba is magic, should be filed as a doc bug mentioning the exception.

(dba is magic, dba conveys other thing beyond the role, like unlimited tablespace for example. dba's are not affected by failed logon triggers and other things)

Application needs to create users - how to secure this

September 26, 2005 - 8:11 am UTC

Reviewer: Tom

Hi Tom,

I have a mod_plsql application using database authentication [ie application users are database users] and am using a role to grant priviledges on the relevant packages [not all packages web facing].

What is the most secure way to allow my application to create new users and grant them the role. My current thinking is...

1. Application owner [say APP] gets direct grants of "create user","alter user" and "grant any role".

2. Package created which provides a "createUser" procedure

3. Web packages call this procedure.

4. Application owner account is locked [no-one can log in]

Is there a more secure way of doing this?



Tom Kyte

Followup  

September 26, 2005 - 9:33 am UTC

1) grant any role? why not just grant the roles to this account with the grant option?

are all users in this database solely for this application? not sure I'd want this user to be able to alter SYS or SYSTEM's password for example, might want another layer - a different trusted account that does this.




What should be the ideal set of privileges for a developer

January 03, 2006 - 4:12 pm UTC

Reviewer: Biju C from New york, NY

Tom

What should be the ideal set of privileges for a developer.

Once we moved in to 9.2, since the O7_DICTIONARY_ACCESSIBILITY is set to FALSE is the default, Should the DBA Grant SELECT_CATALOG_ROLE TO the developer, to view what is happening with his session. (Or table space, or whatever a developer needs)

We do have a tied up environment where all the Production code and structure changes are applied by the DBA. Is it a security concern if the SELECT_CATALOG_ROLE is granted to the developer in this environment.

I know it is difficult to generalize these things, but I am looking for some guidelines/industry standards here.

Thanks in advance for your response and this service

Tom Kyte

Followup  

January 03, 2006 - 6:07 pm UTC

question: What should be the ideal set of privileges for a developer.
answer: as few as possible, as many as necessary to get the job done.

The schema holding objects (code, tables, etc) should have as little as possible, the develop - likewise, but it would seem that select catalog and access to v$ views for developers is "necessary" in development (as least I couldn't live without it)


can not retrieve data from view

February 28, 2008 - 3:50 pm UTC

Reviewer: Dawar from Los Angeles, CA USA

I have Oracle Database 10g running on Linux box.

We have main user for this schema, l
ets say XYZ.

We created more users on request.

But they can not retrieve data from view (abc).
Users can access to the base table on which view has been created. But they are not able to get values from abc_view but special user XYZ is fine.
User XYZ can retrieve data from view (abc).

TABLES

SQL> select count(*) from a_table;

COUNT(*)
----------
25412

SQL> select count(*) from b_table;

COUNT(*)
----------
32575

SQL> select count(*) from c_table;

COUNT(*)
----------
322

*****************************************
VIEW:

SQL> select count(*) from abc_view;

COUNT(*)
----------
0 


Dawar 
Los Angeles, CA USA

Tom Kyte

Followup  

February 28, 2008 - 11:26 pm UTC

so? why do you think this is not normal.

views are just like tables - in fact, just think of a view as a table.


and tables (views) have grants.

You can only select from a table (view) if you have been granted the ability to select from that table(view)

sounds like XYZ was granted this ability on the view
and the rest were not.

can not retrieve data from view

March 28, 2008 - 11:12 am UTC

Reviewer: Dawar from Los Angeles, CA USA

Tom,

Part 2 of above question.

Actually our views following condition as below:

select 
...
....
from
dept_users c
emp a
where c.user_name = user
... some other conditions


Now dept_user tables has all user name along with our internal department number.
I need to enter all users in this table along with dept no.
So they can be able to work with departments associat with users.

Last time I commented out below line from where condition

---where c.user_name = user

So every user list in the dept_users table can get values.
But after some time I noticed we are getting repeation values in out put.

SQL> select count(*) from 123_view;

  COUNT(*)
----------
   455685

But if removed commented out and used condition as below in the where clause

where c.user_name = user


SQL> select count(*) from 123_view;

  COUNT(*)
----------
19847


**********************
But again only few users can get values now.
I created new users but they are not getting any values from the views of this particular schema.

Dawar
LA, CA
USA


Tom Kyte

Followup  

March 31, 2008 - 7:37 am UTC

sorry, this makes little sense - there is totally insufficient data here to comment on anything. I'm totally guessing here.

...
Last time I commented out below line from where condition

---where c.user_name = user

So every user list in the dept_users table can get values.
But after some time I noticed we are getting repeation values in out put.

......

I would anticipate that the removal of a where clause like that, without adding MORE to the where clause would do that, think about it.

You are joining two tables together.

You USED to do it for a SINGLE USER.

Now you are doing it for EVERY USER.

If two users had the same name for something (totally do not know your schema, so I'm using "name" rather generically here - whatever columns you join by) - then they would get "joined together" - you would get what you are calling duplicates (but they are NOT duplicates - they are the result of an incorrectly specified JOIN)

You probably need to add something like "and table1.username = table2.username"


Think about it, if you join USER_TABLES to USER_TAB_COLUMNS by table_name - all is well (it has a filter "where owner = USER")

If you stop using USER_TABLES and start using ALL_TABLES and ALL_TAB_COLUMNS (you remove the "where owner = user") and still only join by table_name, and NOT table_name, owner - you will obviously get "trouble" since my "EMP" table will join to MY EMP TABLE and YOUR EMP TABLE and HIS EMP TABLE and HER EMP TABLE and so on...

MDSYS privileges

July 30, 2009 - 4:46 pm UTC

Reviewer: Adrian from Buenos Aires, Argentina

Hi Tom,

I have one question related to MDSYS account privileges.

This account is for Oracle Spatial, and it has several sys privileges critical like CREATE ANY LIBRARY, ALTER ANY TRIGGER, ALTER ANY PROCEDURE, EXECUTE ANY PROCEDURE, GRANT ANY ROLE, GRANT ANY PRIVILEGES and BECOME USER.

Even this account is locked and its password expired, do you consider it's necessary to revoke these privileges from it ? or could I revoke only the CREATE SESSION priv and CONNECT role from MDSYS ?

Thank you in advance.

Adrian
Tom Kyte

Followup  

August 03, 2009 - 5:41 pm UTC

if you do not use spatial, you can uninstall it.

but revoking the base privileges from the account would not be recommended - as it will break the underlying code - it's procedures expects to have those privileges.

August 04, 2009 - 11:26 am UTC

Reviewer: Adrian from Buenos Aires, Argentina

Thank you Tom for your clarification.

Best regards

Adrian