7 Replies Latest reply on Nov 23, 2010 3:58 PM by varun4dba

    procedure privileges


      I have give the system privilege 'create any procedure' so that the users of the development team can see the pacakage body and the contents of the procedures of other schemas..
      The issue that we have is that with this privilege the users of the development team can compile these type of objects and that is something that we would like to remove from them..

      Does anyone know what options do we have?

        • 1. Re: procedure privileges
          alter any procedure
          • 2. Re: procedure privileges
            revoke execute on procedure_name* from user_name* ;

            • 3. Re: procedure privileges

              revoke create any procedure from <user>;

              any thing else you are looking? you can give execute permissions as

              grant execute on procedure <procedure_name> to <user>;

              Edited by: CKPT on Nov 23, 2010 7:06 PM
              • 4. Re: procedure privileges
                Hi again,

                the problem is that i want the users to see the body package and it's contents but i do not want the users to recompile it..
                the alter any procedure privilege is not given to them but since i want them to see the package body i have to give the create any procedure..(but create any procedure also recompile.. that's my problem)
                • 5. Re: procedure privileges
                  from privileged account

                  CREATE PROCEDURE DISPLAY_SOURCE(schema owner in VARCHAR2, procedure_name IN VARCHAR2)

                  GRANT EXECUTE ON DISPLAY_SOURCE TO <user_of_choice>
                  • 6. Re: procedure privileges
                    Where/ how are the developers trying to see the source?

                    Many GUIs will only show the source for packages that a user has the ability to execute. That is not an Oracle restriction, that is a restriction of the particular tool.

                    If you give the developers SELECT access on DBA_SOURCE, they can view the source code for any object in the database (you could grant the developers access to just that data dictionary view, but I would tend to think that giving them SELECT ANY DICTIONARY (or SELECT_CATALOG_ROLE) in development would be appropriate). That may or may not allow them to view the source in their favorite GUI, but that is a GUI issue not an Oracle privileges issue.

                    • 7. Re: procedure privileges
                      user12972346 says,

                      i do not want the users to recompile it..+

                      whatever priviledges you have granted to the user, just revoke execute on procedure can solve your poblem.
                      as you revoke execute on procedure the user will not be able to execute the procedure(this is what you want).