1 2 Previous Next 19 Replies Latest reply on Jan 24, 2011 12:24 PM by Peter Gjelstrup Go to original post
      • 15. Re: grant execute on package
        EmadKehail
        I will suppose the following is true:
        There is a package called HR_PKG which contains the following procedures:
        - A
        - B
        - C
        - D
        - E

        I will also suppose there are 2 users, X and Y. User X will use procedures A and B, while user Y will use procedures C and D. Well, for sure you can use wrappers and develop a procedure for each user. This will solve the problem for this* situation. But, if you are going to add another user in the future, suppose user Z, that needs to execute procedures A, D and E, then another wrapper is required!!!

        If those users are managed in groups and classified well (think of table Employees in the HR schema that classify the users), then - as I believe - you can grant the execute on the package to the users, and control the security with the Oracle VPD (Virtual Private Database).
        The following link explains the idea and it also contains additional Hands-On
        http://www.oracle.com/technetwork/database/security/index-088277.html

        Would that be good for this post??
        • 16. Re: grant execute on package
          Peter Gjelstrup
          Would that be good for this post??
          Not sure,

          Have you any idea on how to outline a solution for fine-grained execution control on subprograms using VPD?

          Regards
          Peter
          • 17. Re: grant execute on package
            mmatar
            The example Emad posted is almost the case I have.
            But how would VPD help me.

            The example I posted suppose the two users work on the same data, not different data, user_employee would fill the data using one procedure and user_manager would update on the same record but using another procedure in the same package. This was my example.
            The purpose - as I know - of the package is to collect related procedure and functions - in the data issue not the security issue -, am I right?!!

            I want something like VPD, Is there any? workaround?

            Thank you all for replies.
            • 18. Re: grant execute on package
              EmadKehail
              Since you need someone to insert data only, while the other will update the data then you might need to go for Secure Application Role.

              See the link below

              http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/security/approles/approles_otn.htm

              You can have roles for Insert and Roles for Update and then manage them using Secure Application Roles
              • 19. Re: grant execute on package
                Peter Gjelstrup
                Emad wrote:

                You can have roles for Insert and Roles for Update and then manage them using Secure Application Roles
                Emad, stop posting links to irrelevant stuff. This is not about Insert and Update. This is about - let's call it - "fine grained access control to packaged subprograms".

                A thing I stated does not exist. Could be that I'm wrong, you already suggested that it does, and linked to the front page of VPD.

                I asked you to outline a solution, instead. A thing you reclined to do, instead you linked to something completely different.


                @mmtar
                The purpose - as I know - of the package is to collect related procedure and functions - in the data issue not the security issue -, am I right?!!
                Let's just say that a package is a collection of related procedure and functions.

                Question is, are they related if these procedures and functions relate to different use/users?

                Maybe, maybe not - Your choice.

                Earlier I linked to examples on how Oracle solves this, themselves. Link was

                http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/authoriz.htm#i1007675


                Example where they do it by splitting package:
                Packages and Package Objects: Example 1
                
                This example shows four procedures created in the bodies of two packages. 
                
                CREATE PACKAGE BODY hire_fire AS 
                  PROCEDURE hire(...) IS 
                    BEGIN 
                      INSERT INTO employees . . . 
                    END hire; 
                  PROCEDURE fire(...) IS 
                    BEGIN 
                      DELETE FROM employees . . . 
                    END fire; 
                END hire_fire; 
                
                CREATE PACKAGE BODY raise_bonus AS 
                  PROCEDURE give_raise(...) IS 
                    BEGIN 
                      UPDATE employees SET salary = . . . 
                    END give_raise; 
                  PROCEDURE give_bonus(...) IS 
                    BEGIN 
                      UPDATE employees SET bonus = . . . 
                    END give_bonus; 
                END raise_bonus; 
                
                Access to execute the procedures is given by granting the EXECUTE privilege for the package by using the following statements:
                
                GRANT EXECUTE ON hire_fire TO big_bosses; 
                GRANT EXECUTE ON raise_bonus TO little_bosses; 
                Example where they do it with wrappers:
                Packages and Package Objects: Example 2
                
                This example shows four procedure definitions within the body of a single package. Two additional standalone procedures and a package are created specifically to provide access to the procedures defined in the main package.
                
                CREATE PACKAGE BODY employee_changes AS 
                  PROCEDURE change_salary(...) IS BEGIN ... END; 
                  PROCEDURE change_bonus(...) IS BEGIN ... END; 
                  PROCEDURE insert_employee(...) IS BEGIN ... END; 
                  PROCEDURE delete_employee(...) IS BEGIN ... END; 
                END employee_changes; 
                 
                CREATE PROCEDURE hire 
                  BEGIN 
                    employee_changes.insert_employee(...) 
                  END hire; 
                 
                CREATE PROCEDURE fire 
                  BEGIN 
                    employee_changes.delete_employee(...) 
                  END fire; 
                 
                PACKAGE raise_bonus IS 
                  PROCEDURE give_raise(...) AS 
                    BEGIN 
                      employee_changes.change_salary(...) 
                    END give_raise; 
                 
                  PROCEDURE give_bonus(...) 
                    BEGIN 
                      employee_changes.change_bonus(...) 
                    END give_bonus; 
                
                Using this method, the procedures that actually do the work (the procedures in the employee_changes package) are defined in a single package and can share declared global variables, cursors, on so on. By declaring top-level procedures, hire and fire, and an additional package, raise_bonus, you can grant selective EXECUTE privileges on procedures in the main package:
                
                GRANT EXECUTE ON hire, fire TO big_bosses; 
                GRANT EXECUTE ON raise_bonus TO little_bosses; 
                Regards
                Peter
                1 2 Previous Next