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;
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
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)