DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

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

Threaded View

  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 01:42 PM.

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