Link to home
Create AccountLog in
Avatar of nsdlsandy
nsdlsandy

asked on

Make all Oracle users in a ReadOnly role.


I have about 100 users with different roles. These are the roles in current oracle database

select role from dba_roles where role like 'XXX%' and role not like 'XXX%ROLE';

XXX_DESIGNER
XXX_ADMINISTRATOR
XXX_READONLY
XXX_SUPERVISOR

I want to mass update so all Users are 'XXX_READONLY'

How can I do that.
Avatar of anumoses
anumoses
Flag of United States of America image

There is a role called as OEM_MONITOR .This role will give the user the read
only priviledge to all the tables used for monitoring the database

sql>grant oem_monitor to user;
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

@anumoses,

How does that revoke all currently granted roles and only grant the read only role?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
comment ID: 34467754 is a good approach assuming that you are not ever going to give these users their old privs again.  If you will be putting all of these users back to the way they were and you don't need these roles for other things, you might do better to just revoke insert and update type privs from the 4 roles and put a script together to restore the grants to the roles later when these users go back to their former roles.

Just another approach if this is a temporary move.
Agreed but on a large schema revoking all insert/update/delete to multiple roles  could be a lot of work.
Good point.  I guess it all depends on how things are done and how many objects there are.
Avatar of nsdlsandy

ASKER

I will test it out but it is a good start.