This discussion is archived
4 Replies Latest reply: Mar 29, 2012 10:17 AM by N K RSS

SET Role and PL/SQL

N K Pro
Currently Being Moderated
Hello folks

Is there a way/workaround to do a SET ROLE using PL/SQL ? Please advise

Thanks,
N K
  • 1. Re: SET Role and PL/SQL
    926577 Newbie
    Currently Being Moderated
    yes, you can try "execute immediate"
  • 2. Re: SET Role and PL/SQL
    N K Pro
    Currently Being Moderated
    nope, that wont work have tried already

    This is something whichwould be fired by a logon trigger and it wouldnt allow me to execute it:

    ORA-00604: error occurred at recursive SQL level 1
    ORA-06565: cannot execute SET ROLE from within stored procedure
    ORA-06512: at line 14
  • 3. Re: SET Role and PL/SQL
    rp0428 Guru
    Currently Being Moderated
    >
    Is there a way/workaround to do a SET ROLE using PL/SQL ? Please advise
    >
    No - you cannot set roles within a definer's rights procedure.

    See How Roles Work in PL/SQL Blocks in the Database Security doc
    http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm
    >
    How Roles Work in PL/SQL Blocks
    The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer's rights or invoker's rights.

    Roles Used in Named Blocks with Definer's Rights
    All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.
    >

    And be careful to heed this warning about LOGON triggers (see Using triggers in the PL/SQL doc - http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm)

    >
    •If you use a LOGON trigger to monitor logons by users, include an exception-handling part in the trigger, and include a WHEN OTHERS exception in the exception-handling part. Otherwise, an unhandled exception might block all connections to the database.
  • 4. Re: SET Role and PL/SQL
    N K Pro
    Currently Being Moderated
    Thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points