Marc Eggenberger wrote:
> Hi there ..
>
> System:
> Oracle 8.1.7.0.0 on Windows 2000 ADV SP3
>
> I try to create a procedure which access tables from another shema. I
> created a role and I granted the following to that role:
>
> CREATE ROLE "AMS_METADIR_IMPORT" NOT IDENTIFIED;
> GRANT CREATE PROCEDURE TO "AMS_METADIR_IMPORT"
> GRANT CREATE TABLE TO "AMS_METADIR_IMPORT"
> GRANT DELETE ON "AMS"."USER_DEF_SAVE" TO "AMS_METADIR_IMPORT"
> GRANT INSERT ON "AMS"."USER_DEF_SAVE" TO "AMS_METADIR_IMPORT"
> GRANT UPDATE ON "AMS"."USER_DEF" TO "AMS_METADIR_IMPORT";
>
> When I try to create the procedure I get an PLS-00904 saying that I dont
> have the permissions to access the object USER_DEF
>
> The code which creates the error is
>
> CREATE OR REPLACE PROCEDURE Import_From_Metadir
> IS
>
> --Declarations
> CURSOR metadir_cur IS
> SELECT sobjUniqueKey, sPersonalNumber, sLastName,
> sFirstName, sNetzwerkLogin, sDepartment, sKST,
> sOfficePhoneNumber
> FROM metadir_import.TBLPERSONALDATA;
>
>
> BEGIN
>
>
> --Delete entries with no sNetzwerklogin (Username) from
> Importtable first
> DELETE FROM metadir_import.TBLPERSONALDATA WHERE snetzwerklogin
> IS NULL;
>
> --Loop over all remaining entries and update the AMS Table
> user_def
> FOR metaupdate_rec IN metadir_cur
> LOOP
> UPDATE ams.USER_DEF SET NAME = SUBSTR
> (metaupdate_rec.sLastName,1,25)
> , Vorname = SUBSTR(metaupdate_rec.sFirstName,1,25) ,
> Bereich =
> metaupdate_rec.sDepartment, Stammnr =
> metaupdate_rec.sPersonalNumber,
> Tel_Int = SUBSTR(metaupdate_rec.sOfficePhoneNumber,1,15) ,
> Kostenstl =
> SUBSTR(metaupdate_rec.sKST,1,10), Unique_ID =
> metaupdate_rec.sobjUniqueKey
> WHERE Username = metaupdate_rec.sNetzwerklogin;
> END LOOP;
> COMMIT;
> END;
> /
>
>
> The update in the loop gives me the error .. but why? The user I use has
> the role I created and this role gives him update rights on ams.user_def
> ...
> Why am I getting this error?
>
> Thanks for any hints ...
>
Privs obtained via a ROLE are NOT available within a procedure.
The GRANT must be explicitly given the user invoking the procedure.
Received on Sat Aug 02 2003 - 08:53:11 CDT
