Re: Set Role command in SQL*Forms 4.0 or SQL*Menu - Wrong!

JAMES -- I tested this on 7.0.16.4 of Oracle on my Sparcstation, with

         both Forms 3.0.16.12 and Forms 4.0.12 and as my previous
         email suggested, calling the DBMS_SESSION package's Set_Role
         command, enables a role for the current session, not only for
         the duration of the stored procedure invocation. To test this
         I did the following:



            (1) Created a table called OBJ as SYSTEM, and inserted one


                row. 


            (2) Created a role called OBJROLE

            (3) Granted SELECT on OBJ to OBJROLE

            (4) Granted OBJROLE to SCOTT


            (5) Alter User'ed SCOTT to make sure OBJROLE was not


                one of his Default Role's


            (6) Built a form with a database block based on


                "SYSTEM.OBJ", with a KEY-PRINT trigger that calls
                DBMS_SESSION.SET_ROLE('objrole'); 


            (7) Ran the form as SCOTT


            (8) Execute Query in the block to get an ORACLE error


                (table or view does not exist.)


            (9) Press the [Print] key to fire the KEY-PRINT trigger to


                enable the role.
           (10) Execute Query in the block to successfully query the
                data from that table.

         If these steps on your 7.0.12 database don't produce the same
         result, then I can only surmise that some problem has been
         fixed since 7.0.12.

         Hope this helps.

--

Steve Muench         Email: smuench_at_oracle.com
Forms Development      CIS: 73404,676
Product Manager
Oracle Corporation