-
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.
zm
-
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]
Reddy,Sam
-
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]
Jeff Hunter
-
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.
zm
-
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.
Reddy,Sam
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|