Is there any easy way to give grant privilage ?

[serkanb]

I have a "USERS" table on Oracle 7 (for Sun Solaris). There is ~70 user in this table.
I have to give connect permission for these users.
I wrote a SQL command like this:
"select 'GRANT CREATE TABLE, CREATE SESSION, CREATE SEQUENCE, CREATE VIEW, CREATE
PROCEDURE TO' name from users"

and I got a script. But I couldn't run this script for all users. I can run only one 'GRANT' sql command. Is there any easy way to perform this task?

 

[karluk]

You are on the right track. A common technique is to generate sql from a db query and then execute the generated script. It sounds as if that's what you're doing, so you are probably making some minor mistake.

Your script generating statement should be something like

select 'GRANT CREATE TABLE, CREATE SESSION, CREATE SEQUENCE, CREATE VIEW, CREATE
PROCEDURE TO' || name || ';' from users;

The ";" at the end is a separator that should allow you to execute multiple sql statements from the same script file.

 

[serkanb]

Thanks "karluk"...
I've used your code I have a script a script now :
"
GRANT CREATE TABLE, CREATE SESSION, CREATE SEQUENCE, CREATE VIEW, CREATE
PROCEDURE TO ggurkas;
GRANT CREATE TABLE, CREATE SESSION, CREATE SEQUENCE, CREATE VIEW, CREATE
PROCEDURE TO eatalay; ...
...
"
But when I try to run this script I've got this error message:
"ERROR at line 2:
ORA-00911: invalid character"

Here is line 2:
"SQL> list 2
2* PROCEDURE TO ggurkas;"

What's the problem? Have you an idea?

 

[karluk]

Please describe how you are attempting to execute these grants. When I save your statements in a file and execute them with the @filename syntax everything seems ok. Of course I get errors because the userids don't exist on my system, but the grants themselves execute.

My guess is that you are copying the grants into the SQL*Plus buffer. That will indeed generate your error.

 

[stefanhei]

I think a good approach would be to collect the privileges in a role and then grant that role to the users.

CREATE ROLE user_role NOT IDENTIFIED;
-- then grant the privileges to the role
GRANT CREATE TABLE TO user_role;
GRANT CREATE SESSION TO user_role;
GRANT CREATE SEQUENCE TO user_role;
GRANT CREATE VIEW TO user_role;
GRANT CREATE PROCEDURE TO user_role;
-- then grant that role to all users like karluk wrote
-- spool to right file here and turn of formating...
select 'grant user_role to '||name||';' from users;
-- call your skript here..

If you later want to add a privileg to all the users just grant it to the role. Remeber that objectprivileges (select on tableX) work, but if they should be used in stored objects (like a user wants to create a view on the granted object) they have to be granted explicitely to the user, not the role.

Stefan

 

[serkanb]

karluk you are wrigth.
I got them into SQL plus buffer with "get" command.

"@scriptfile" command is working...

thanks...