Can't grant execute to user, even as SYS?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Can't grant execute to user, even as SYS?

  1. #1
    Join Date
    Feb 2008
    Posts
    3

    Question Can't grant execute to user, even as SYS?

    Hello,
    I have a user that has created a stored procedure. However, I'm having difficulty granting the execute privilege to any user. I even executed the following statement as SYSTEM and SYS (as SYSDBA) where tim is the user that created the stored procedure and cs_pass is the stored procedure:
    Code:
    SQL> grant execute on tim.cs_pass to toby;
    grant execute on tim.cs_pass to toby;
                         *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    I also granted to following to myself, and I still get the same result. Any help?
    Code:
    GRANT DBA TO SYSTEM;
    GRANT CONNECT TO SYSTEM;
    GRANT RESOURCE TO SYSTEM;
    GRANT ANY PRIVILEGE TO SYSTEM;
    GRANT CONNECT_MTN TO SYSTEM;
    GRANT EXP_FULL_DATABASE TO SYSTEM;
    GRANT IMP_FULL_DATABASE TO SYSTEM;
    GRANT SELECT_CATALOG_ROLE TO SYSTEM;
    GRANT EXECUTE_CATALOG_ROLE TO SYSTEM;
    GRANT AQ_ADMINISTRATOR_ROLE TO SYSTEM;
    GRANT GATHER_SYSTEM_STATISTICS TO SYSTEM;
    ALTER USER SYSTEM DEFAULT ROLE ALL;
    GRANT ANALYZE ANY TO SYSTEM;
    GRANT ALTER ANY TYPE TO SYSTEM;
    GRANT ALTER ANY INDEX TO SYSTEM;
    GRANT ALTER ANY TABLE TO SYSTEM;
    GRANT EXECUTE ANY TYPE TO SYSTEM;
    GRANT SELECT ANY TABLE TO SYSTEM;
    GRANT ALTER ANY INDEXTYPE TO SYSTEM;
    GRANT ALTER ANY PROCEDURE TO SYSTEM;
    GRANT DEBUG ANY PROCEDURE TO SYSTEM;
    GRANT UNLIMITED TABLESPACE TO SYSTEM;
    GRANT EXECUTE ANY INDEXTYPE TO SYSTEM;
    GRANT EXECUTE ANY PROCEDURE TO SYSTEM;
    GRANT SELECT ANY DICTIONARY TO SYSTEM;
    It it helps, the code for the stored procedure is:
    Code:
    CREATE OR REPLACE PROCEDURE cs_pass
    	(usernam varchar2,
    	 passw varchar2)
    	AUTHID DEFINER IS
    
      usern     varchar2 (10);
      pw        varchar2 (10);
      sb	    char     (2);
      oper      char     (8);
      comp	    char     (10);
      user      char     (10);
      user_id   numeric  (5);
      fail_sw   char     (1);
    
    PROCEDURE write_log (comp char, user char, user_id numeric, fail_sw char, oper char) IS
    
    BEGIN
    execute immediate 'INSERT INTO TIM.T_CS_PASSWORD_LOG (comp, TIM_username, TIM_user_id,
    		success_switch, operation, create_date) values (:1, :2, :3, :4, :5, :6)'
                    using comp, user, user_id, fail_sw, oper, sysdate;
    END;
    
    BEGIN
    
    fail_sw := 'F';
    comp    := usernam;
    user_id := uid;
    oper    := 'PASSWORD';
    
      execute immediate 'SELECT USERNAME FROM DBA_USERS WHERE USER_ID = :1' INTO user USING uid;
    
    usern   := usernam;
    pw      := passw;
    sb      := substr(usern, 1, 2);
    
     if sb <> 'BK' then
         write_log(comp, user, user_id, fail_sw, oper);
         return;
     end if;
    
     if upper(pw) = 'UNLOCK' then
         execute immediate 'alter user '||usern||' account unlock';
         oper := 'UNLOCK  ';
         fail_sw := 'S';
         write_log(comp, user, user_id, fail_sw, oper);
         return;
     end if;
    
         execute immediate 'alter user '||usern||' identified by '||pw;
         fail_sw := 'S';
         write_log(comp, user, user_id, fail_sw, oper);
      RETURN;
    END;
    /
    Last edited by DrNeko; 02-25-2008 at 12:42 PM.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Login in as "tim" and just grant the privilege.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Feb 2008
    Posts
    3
    Thanks! It works!

  4. #4
    Join Date
    Feb 2008
    Posts
    3
    I have a quick question for future reference: Is it possible to grant the privilege through through another user? I'm guessing it's impossible to grant the privilege through a role. Thanks.
    Last edited by DrNeko; 02-25-2008 at 01:58 PM.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    don't be that lazy man, you can easily test it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width