0

I have a problem with Revoke command. I created the role and grant to role "SELECTE on table" privilege. Then I grant role to user. Then I revoke role from user. But the user still have "SELECT on table" privilege which he received via role. What I do wrong?

HR user: 

create table testtable (id number);--Table TESTTABLE created 
create role trole;--Role TROLE created 
grant select on testtable to trole;--Grant succeeded 
grant trole to test_user;--Grant succeeded

test_user: 

set role trole;--Role TROLE succeeded. 
select * from hr.testtable;--working

HR user: 

revoke trole from test_user;--Revoke succeeded.

Test_user: 

select * from hr.testtable;--working again despite that the fact the role is revoked.

note: there are no any other grant to test_user

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Tgy
  • 5
  • 6
  • Most likely the user was granted the privilege separately from the role. Please post the actual code you used. – pmdba Apr 25 '20 at 11:25
  • HR user: create table testtable (id number);--Table TESTTABLE created create role trole;--Role TROLE created grant select on testtable to trole;--Grant succeeded grant trole to test_user;--Grant succeeded test_user: set role trole;--Role TROLE succeeded. select * from hr.testtable;--working HR user: revoke trole from test_user;--Revoke succeeded. Test_user: select * from hr.testtable;-working again despite that the fact the role is revoked. note: there are no any other grant to test_user – Tgy Apr 25 '20 at 12:18
  • Post was edited with new details – Tgy Apr 25 '20 at 12:24
  • What other roles the test_user has? from the scripts, you create the role and grant it to test_user from HR user. Could it happen that both these users has some sort of DBA privileges? – micklesh Apr 25 '20 at 12:29
  • 1
    There is one role that all users have and that **cannot be revoked**. That is the `PUBLIC` role. And, since `HR` is often used for testing, in many databases `PUBLIC` is granted privileges to `HR` tables. Oracle made the (idiotic in my opinion) decision not to show the `PUBLIC` role when you query to see "all" the roles granted to users. (If that is a **completely new** table, then perhaps `PUBLIC` has `SELECT` **`ANY`** `TABLE` - not a good thing, but possible.) –  Apr 25 '20 at 12:44
  • test_user haven' any other role because this user created today by me for testing purpose. @mathguy Maybe you are right about public role. How I can check it? Is there any other way to understanding what is the problem. And if think abstractly will "revoke" command revoke the privilege from user which he recived via role? I need this information for understanding how revoke command work related to roles. Thanks. – Tgy Apr 25 '20 at 15:37

1 Answers1

0

Revoke will remove the role and any privileges that are part of that role from the user. In your case it sounds like test_user has some additional privilege like SELECT ANY TABLE granted directly or inherited through the PUBLIC role. Check this post to find all granted privileges: How to show all privileges from a user in oracle?

pmdba
  • 6,457
  • 2
  • 6
  • 16
  • Hi. I checked the roles and other privileges list, but no any other privileges on this user. I tested give grant to user from other user (not HR), but result is same. I can't understand what wrong there is. – Tgy Apr 25 '20 at 19:19
  • Did you confirm whether or not PUBLIC has access to the HR schema and your table, or the SELECT ANY TABLE privilege? – pmdba Apr 25 '20 at 21:38
  • I do not know how i can check public access to HR scheme, but I use another scheme for testing (not HR) and result is same. – Tgy Apr 25 '20 at 23:29
  • This suggests that PUBLIC has too many privileges. Use the queries in the above link with a user 'PUBLIC' to check. – pmdba Apr 25 '20 at 23:56