Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Is there any easy way to give grant privilage ?

Status
Not open for further replies.

serkanb

Programmer
Jan 30, 2002
19
TR
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?

 
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.
 
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?
 
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.
 
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
 
karluk you are wrigth.
I got them into SQL plus buffer with "get" command.

"@scriptfile" command is working...

thanks...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top