Home » SQL & PL/SQL » SQL & PL/SQL » script to grant insert, update for all tables in a schema
script to grant insert, update for all tables in a schema [message #352437] Tue, 07 October 2008 11:31 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
I have a requirement, where I have to grant insert, update
privs for all tables in my schema to another user.

I was just wondering, instead of writing multiple
grant lines, is there a script for that?
Re: script to grant insert, update for all tables in a schema [message #352441 is a reply to message #352437] Tue, 07 October 2008 12:31 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
SQL from SQL.

Pretty easy to write a single SELECT command to generate the lines for you.
icon2.gif  Re: script to grant insert, update for all tables in a schema [message #353116 is a reply to message #352441] Sat, 11 October 2008 11:16 Go to previous messageGo to next message
brahma0115
Messages: 3
Registered: October 2008
Location: ca
Junior Member
grant select,update,delete,insert on <table name> to <user name>
Re: script to grant insert, update for all tables in a schema [message #353119 is a reply to message #353116] Sat, 11 October 2008 11:20 Go to previous messageGo to next message
brahma0115
Messages: 3
Registered: October 2008
Location: ca
Junior Member
grant all on <object> to <user>
Re: script to grant insert, update for all tables in a schema [message #353122 is a reply to message #353119] Sat, 11 October 2008 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ALL includes ALTER and you surely don't want your users alter your table, don't you?
NEVER uses ALL.

Regards
Michel
Re: script to grant insert, update for all tables in a schema [message #353123 is a reply to message #353122] Sat, 11 October 2008 11:29 Go to previous messageGo to next message
brahma0115
Messages: 3
Registered: October 2008
Location: ca
Junior Member
oh sorry Mr michel
Re: script to grant insert, update for all tables in a schema [message #353158 is a reply to message #352437] Sun, 12 October 2008 01:24 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
select 'grant select,update,delete,insert on '||table_name||' to username' from tabs;
Re: script to grant insert, update for all tables in a schema [message #353180 is a reply to message #353158] Sun, 12 October 2008 08:49 Go to previous messageGo to next message
hijack
Messages: 4
Registered: October 2008
Junior Member
sql>set feedback off;
sql>set heading off;
sql>spool d:\grant.sql
sql>select 'grant select,update,delete,insert on '||table_name||' to &username;' from tabs;
sql>spool off
sql>start d:\grant.sql
Re: script to grant insert, update for all tables in a schema [message #353182 is a reply to message #353180] Sun, 12 October 2008 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@hijack

Welcome to the forum, please read OraFAQ Forum Guide, for instance "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Don't post full solution only hint or clue or link that hekp to find the solution (this is in the forum guide).

Don't repeat others answers.

Regards
Michel
Re: script to grant insert, update for all tables in a schema [message #353339 is a reply to message #353182] Mon, 13 October 2008 06:57 Go to previous message
hijack
Messages: 4
Registered: October 2008
Junior Member
@Michel
so sorry!
Previous Topic: plsql procedures sending field as parameter
Next Topic: Oracle Query_Trigger
Goto Forum:
  


Current Time: Sun Dec 11 00:04:59 CST 2016

Total time taken to generate the page: 0.11268 seconds