Interesting Requirement

I have been asked to give user A account lock and unlock so that it can lock and unlock only user B but if I happen to give it to user A then it can be used against any user.

Researched and came to following conclusion which looks promising


SQL> create or replace procedure MYLOCK as
2 begin
3 execute immediate 'alter user A account lock';
4 end;
5 /

Procedure created.

SQL> create or replace procedure MYUNLOCK as
2 begin
3 execute immediate 'alter user A account unlock';
4 end;
5 /

Procedure created.

SQL> grant execute on mylock to B;

Grant succeeded.

SQL> grant execute on myunlock to B;

Grant succeeded.

SQL> conn b/b
Connected.

SQL> conn a/a
Connected.

SQL> conn b/b
Connected.

SQL> exec sys.mylock;

PL/SQL procedure successfully completed.

SQL> conn a/a
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> conn b/b
Connected.
SQL> exec sys.myunlock;

PL/SQL procedure successfully completed.

SQL> conn a/a
Connected.