Hi That's right. I think I started that discussion. Whatever you do like AUTHID definer or current user, it won't help. The object privileges have to be granted directly. That can be pretty annoying if you try to seperate data owner schemas from code owners, but a couple scripts can automatize it. What we do now: Still maintan the roles for "normal" sql access via synonyms, additionally grant directly to the users that need to execute pl/sql code (data conversion in our case). Not too elegant, but whatever. Stefan -----Ursprungliche Nachricht----- Von: Naveen Nahata [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 12. August 2003 14:34 An: Multiple recipients of list ORACLE-L Betreff: RE: Referencing other schemas' tables in PL/SQL procedure There was a discussion on this list, regarding the same a few months back. It is annoying, but there are reasons for this restriction as pointed out by some in that thread. I didn't follow the thread deeply, but you can find it in the archives. Regards Naveen >>>-----Original Message----- >>>From: Paul Vincent [mailto:[EMAIL PROTECTED] >>>Sent: Tuesday, August 12, 2003 5:54 PM >>>To: Multiple recipients of list ORACLE-L >>>Subject: RE: Referencing other schemas' tables in PL/SQL procedure >>> >>> >>>Naveen - many thanks! I had a vague recollection that this >>>may be the case, rattling around in the back of my mind, but >>>I couldn't find confirmation in the manuals. Thanks for the >>>definitive answer! It's an annoying restriction, but now we >>>can cope with it! >>> >>>Paul >>> >>>-----Original Message----- >>>Sent: 12 August 2003 13:06 >>>To: Multiple recipients of list ORACLE-L >>> >>> >>>Any privileges granted through roles are not enabled in >>>PL/SQL procedures. >>> >>>You need to have the privilege granted directly not through a ROLE. >>> >>>Regards >>>Naveen >>> >>>>>>-----Original Message----- >>>>>>From: Paul Vincent [mailto:[EMAIL PROTECTED] >>>>>>Sent: Tuesday, August 12, 2003 5:29 PM >>>>>>To: Multiple recipients of list ORACLE-L >>>>>>Subject: Referencing other schemas' tables in PL/SQL procedure >>>>>> >>>>>> >>>>>>Hi, >>>>>> >>>>>>one of our developers is having a problem. His userid has >>>>>>the DBA role on a test database, and he's written a PL/SQL >>>>>>procedure, in his schema, which is referencing (via SELECT) >>>>>>and updating a table in another schema, so he's coding >>>the select as: >>>>>> >>>>>> CURSOR c1 >>>>>> IS >>>>>> select distinct ORIG_MODULE >>>>>> from QLDBA.GENTRAN >>>>>> where TRANS_DT = to_date('15/07/2003','dd/mm/yyyy'); >>>>>> >>>>>>...however, when he tries to compile the procedure, he gets >>>>>>several error messages including: >>>>>> >>>>>>Line # = 16 Column # = 11 Error Text = PLS-00201: identifier >>>>>>'QLDBA.GENTRAN' must be declared >>>>>> >>>>>> >>>>>>Now, the table GENTRAN certainly exists in the QLDBA schema, >>>>>>so there must be some rule being broken here. I thought >>>>>>anyone with the DBA role could do any DML on any table in >>>>>>any schema? Indeed, when the guy runs the select in a >>>>>>SQL*Plus window, it works fine, so can any PL/SQL guru shed >>>>>>some light on this? My PL/SQL skills are pretty rudimentary, >>>>>>and a rummage through the PL/SQL User Guide didn't turn >>>>>>anything up... >>>>>> >>>>>>Hope someone can help! >>>>>> >>>>>>Regards, >>>>>> >>>>>>Paul >>>>>>-- >>>>>>Please see the official ORACLE-L FAQ: http://www.orafaq.net >>>>>>-- >>>>>>Author: Paul Vincent >>>>>> INET: [EMAIL PROTECTED] >>>>>> >>>>>>Fat City Network Services -- 858-538-5051 >>>http://www.fatcity.com >>>>>>San Diego, California -- Mailing list and web >>>hosting services >>>>>>---------------------------------------------------------- >>>----------- >>>>>>To REMOVE yourself from this mailing list, send an E-Mail message >>>>>>to: [EMAIL PROTECTED] (note EXACT spelling of >>>'ListGuru') and in >>>>>>the message BODY, include a line containing: UNSUB ORACLE-L >>>>>>(or the name of mailing list you want to be removed >>>from). You may >>>>>>also send the HELP command for other information (like >>>subscribing). >>>>>> >>> >>> >>>DISCLAIMER: >>>This message (including attachment if any) is confidential >>>and may be privileged. Before opening attachments please >>>check them for viruses and defects. MindTree Consulting >>>Private Limited (MindTree) will not be responsible for any >>>viruses or defects or any forwarded attachments emanating >>>either from within MindTree or outside. If you have received >>>this message by mistake please notify the sender by return >>>e-mail and delete this message from your system. Any >>>unauthorized use or dissemination of this message in whole >>>or in part is strictly prohibited. Please note that e-mails >>>are susceptible to change and MindTree shall not be liable >>>for any improper, untimely or incomplete transmission. >>>-- >>>Please see the official ORACLE-L FAQ: http://www.orafaq.net >>>-- >>>Author: Naveen Nahata >>> INET: [EMAIL PROTECTED] >>> >>>Fat City Network Services -- 858-538-5051 http://www.fatcity.com >>>San Diego, California -- Mailing list and web hosting services >>>--------------------------------------------------------------------- >>>To REMOVE yourself from this mailing list, send an E-Mail message >>>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >>>the message BODY, include a line containing: UNSUB ORACLE-L >>>(or the name of mailing list you want to be removed from). You may >>>also send the HELP command for other information (like subscribing). >>>-- >>>Please see the official ORACLE-L FAQ: http://www.orafaq.net >>>-- >>>Author: Paul Vincent >>> INET: [EMAIL PROTECTED] >>> >>>Fat City Network Services -- 858-538-5051 http://www.fatcity.com >>>San Diego, California -- Mailing list and web hosting services >>>--------------------------------------------------------------------- >>>To REMOVE yourself from this mailing list, send an E-Mail message >>>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >>>the message BODY, include a line containing: UNSUB ORACLE-L >>>(or the name of mailing list you want to be removed from). You may >>>also send the HELP command for other information (like subscribing). >>> DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
