Re: Synonyms and DBMS_SQL?

It turns out that:

For a Stored Procedure or a Stored Function, the owner of the stored object must have been granted the necessary target object priveleges directly (ie, not through a role) OR the owner must have been granted the necessary Delete/Insert/Update/.. Any system privelege directly (ie, not through a role).

If the same PL/SQL code is run as an anonymous PL/SQL block, then the above restriction does not apply and the PL/SQL will run with the appropriate priveleges from a role.

I am not sure what the logic is behind this and what security weakness would be introduced by having a Stored Procedure/Function work off priveleges granted through a role. Does anyone understand this?

Jerry Apfelbaum wrote:
>
> I've done some detective work and found that the difficulty is not the
> use of synonyms but the fact that the object priveleges on the
> underlying tables were granted through a role. It appears that one
> needs to have been granted object priveleges directly or have been
> granted the SELECT ANY TABLE (or DELETE, etc.) system privelege.
>
> Still a reasonably big inconvenience.
>
> Jerry Apfelbaum wrote:
> >
> > Has anyone else experienced any problems with running SQL DML via the
> > PL/SQL package DBMS_SQL when the tables are referenced via synonyms?
> >
> > I have some queries which fail in DBMS_SQL.PARSE (with Table or view
> > does not exist error) but which work just fine in SQL*Plus. The
> > referenced tables are mapped via synonyms to TableName =
> > Schema.TableName. The queries just use TableName. DBMS_SQL works fine
> > for tables which I own.
> >
> > This would be a big inconvenience if DBMS_SQL won't work with synonyms.
 

-- 
=================================================
Jerry Apfelbaum           email: japfelba_at_ican.ca
Eastern Sun Group Inc.    phone:     416.240.9695 
Toronto, Canada