I need to create multiple users for one schema. All users should have same privileges. It seems that except the schema's owner user I have to grant the object privilegs on all objects to other users one by one. For example, I have to grant the privileges on each table to other users. If I have more than a thousand tables, I have to grant the privileges on tables to other users more than a thousand times.
I was wondering if there is any easy way to do that.
There is ROLE which is nothing but a user which holds all the privileges catrgorized based on the functionality of the system.
Say Clerk doesn't need to update anything, but to see. Supervisor needs to update.
Create 2 roles Clerk and Supervisor.
Grant select on xyz to clerk
grant update on xyz to supervisor.
These roles can be granted to any of the users who work as Clerks/Supervisors
In your context, Grant 1000 tables privileges to XYZ Role and Grant that XYZ role to all the Users of the system. Read the docs on Roles.
You may also want to look into using a shared schema. I have read about it at [url]http://technet.oracle.com/deploy/security/aso/pdf/eu2.pdf[/url]
Thanks for the replies.
So I do need to grant the privileges on the table to a role 1000 times, right? I thought there should be a easier way.
Nope Not 1000 times. You grant (say read privileges) as follows. Say your Role is XYZ,
Grant SELECT on table_1 to XYZ
Grant SELECT on table_2 to XYZ
Grant SELECT on table_3 to XYZ
Grant SELECT on table_n to XYZ
Grant XYZ to Bob/Mary/scott/whoever your users are
This way you don't need to give Grant SELECT ON table_1 to table_n to every user Bob/Mary/scott.
With no role for 1000 tables you need to grant for each user 1000 times and for these 3 users 3000 Grant statements.
Now you do with only 1000+3 grant statements.
Click Here to Expand Forum to Full Width