(p_username IN dba_role_privs.grantee%TYPE,
p_role OUT dba_role_privs.granted_role%TYPE) IS
v_username dba_role_privs.grantee%TYPE;
v_role dba_role_privs.granted_role%TYPE;
BEGIN
SELECT granted_role INTO v_role
FROM dba_role_privs
WHERE grantee = p_username
AND granted_role NOT LIKE 'CON%';
p_role:=v_role;
END;
This code will not compile I get error:
LINE/COL ERROR
--------------------------------------------------------------------------------
2/15 PLS-00201: identifier 'DBA_ROLE_PRIVS' must be declared
0/0 PL/SQL: Compilation unit analysis terminated
Hmmm...the user who owned this granted DBA with Admin option.
However, I granted select on dba_role_privs to this user and it worked?
Does that make sense? Why did I have to grant select on dba_role_privs?
Anyway, Thanks!
pennnn
The problem is that in order to use a privilege in a stored procedure (PL/SQL) it needs to be granted to you directly, not via a role. You were granted the DBA role, which allows you to query basically everything, but you can't run those queries in PL/SQL unless granted directly.
Hope that explains it.
sys. didn't work
Hmmm...the user who owned this granted DBA with Admin option.
However, I granted select on dba_role_privs to this user and it worked?
Does that make sense? Why did I have to grant select on dba_role_privs?
Anyway, Thanks!