tags:

views:

218

answers:

2

I need to grant privileges to all users, I can do:

GRANT select on table TO user1;
GRANT select on table TO user2;
...

But there are many users. How can I grant this privilege to all users at once?

I tried:

GRANT select on table TO ALL;

But that doesn't work.

+4  A: 
grant select on table to public;

But be careful when you do that -- make sure it's what you really want to do.

Jim Hudson
Thanks, it's just what I needed to do in my school assignment but I couldn't figure it out. I had 99% of the assignemnt already finished and this was the last thing remaining :)
Richard Knop
+3  A: 

You should use roles. Grant permission to roles. grant roles to users.

Dani
Are roles in Oracle 'cumulative' or 'disjoint'? In DB2 they are cumulative - if you have been granted a role, you can exercise those permissions at all times. In Informix, roles are separate; you use the SET ROLE statement to set which role is currently active, and at any time you can only exercise the role-provided privileges with the currently set role. (You can always exercise any privileges granted direct to your user name, of course.)
Jonathan Leffler
a Role is like a user, but it hides behind it all the users that has being granted that role.It is a better practice to grant permission on a role-based setup rather to each user separately.I think that Oracle is more like DB2.
Dani
@Dani - thanks. An acid test is "is there a SET ROLE statement in Oracle". If not, it has to work like DB2; if there is one, it might be more like Informix.
Jonathan Leffler
A user can have multiple roles, and roles can be granted to other roles.Nice diagram here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/security.htm#i14426
David Aldridge
There is a SET ROLE command in Oracle, and you can enable any or all of your roles with a given execution of it. There are certain things that cannot be done via roles. For example, you cannot create a view on a table you've been granted SELECT privileges for via a ROLE.
DCookie
Thanks DCookie and David Aldridge.
Jonathan Leffler