grants on mutiple tables and to multiple users
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: grants on mutiple tables and to multiple users

Hybrid View

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    Hi,

    can you grant a privilege on multiple tables and to multple users. See below example:

    grant select on (TEMP1,temp2,temp3,temp4) to user1,user2,user3;


    I got an "invalid table name" error message.

    Please help!!


    Thanks,
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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."

  3. #3
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    Thanks, but the site does not have what I'm looking for. I know the correct format for an object but multiple objects
    leonard905
    leonard905@yahoo.com

  4. #4
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    I believe you can do multiple grantees, but not multiple objects unless you set up a role (and then grant the role).
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by leonard905
    Thanks, but the site does not have what I'm looking for.
    Funny, that site lists the "official" Oracle syntax, so if there is not what you are looking for then very probbably you are looking for nonexisting variant of the syntax. You just have to read those railroad diagrams correctly.

    As other have allready said, you can have multiple grantees, but not multiple objects in the single OBJECT GRANT command. It's all in the URL jJeff has provided.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by leonard905
    Thanks, but the site does not have what I'm looking for. I know the correct format for an object but multiple objects
    No, the answer is clearly there. You can't grant permissions on multiple objects at the same time.

    Code:
    SQL> create table t1 (x number);
    
    Table created.
    
    SQL> create table t2 (y number);
    
    Table created.
    
    SQL> grant select on t1, t2 to b,system;
    grant select on t1, t2 to b,system
                      *
    ERROR at line 1:
    ORA-00990: missing or invalid privilege
    
    
    SQL> grant select on t1 to b,system;
    
    Grant succeeded.
    
    SQL>
    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."

  7. #7
    Join Date
    Mar 2001
    Posts
    77
    1, Create a role

    e.g. Create role leonard905;

    2, Grant privilege to role

    e.g. GRANT create session, create database link TO leonard905;

    3, Grant role to users.

    e.g. GRANT leonard905 to Jack, Bob, Cathy;

    If you want to users also have the right to grant privileges in the role to other users, then

    GRANT leonard905 to Jack, Bob, Cathy WITH ADMIN OPTION;

    Hope this helps.

  8. #8
    Join Date
    Mar 2001
    Posts
    77
    Forgot, you can also grant privilege on object to user like so:

    SQL> CREATE user BOB IDENTIFIED BY bob;

    User created.

    SQL> GRANT update, delete ON emp TO bob;

    Grant succeeded.

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