Re: Trigger call to a package in another schema generates an error

Ryan Gaffuri wrote:

> DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E4C7051.DC8C09F6_at_exesolutions.com>...
> > Marco wrote:
> >
> > > Hey guys . . .
> > >
> > > Has anyone ever called a package from a trigger that is owned by another
> > > schema in Oracle 8.1.7?
> > >
> > > NOTE: I can execute this procedure in SQL*Plus as Schema1 with no problem.
> > > The problem only exists when I try
> > > to call this procedure inside Schema1's trigger!
> > >
> > > When I attempt to compile the trigger, I get the following error:
> > >
> > > TRIG1
> > > PLS-00201: identifier 'SCHEMA2.PKG1.PROC1' must be declared
> > >
> > > I have two schemas in an instance and they are laid out in the following
> > > way:
> > >
> > > Schema1: resource permission
> > > execute any procedure
> > > grants for Table2 in Schema2 (insert, update, delete, select)
> > >
> > > TABLE1
> > > which has a trigger:
> > >
> > > CREATE OR REPLACE TRIGGER TRIG1
> > > AFTER INSERT ON TABLE1
> > > FOR EACH ROW
> > > BEGIN
> > > SCHEMA2.PKG1.PROC1(:new.col1);
> > > END;
> > >
> > > Schema2: dba permission
> > > Table2
> > >
> > > PKG1 which has PROC1 procedure
> > > PROC1 inserts into TABLE2
> > >
> > > CREATE OR REPLACE PACKAGE PKG1 IS
> > > PROCEDURE PROC1(parm1 in varchar2);
> > > END:
> > >
> > > CREATE OR REPLACE PACKAGE BODY PKG1 IS
> > > PROCEDURE PROC1(parm1 in varchar2) IS
> > > BEGIN
> > > INSERT INTO TABLE2 VALUES (PARM1,'C',SYSDATE);
> > > END;
> > > END;
> > >
> > > - Public synonym for PKG1
> > >
> > > NOTE: This is oversimplified code used only to post this message, but the
> > > underlying logic is the same.
> > > Any ideas would be appreciated.
> > >
> > > Thanks,
> > >
> > > Ann
> >
> > Privileges granted through roles are not valid for functions, procedures, or
> > packages. The grants must be explicit. And as Sybrand has said ... we've
> > covered this thousands of times in the last few years ... search the google.com
> > archives.
> >
> > Daniel Morgan

>

Can you provide an example of what you mean by "same priviledges in a variety of schemas". We aren't talking abut system privileges here but object privileges. So if a user is granted SELECT ANY TABLE or CREATE ANY SEQUENCE those system privileges are global. But if you are granted SELECT ON <table_name> it is only valid for the single table in a single schema.

Personally I like to put the procedure, function, or package in the schema with the objects and then the only thing I grant to the user is EXECUTE on the code.

Daniel Morgan Received on Fri Feb 14 2003 - 10:53:44 CST