-1

Accidently i have created the 2 rows for each DBA and AQ_ADMINISTRATOR_ROLE in table user_roles_privs, can you let me know how i can delete 1 row of each and also the query to delete.

This is the current state of the table:

database table screenshot

Thanks Nilesh

kidroca
  • 3,480
  • 2
  • 27
  • 44
Nilesh
  • 1
  • 1
    Does this answer your question? [Removing duplicate rows from table in Oracle](https://stackoverflow.com/questions/529098/removing-duplicate-rows-from-table-in-oracle) – Travis Jan 15 '21 at 22:26

1 Answers1

2

You are getting these records as you/someone must have given these roles to your user - C##234. You need to revoke the role from your user as follows:

REVOKE AQ_ADMINISTRATOR_ROLE FROM C##234; -- You can execute same for DBA

Please make sure that you are executing the command from user with DBA rights.

Read more about REVOKE from Oracle documentation here

Popeye
  • 35,427
  • 4
  • 10
  • 31
  • Thanks, it worked but the AQ_ADMINISTRATOR_ROLE with the admin option as yes has been deleted and when i am again running the grant command it is making the new row . Basically i want the Admin option as yes and Common as yes for both DBA and AQ_ADMINISTRATOR_ROLE. Thanks – Nilesh Jan 15 '21 at 14:59
  • If you want admin option as YES then you need to grant the role with admin option. – Popeye Jan 15 '21 at 15:04
  • yes i have done that but it is creating a new row and not letting me change in the existing row , if the new row will be created than common will become no, but i want both to be yes. – Nilesh Jan 15 '21 at 15:07
  • This is how oracle has implemented its internal code and it is also mentioned in the given oracle document. – Popeye Jan 15 '21 at 16:17