Hi,

I am having problems granting access to all tables to a role. I have
tried the following I found in the internet ....


begin
for i in (select table_name from all_tables) loop
execute immediate 'grant select on ' || i.table_name || ' to ' || XRL;
end loop;
end;
/

I created the user XRL and granted it CONNECT and RESOUCES but when I
run the dynamic sql I get the following error

error at line 3:
ORA-06650: line 3, column 64
PLS-00201: identifier 'XLR' must be declared
ORA-06550 line 3, column 1:
PL/SQL: Statement ignored.

I am using Oracle 11.1.0 on Windows 2008

Thanks,
Dave

<test@m.com> a écrit dans le message de news: gemm571pdd1fjgi3un09u4pl1t9dec7pbv (AT) 4ax (DOT) com...
XLR is not an identifier of your block.
It is static is your statement and so part of the SQL string.

Regards
Michel

On 29.08.2011 17:10, Michel Cadot wrote:
Also it's a good idea to quote object names

execute immediate 'grant select on "' || i.table_name || '" to XRL';

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Mon, 29 Aug 2011 04:24:55 -0500, test@m.com wrote:


Thanks Michel and Robert for the help!
It is working now.

Dave

On Aug 29, 2:29*pm, t...@m.com wrote:
Dave, I am surprised the code is working properly since I was
expecting you also needed to add the owner to the grant so that you
were producing grant select on owner.table_name to user and that you
might need to filter the select to exclude tables owned by usernames
SYS, SYSTEM, etc that your ID has been granted privilege on. It may
also be desirable to exclude issuing grants for objects alreadys
granted to PUBLIC.

HTH -- Mark D Powell --