Multiple Users
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Multiple Users

  1. #1
    Join Date
    Mar 2001
    Posts
    109
    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

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Mar 2001
    Posts
    109
    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

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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
  •  



Click Here to Expand Forum to Full Width