Toad World® Forums

Role Deletion


#1

Hello All,

I am using SQL Navigator 5.5 Standard Edt.

Recently, i Granted RESOURCE role to one of our users. After granting that role, i decided to revoke it from the user and just give the user the CONNECT role.

This is how I went about revoking the role from the user:

  1. Right click on the User name under USERS (DB Navigator view - F12).
  2. Select Open
  3. Click on the Roles Tab
  4. Under Granted Roles Heading, unchecked the RESOURCE Role.

Guess what happened, SQL Navigator actually removed the RESOURCE role itself.
I wondering why would it happen like that.

I just wanted to revoke the Resource role from that particular user ONLY.

Can someone shed some light on this?

Thanks a lot,
Murali Bala


#2

HI Murali,

Welcome to the SQL navigator community.

As you mentioned in your post >>>

  1. Under Granted Roles Heading, unchecked the RESOURCE Role.

This is not revoking the role granted to the user.
It is executing >>>

Alter user username default role all except resource;

So the user will still have the role resource under his name in dictionary table having ‘NO’ for DEFAULT_ROLE column in DBA_ROLE_PRIVS dictionary table. In future if you want to enable the role you can do so by checking in the UI without selecting the specified role on Left hand side and clicking and moving to the right. SO In case if you check in future it will execute >>> Alter user username default role all; statement in the background.

Either you revoke or disable Oracle database point of view no difference.

regards
sekhar


#3

Thanks a lot for your reply shekhar. I still can’t figure out why SQL Navigator would delete the RESOURCE ROLE from the Database.

So by saying "SQL Navigator actually removed the RESOURCE role itself. " I meant it removed the role from the DATABASE.

Hope i making myself clear.

Thanks again,
Murali Bala


#4

select * from dba_roles;

We use the below query to display the Available list for a particular user >>

select role “Available Roles” from dba_roles

MINUS

select GRANTED_ROLE from DBA_ROLE_PRIVS where GRANTEE = ‘AKOT’;

Note : Please observe the attached snapshots.

Hi Murali,

SQL Navigator is not at all Deleting a role from database.Let me explain more clearly.

  1. You have edited a user and you have un checked the “RESOURCE” role and saved it to database.So at this step SQL Navigator has disabled the Role to the particular user.

  2. So the left hand Available Roles list is not displaying the “RESOURCE” role for that particular user Because the user is already granted with RESOURCE role and disabled from the user but not revoked totally from user.So we are not displaying on the left hand side Available list for the particular user.

  3. But if you open some other user who is not granted with RESOURCE role then you can find in the Available list in our user interface.It is just the way we display in our user interface. We never remove or delete any role from database if you revoke or disable from a user.

  4. If you want to revoke totally , highlight the Role on Right hand side in Granted Roles List and press button “<<” .So it will revoke the particular role from the user and you will see that in the Available Roles list on the left hand side in our GUI.

  5. In our display we are just eliminating using queries if the role is already granted to a user.The available list is for that particular user only not at database level.

  6. If you go to the DB navigator tree and Expand the Roles node as DBA user then you can see all the available roles in the database.

Please check using the following queries.

**Granted Roles are displayed using the query >>>

select** * from dba_role_privs where grantee =‘AKOT’ order by GRANTED_ROLE;
Role1.jpeg


#5

select * from dba_roles;

We use the below query to display the Available list for a particular user >>

select role “Available Roles” from dba_roles

MINUS

select GRANTED_ROLE from DBA_ROLE_PRIVS where GRANTEE = ‘AKOT’;

Note : Please observe the attached snapshots.

Hi Murali,

SQL Navigator is not at all Deleting a role from database.Let me explain more clearly.

  1. You have edited a user and you have un checked the “RESOURCE” role and saved it to database.So at this step SQL Navigator has disabled the Role to the particular user.

  2. So the left hand Available Roles list is not displaying the “RESOURCE” role for that particular user Because the user is already granted with RESOURCE role and disabled from the user but not revoked totally from user.So we are not displaying on the left hand side Available list for the particular user.

  3. But if you open some other user who is not granted with RESOURCE role then you can find in the Available list in our user interface.It is just the way we display in our user interface. We never remove or delete any role from database if you revoke or disable from a user.

  4. If you want to revoke totally , highlight the Role on Right hand side in Granted Roles List and press button “<<” .So it will revoke the particular role from the user and you will see that in the Available Roles list on the left hand side in our GUI.

  5. In our display we are just eliminating using queries if the role is already granted to a user.The available list is for that particular user only not at database level.

  6. If you go to the DB navigator tree and Expand the Roles node as DBA user then you can see all the available roles in the database.

Please check using the following queries.

**Granted Roles are displayed using the query >>>

select** * from dba_role_privs where grantee =‘AKOT’ order by GRANTED_ROLE;
Roles_left_after_granting_to_user.jpeg