How to validate the username and password inside the stored procedure

[A Sundar]

Hi,   

    For example i have stored procedure with the parameters username and password. Inside the stored procedure i want to check if the username and password are a valid oracle account. Is there any way?

Regards,

Sundar

[Michael Moore]

You can find out from dictionary tables the names of all the schemas/users, but you can not retrieve the passwords. That would be a huge security problem if you could. 

--
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

---
You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[A Sundar]

Thanks for your update.

Is it possible to connect inside the stored procedure for checking for valid oracle account.

Regards,

Sundar

[ddf]

On Tuesday, September 9, 2014 10:18:25 AM UTC-6, sundar wrote:

Thanks for your update.

Is it possible to connect inside the stored procedure for checking for valid oracle account.

Regards,

Sundar

No.  DBA_USERS provides that information:  ACCOUNT_STATUS should be OPEN for valid accounts:

SQL> select username, created, account_status
  2  from dba_users;

USERNAME                       CREATED   ACCOUNT_STATUS
------------------------------ --------- --------------------------------
SYSTEM                         10-JAN-14 OPEN
SYS                            10-JAN-14 OPEN
PERFSTAT                       25-APR-14 OPEN
BINGNORFER                     13-MAR-14 OPEN
PMS2                           23-JUN-14 OPEN
RUMPUS                         14-JUL-14 OPEN
DFITZJ                         12-MAY-14 OPEN
BKPUSER                        23-JUN-14 OPEN
OUTLN                          10-JAN-14 EXPIRED & LOCKED
SI_INFORMTN_SCHEMA             10-JAN-14 EXPIRED & LOCKED
ORDPLUGINS                     10-JAN-14 EXPIRED & LOCKED

USERNAME                       CREATED   ACCOUNT_STATUS
------------------------------ --------- --------------------------------
XDB                            10-JAN-14 EXPIRED & LOCKED
ANONYMOUS                      10-JAN-14 EXPIRED & LOCKED
ORDDATA                        10-JAN-14 EXPIRED & LOCKED
APEX_030200                    10-JAN-14 EXPIRED & LOCKED
APPQOSSYS                      10-JAN-14 EXPIRED & LOCKED
WMSYS                          10-JAN-14 EXPIRED & LOCKED
DBSNMP                         10-JAN-14 EXPIRED & LOCKED
EXFSYS                         10-JAN-14 EXPIRED & LOCKED
ORDSYS                         10-JAN-14 EXPIRED & LOCKED
MDSYS                          10-JAN-14 EXPIRED & LOCKED
FLOWS_FILES                    10-JAN-14 EXPIRED & LOCKED

USERNAME                       CREATED   ACCOUNT_STATUS
------------------------------ --------- --------------------------------
APEX_PUBLIC_USER               10-JAN-14 EXPIRED & LOCKED
DIP                            10-JAN-14 EXPIRED & LOCKED
XS$NULL                        10-JAN-14 EXPIRED & LOCKED
ORACLE_OCM                     10-JAN-14 EXPIRED & LOCKED

26 rows selected.

SQL>

Your query from the procedure or PL/SQL block can address this depending on the value assigned to ACCOUNT_STATUS.

David Fitzjarrell