4 Replies Latest reply: Mar 29, 2012 12:17 PM by EmaxG RSS

    SET Role and PL/SQL

    EmaxG
      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
          yes, you can try "execute immediate"
          • 2. Re: SET Role and PL/SQL
            EmaxG
            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
              >
              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
                EmaxG
                Thank you.