Problem with grant command [message #270767] |
Fri, 28 September 2007 00:06  |
axiom
Messages: 26 Registered: August 2007
|
Junior Member |
|
|
hi all
i have to schemas as fas & hofas.
i want to give grant all on tables of fas to hofas in a single query
i tried this as
grant select on '&"||select table_name from user_tables;||" to hofas
plz anyone tell me whats wrog with this query
|
|
|
|
|
|
|
|
|
|
|
Re: Problem with grant command [message #270826 is a reply to message #270775] |
Fri, 28 September 2007 01:54   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
anacedent wrote on Fri, 28 September 2007 07:19 | What make you think you can embed a SELECT statement right in the middle of a GRANT statement?
|
If you insist on doing it in a single command, you have to use dynamic sql.
Check the documentation on how to use that.
[Updated on: Fri, 28 September 2007 01:55] Report message to a moderator
|
|
|
Re: Problem with grant command [message #271017 is a reply to message #270767] |
Fri, 28 September 2007 13:59   |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Run the following script in sqlplus when you are logged into the fas user.
SET FEEDBACK OFF
SET LINESIZE 300
SET TRIMSPOOL ON
SET PAGESIZE 0
set echo off
SPOOL SET_GRANT.SQL
SELECT 'grant select on '||table_name||' to hofas;'
FROM USER_TABLES;
SPOOL OFF
@SET_GRANT.SQL
[Updated on: Fri, 28 September 2007 13:59] Report message to a moderator
|
|
|
Re: Problem with grant command [message #271069 is a reply to message #270767] |
Fri, 28 September 2007 23:36   |
muzahid
Messages: 281 Registered: September 2004 Location: Dhaka, Bangladesh
|
Senior Member |
|
|
This is another solution. You may try with this
declare
cursor c_table is
select table_name from user_tables;
r_table c_table%rowtype;
begin
for r_table in c_table
loop
execute immediate 'grant select on ' || r_table.table_name || ' to hofas';
end loop;
end;
/
|
|
|
Re: Problem with grant command [message #271081 is a reply to message #271069] |
Sat, 29 September 2007 01:31  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Don't you know what is indentation?
Do you write all your code like that?
By the way, never hear about bulk operation?
Regards
Michel
|
|
|