Skip to Main Content
  • Questions
  • What type of role/grant that should be given to allow to grant permissions on other schemas

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Ajay.

Asked: July 18, 2017 - 12:01 pm UTC

Last updated: July 19, 2017 - 1:08 pm UTC

Version: 12.1.0

Viewed 1000+ times

You Asked

Hi,

I have user, with name of "grant_schema". This user should be able to run below command.

SQL> grant select, insert, update, delete on claims.table_name to CLAIMS_USER;

What type of role/grant that should be give to my "grant_schema" to be able to run above grant command. I Don't want to give DBA role to my user.


and Chris said...

If you want user A to grant permissions on user B's objects to user C, then give A permissions on B "with grant option":

select user from dual;

USER                          
------------------------------
CHRIS

create table t (
  x int 
);
grant create session to grant_user identified by grant_user;
grant create session to app_user identified by app_user;
grant select on t to grant_user;

conn grant_user/grant_user

grant select on chris.t to app_user ;

ORA-01031: insufficient privileges

conn chris/chris

grant select on chris.t to grant_user with grant option;

conn grant_user/grant_user

grant select on chris.t to app_user ;

Grant succeeded.

Rating

  (2 ratings)

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

Comments

Ajay, July 19, 2017 - 10:19 am UTC

Hi,

My grant_user is a temporary schema and which is not having any tables. So I want to connect my grant_user schema run the below command.

Please check and let me know how I will do that.

conn grant_user/grant_user.

grant select,insert,update,delete on claims.table_name to CLAIMS_USER with grant option;
Chris Saxon
July 19, 2017 - 10:26 am UTC

You need to grant permissions on claims.table_name to grant_user with grant option. Then you can connect to grant_user and give it privileges to others.

Ajay, July 19, 2017 - 12:05 pm UTC

No, I want to grant permissions on claims.table_name to CLAIMS_USER by connecting grant_user. I have tried it but it's not working. Please see below.

SQL> conn GRANT_USER/grant_user;
Connected.
SQL> show user;
USER is "GRANT_USER"
SQL> SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON CLAIMS.'||TABLE_NAME||' TO CLAIMS_USER;' FROM USER_TABLES;

no rows selected
Chris Saxon
July 19, 2017 - 1:08 pm UTC

I understand. But FIRST you MUST grant select etc. on claims tables to grant_user. Or give grant_user the "GRANT ANY OBJECT PRIVILEGE" privilege (which you really shouldn't do...)

Think about it.

If I can login as some low privilege user and grant/revoke access on objects in any schema to/from any other user that's a massive security hole. I could use my low privilege user to give a higher-priv user even more access, extract/modify sensitive data using this higher priv user (possibly via the app itself). Then revoke access. And no one would be any the wiser (particularly if you don't have any auditing in place...).

So to grant access to objects you must either:

- Own the objects yourself
- Be granted privileges on the objects with grant option
- Have the grant any priv (which is best avoided)