5 Replies Latest reply: May 5, 2012 5:59 AM by Mikail RSS

    Create procedure privelege granted but can't create procedure

    Mikail
      I have a user to which I've granted the following priveleges:
      CREATE SESION
      SELECT ANY TABLE
      CREATE ANY PROCEDURE
      CREATE PROCEDURE
      EXECUTE PROGRAM
      EXECUTE PROCEDURE

      But when I try to create a procedure with that user I get, insufficient proveleges error. What am I doing wrong?
        • 1. Re: Create procedure privelege granted but can't create procedure
          Billy~Verreynne
          What's wrong? You are opening the proverbial barn doors ito security. That is not how security should be done - allowing a schema to create any procedural code anywhere in the database. Or select data from any table.

          Just how do you think Sony's PS network was hacked and millions of users's credit card data stolen?

          By this type of insecure stuff-security approach to security.

          A schema needs the minimum privs in order to achieve its goals and requirements. Nothing more.

          E.g.
          // standard logical database schema, 10Gb space allocation
          create user HRDB 
            identified by <secret password here>
            default tablespace USERS
            quota 10G on USERS;
          
          // configure the basic security layer for the schema
          grant
            create session, --// allow client-server connections to schema
            create table, create trigger, --// allow to create standard db objects
            create sequence, create view, --// allow access to defining extended objects
            create procedure --// allow creating stored proc code
          to HRDB;
          In addition to this, you may want to decide on assign a resource profile and specific roles and so on. In some cases you also may want to allow the schema to create types, private database links and synonyms, materialised views, etc.

          No grant any access. No access to SYS code and objects. By default. Anything else is a security exception that needs sound justification.
          • 2. Re: Create procedure privelege granted but can't create procedure
            Mikail
            I get the same error when I grant just CREATE PROCEDURE.
            • 3. Re: Create procedure privelege granted but can't create procedure
              Centinul
              Mikail wrote:
              I get the same error when I grant just CREATE PROCEDURE.
              Can you show us what your doing by providing the command you are executing and Oracle's response?
              • 4. Re: Create procedure privelege granted but can't create procedure
                rp0428
                >
                But when I try to create a procedure with that user I get, insufficient proveleges error.
                >
                Is there some reason you don't want to post the procedure DDL and the actual error you are getting?

                That error can be caused if your procedure code tries to reference ANY object that it doesn't have privileges to.

                So without knowing what the code is doing how can we tell what the problem is?
                • 5. Re: Create procedure privelege granted but can't create procedure
                  Mikail
                  Here's the DDL

                  CREATE OR REPLACE PROCEDURE TOTALBASE.ROUNDUP
                  (
                  CUR OUT SYS_REFCURSOR )
                  AS
                  BEGIN
                  OPEN CUR FOR
                  ('SELECT * FROM TOTALBASE.PENSIONERS');
                  END ROUNDUP;

                  The user can select from TOTALBASE.PENSIONERS