Re: Can't select from SYS.DBA_USERS in a procedure

A copy of this was sent to "Chris Ermlich" <cermlich_at_swri.edu> (if that email address didn't require changing) On Fri, 11 Feb 2000 14:36:09 -0600, you wrote:

>Hi folks,
>
>I'm trying to select some values from the SYS.DBA_USERS table in a procedure
>but receive an error when trying to create the procedure:
>PLS-00201:identifier 'SYS.DBA_USERS' must be declared.
>It works fine with any other table but not with 'DBA_USERS'. I am executing
>this procedure as 'SYSTEM' and have full rights to all tables. What I'm
>ultimately trying to do is getting the username and password (encrypted) out
>of the DBA_USERS table for some 3rd party authentication application.
>
>Any help would be appreciated.

see http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html for the reason and the solution

>
>Chris,
>cermlich_at_swri.edu
>
>
>CREATE OR REPLACE PROCEDURE XYZ.ITC_SM_AUTH(user_id VARCHAR2) IS
>/* some variables here */
>old_pw varchar2(30);
>cid integer;
>res integer;
>
>BEGIN
>/* some other work here */
>
> select password into old_pw from SYS.DBA_USERS where username=user_id2;
>
>/* same statement works with any other table except SYS.DBA_USERS */
>/* some other work here */
>
>END;
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Feb 11 2000 - 15:25:20 CST