Home » SQL & PL/SQL » SQL & PL/SQL » Problem with grant command
Problem with grant command [message #270767] Fri, 28 September 2007 00:06 Go to next message
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 #270771 is a reply to message #270767] Fri, 28 September 2007 00:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>plz anyone tell me whats wrog with this query
It does not produce the desired results.
Re: Problem with grant command [message #270773 is a reply to message #270771] Fri, 28 September 2007 00:14 Go to previous messageGo to next message
axiom
Messages: 26
Registered: August 2007
Junior Member
Query gives error as
quoted string not properly terminated
Re: Problem with grant command [message #270775 is a reply to message #270767] Fri, 28 September 2007 00:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Query gives error as
ERRORS? What errors? I don't see any errors.
Use CUT & PASTE to post complete session.

What make you think you can embed a SELECT statement right in the middle of a GRANT statement?
Re: Problem with grant command [message #270783 is a reply to message #270773] Fri, 28 September 2007 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Count the quote.
What is this "&" for?

Regards
Michel
Re: Problem with grant command [message #270799 is a reply to message #270783] Fri, 28 September 2007 01:14 Go to previous messageGo to next message
axiom
Messages: 26
Registered: August 2007
Junior Member
i tried a lot but still i m not getting
give me the correct query
it is very urgent

[Updated on: Mon, 01 October 2007 07:53] by Moderator

Report message to a moderator

Re: Problem with grant command [message #270801 is a reply to message #270767] Fri, 28 September 2007 01:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>it is very urgent
Please explain why it is URGENT for me to solve this problem for YOU?
>plz give me the correct query
why does any OWE you the correct query?

[Updated on: Fri, 28 September 2007 01:17] by Moderator

Report message to a moderator

Re: Problem with grant command [message #270804 is a reply to message #270799] Fri, 28 September 2007 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
i tried a lot but still i m not getting

You didn't try so much, just a glance to the query and I see there is an odd number of quotes (even if the query is not formatted).

In addition, you didn't answer to my question:
Quote:
What is this "&" for?

Regards
Michel
Re: Problem with grant command [message #270815 is a reply to message #270804] Fri, 28 September 2007 01:32 Go to previous messageGo to next message
axiom
Messages: 26
Registered: August 2007
Junior Member
grant select on "||select table_name from user_tables;||" to hofas
*
ERROR at line 1:
ORA-00903: invalid table name
Re: Problem with grant command [message #270826 is a reply to message #270775] Fri, 28 September 2007 01:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: package
Next Topic: pro and con of custom datatypes
Goto Forum:
  


Current Time: Mon Feb 17 15:00:44 CST 2025