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;
/
