Multiple Users

[mzhu]

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.

[sreddy]

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.

[url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76965/c26privs.htm#1125[/url]

[marist89]

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]

[mzhu]

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.

[sreddy]

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

And

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.