Home » RDBMS Server » Security » DBA_USER privilege (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0)
DBA_USER privilege [message #673129] Thu, 08 November 2018 07:39 Go to previous message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
what roles or privileges do i need to use the data dictionary view dba_users on a database procedure. i have select privilege on the view dba_role but when i attempt to use it inside a database procedure it throws an error "ORA-00942: table or view does not exist".

example:
SQL> create or replace procedure wtolentino.prc_test_dba_view as
  2    vUserName varchar2(40);
  3  begin
  4    select username
  5      into vUserName
  6      from dba_users
  7     where username = 'WTOLENTINO';
  8    dbms_output.put_line('username '||vUserName);
  9  end;
 10  /

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE WTOLENTINO.PRC_TEST_DBA_VIEW:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: SQL Statement ignored
6/10     PL/SQL: ORA-00942: table or view does not exist
SQL> 
SQL> select count(*) from dba_users;

  COUNT(*)
----------
       139

SQL> select username from dba_users where username = 'WTOLENTINO';

USERNAME
--------------------------------------------------------------------------------
WTOLENTINO

SQL>

thanks.
 
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Allow access from UI and not from back-end
Next Topic: Who locked oracle user
Goto Forum:
  


Current Time: Thu Mar 28 08:12:30 CDT 2024