The sys password needs to be decrypted during runtime for script run & encrypted after script executes successfully.No it doesn't!
There's no need to do this as SYS at all. Just create a procedure that unlocks a given username. With suitable protection against SQL injection:
grant create session, create procedure, alter user
to user_admin identified by user_admin;
conn user_admin/user_admin
create or replace procedure unlock_user (
username varchar2
) as
begin
execute immediate 'alter user ' ||
sys.dbms_assert.schema_name ( username ) ||
' account unlock';
end unlock_user;
/
Having this as a shell script on the database server is also a bad idea. This means staff need accounts on this machine, which increases your security risks.
Create a simple web app instead. This connects as a user that has execute privs on the procedure above:
conn chris/chris
grant create session
to unlock_app_user identified by unlock_app_user;
grant execute on user_admin.unlock_user to unlock_app_user;
alter user hr account lock;
conn hr/hr
Connection Failed
conn unlock_app_user/unlock_app_user
exec user_admin.unlock_user ( 'MADE_UP_USER' );
ORA-44001: invalid schema
exec user_admin.unlock_user ( 'HR' );
conn hr/hr
select user from dual;
USER
HR
If you need to run this on many databases, create the procedure on each one. And add an option in the app that chooses the appropriate database to unlock the account on.