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 next message
wtolentino
Messages: 252
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.
Re: DBA_USER privilege [message #673130 is a reply to message #673129] Thu, 08 November 2018 07:44 Go to previous messageGo to next message
John Watson
Messages: 7664
Registered: January 2010
Location: Global Village
Senior Member
You need to
GRANT SELECT ON DBA_USERS TO WTOLENTINO;
roles are not usable in definers rights stored pl/sql.

[Updated on: Thu, 08 November 2018 07:44]

Report message to a moderator

Re: DBA_USER privilege [message #673131 is a reply to message #673130] Thu, 08 November 2018 07:55 Go to previous messageGo to next message
gazzag
Messages: 1042
Registered: November 2010
Location: Bristol, UK
Senior Member
As John says, database objects need direct grants as roles are not active in PL/SQL.
Re: DBA_USER privilege [message #673133 is a reply to message #673131] Thu, 08 November 2018 08:14 Go to previous messageGo to next message
wtolentino
Messages: 252
Registered: March 2005
Senior Member
Thank you all.
Re: DBA_USER privilege [message #673135 is a reply to message #673133] Thu, 08 November 2018 10:18 Go to previous message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or you can define the procedure with "invoker_rights_clause" as "AUTHID CURRENT_USER".

[Updated on: Thu, 08 November 2018 10:18]

Report message to a moderator

Previous Topic: Who locked oracle user
Goto Forum:
  


Current Time: Thu Nov 15 17:18:19 CST 2018