grants on mutiple tables and to multiple users

[leonard905]

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,

[marist89]

wrong syntax, see http://technet.oracle.com/docs/produ...3g.htm#2062195 for details.

[leonard905]

Thanks, but the site does not have what I'm looking for. I know the correct format for an object but multiple objects

[jrpm]

I believe you can do multiple grantees, but not multiple objects unless you set up a role (and then grant the role).

[jmodic]

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.

[marist89]

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.

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>

[oraka6]

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.

[oraka6]

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.