Re: Granting access on all tables problem

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 30 Aug 2011 07:42:38 -0700 (PDT)
Message-ID: <f822aec2-7cec-461b-bee2-d5d09fe0d93c_at_n11g2000yqh.googlegroups.com>

On Aug 29, 2:29�pm, t..._at_m.com wrote:
> On Mon, 29 Aug 2011 04:24:55 -0500, t..._at_m.com wrote:
> >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
>
> Thanks Michel and Robert for the help!
> It is working now.
>
> Dave- Hide quoted text -
>
> - Show quoted text -

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 -- Received on Tue Aug 30 2011 - 09:42:38 CDT