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
lets say I have a variety of users and I different users may need the
same priviledges in a variety of schemas. What is the best method for
implementing this? I know we can't use roles, but does Oracle provide
anything else to make it easier to manage this?
Received on Fri Feb 14 2003 - 08:02:21 CST
