Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3343
  • Last Modified:

Stored procedures error PLS-00201


(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                          


Any ideas?


Thanks
0
auvillem
Asked:
auvillem
  • 2
1 Solution
 
Daniel StanleyDatabase engineerCommented:
Privileges granted via role are disabled inside stored procedures.  grant explicitly instead of from a role like dba.

example:
grant select on dba_role_privs to %USERNAME%;

good luck,
daniels@asix.com
0
 
Daniel StanleyDatabase engineerCommented:
here is a more detailed example:


$system$ORCL@scatcat>conn ascend/ascend
connected.
$ascend$ORCL@scatcat>create or replace procedure test(p_username IN dba_role_privs.grantee%TYPE,
  2  p_role OUT dba_role_privs.granted_role%TYPE) IS
  3  v_username dba_role_privs.grantee%TYPE;
  4  v_role dba_role_privs.granted_role%TYPE;
  5  BEGIN
  6  SELECT granted_role INTO v_role
  7  FROM dba_role_privs
  8  WHERE grantee = p_username
  9  AND granted_role NOT LIKE 'CON%';
 10  p_role:=v_role;
 11  END;
 12  /

Warning: Procedure created with compilation errors.

$ascend$ORCL@scatcat>show err
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/30     PLS-00201: identifier 'DBA_ROLE_PRIVS' must be declared


$ascend$ORCL@scatcat>conn system/manager
connectd.

$system$ORCL@scatcat>revoke dba from ascend;

Revoke succeeded.

$system$ORCL@scatcat>grant select on dba_role_privs to ascend;

Grant succeeded.

$system$ORCL@scatcat>conn ascend/ascend
Connected.
$ascend$ORCL@scatcat>create or replace procedure test(p_username IN dba_role_privs.grantee%TYPE,
  2  p_role OUT dba_role_privs.granted_role%TYPE) IS
  3  v_username dba_role_privs.grantee%TYPE;
  4  v_role dba_role_privs.granted_role%TYPE;
  5  BEGIN
  6  SELECT granted_role INTO v_role
  7  FROM dba_role_privs
  8  WHERE grantee = p_username
  9  AND granted_role NOT LIKE 'CON%';
 10  p_role:=v_role;
 11  END;
 12  /

Procedure created.

$ascend$ORCL@scatcat>show err
No errors.
$ascend$ORCL@scatcat>



good luck,
daniels@asix.com
0
 
bgopalreddyCommented:
Please check your privileges. I think you don't have privileges(i.e DBA privilege or SELECT_ANY_TABLE privilege)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now