Re: Granting access on all tables problem

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 29 Aug 2011 18:16:04 +0200
Message-ID: <9c1saeFtk4U1_at_mid.individual.net>

On 29.08.2011 17:10, Michel Cadot wrote:
> <test_at_m.com> a écrit dans le message de news: gemm571pdd1fjgi3un09u4pl1t9dec7pbv_at_4ax.com...
> | 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
>
> XLR is not an identifier of your block.
> It is static is your statement and so part of the SQL string.

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/

Received on Mon Aug 29 2011 - 11:16:04 CDT