Re: How to determine in PL/SQL if a user has a certain role?

Holger Peine <peine_at_iese.fraunhofer.de> wrote

> I need to determine if a certain user has a certain role in a PL/SQL
> procedure, something like
>
> IF 'fred' HAS ROLE 'clerk' THEN ...
>
> However, I cannot find a role operator (like the hypothetical HAS ROLE
> above) in the PL/SQL reference. What is the proper way to achieve my
> need?

Look at the data dictionary table DBA_ROLES.

HOWEVER.. I will be hesitant doing this type of thing in PL/SQL. It is a bad idea to address security and access issues manually via roles (e.g. coding IF-THEN-ELSE application logic in PL/SQL based on Oracle Roles). Nor should a user be concerned about the actual roles he have... they simply are.

Fine Grained Access Controls, views, Oracle Label Security and PL/SQL authid and GRANTs address the issues of a) what data can be seen, b) what processing can be done and c) as who the process runs.

E.g. I'm not in favour of using the following approach: BEGIN
 IF 'fred' HAS ROLE 'clerk' THEN

     start_clerk_process
 ELSE
     start_default_process
 END IF
END That application logic can easily be bypassed. Instead, FRED must have execution rights on process START_CLERK_PROCESS and user JOHN not. I.e.
BEGIN
  start_clerk_process;
  // process will fail if user does not have execution   // privs

  exception

     .. handle the priv failure..
END If instead you're doing automated role assignment via PL/SQL for DB admin purposes, then you simply need to look at the DBA_* dictionary views and likely consider using the EXECUTE IMMEDIATE to automate role management (to whatever extent possible in your environment).

--
Billy