Permission Problems

From: Marc Eggenberger <nw1_at_devnull.ch>
Date: Sat, 2 Aug 2003 15:42:27 +0200
Message-ID: <MPG.1995de2eda0ab4bb989692@news.cis.dfn.de>

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 ...

-- 
mfg
Marc Eggenberger

Received on Sat Aug 02 2003 - 08:42:27 CDT