7 Replies Latest reply on Jun 8, 2012 6:46 PM by RN

    Permissions on types

      I have an object type :
          create type test_obj as object....
      then I have a collection type:
          create type test_type as table of test_obj;
      Execute permission have been given on test_type to a role(say test_role). But there are no permission granted on test_obj.

      Application connects to the db with test_role and calls a stored procedure with parameter of type test_type.
      Assuming all the privs for running stored procedure and other db objects are in place.

      My question is that everything works even without giving any access to test_obj. Is execute on test_type is sufficient?
      When do I need to grant execute permission on test_obj?

        • 1. Re: Permissions on types
          Solomon Yakobson
          By default stored procedures are created with DEFINER rights which means roles are ignored. You need to grant SP owner execute on type directly, not via role. But even if you create SP with invoker rights, you still need to grant SP owner execute on type directly, not via role (it it other users who will execute SP can have execute on type granted via role). Roles are ignores at SP compile time no matter what.

          • 2. Re: Permissions on types
            Thanks SY. But I did not get my answer. I realized that it's because my question was not clear enough. Let me try to explain my scenario:

            The stored procedures and types are compiled in schema A. I understand that the SPs are compiled with Definers Right by default and roles are not checked/are disabled at the compilation time.

            Test_role in schema A has been granted this:
            1.Execute permissions for the SPs
            2.DML permissions for all the tables
            3.Execute permission on test_type(but not on test_obj)

            Now there is another user/schema B:
            we have synonyms created here for every object (tables, objects, collections, SPs, sequences )of schema A
            and role test_role is enabled here
            now, user B with test_role executes SP(which is compiled in A but have synonym in B). This SP has parameter of type test_type.

            Now my question is : does test_role need execute on test_obj ? Is it sufficient to have execute on test_type?

            Please let me know if my explanation is still not clear.

            Thanks for the help!
            • 3. Re: Permissions on types
              Solomon Yakobson
              RN wrote:

              Now my question is : does test_role need execute on test_obj ? Is it sufficient to have execute on test_type?
              Irrelevant. Role is not needed at all. SP with definer rights always executes under owner's security domain. So since SP owner has (and is required to have) all privileges granted directly, there is no need for the role. If SP would be with invoker rights, then user B would need the privileges granted to him (either directly or via role).

              • 4. Re: Permissions on types
                The question of roles aside, without execute permissions on the "test_obj" type, how would user B be able to pass anything other than an empty "test_type" table (not being able to construct any "test_obj" instances to populate it with)?

                • 5. Re: Permissions on types
                  Solomon Yakobson
                  gaverill wrote:
                  The question of roles aside
                  SP is owned by user A. When user B (who has execute on SP) runs it Oracle switches security domain to user A and any reference to type is done using user A privileges.

                  • 6. Re: Permissions on types
                    Solomon Yakobson
                    gaverill wrote:
                    The question of roles aside
                    Now I see your point. It is passing parameter to SP question. If user B is not granted execute on object type user B can call SP but all user B can pass is table of NULL objects:
                    SQL> create type obj_type
                      2    as object(
                      3              n number,
                      4              v varchar2(10)
                      5             )
                      6  /
                    Type created.
                    SQL> create type obj_tbl_type
                      2    as table of obj_type
                      3  /
                    Type created.
                    SQL> grant execute on obj_tbl_type to u2
                      2  /
                    Grant succeeded.
                    SQL> connect u2/u2
                    SQL> select * from table(u1.obj_tbl_type(u1.obj_type(1,'A')));
                    select * from table(u1.obj_tbl_type(u1.obj_type(1,'A')))
                    ERROR at line 1:
                    ORA-00904: : invalid identifier
                    So question to OP is what user B is passing as parameter.

                    1 person found this helpful
                    • 7. Re: Permissions on types
                      exactly, thats what is my point too. I also created test scripts to show you where the call to SP will fail in schema B. Because userB can not pass the IN parameter of test_type as userB does not have explicitly given execute priv for test_type as well as test_obj.

                      So, userB needs access to both test_obj as well as test_type. I was wrong in my original question that execute on ONLY test_type can work without having execute on test_obj.
                      See the flow below:
                       *In schema A:*
                      create or replace type test_obj as object (emp_id number(10));
                      create or replace type test_type as table of test_obj;
                      grant execute on test_type to userB;
                      create or replace procedure test_sp (emp_name varchar2,
                                                           emp_type  test_type
                      NULL;  -does not matter at this point
                      grant execute on test_sp to userB;
                      *In schema userB:*
                      v_arr   userA.test_type :=userA.test_type();
                      v_arr(v_arr.LAST) :=  userA.test_obj(2);
                      --won't run as test_obj is unkown to userB
                      In schema userA:
                      grant execute on test_obj to userB;
                      Now userB can run test_sp successfully. I guess, things are clear now and the question is answered.

                      Thanks a lot!