dbms_lock.sleep Grant Execute Privilege in Oracle

I will explain dbms_lock.sleep Grant Execute Privilege in Oracle in this post.

dbms_lock.sleep Grant Execute Privilege in Oracle

Developers or some database users want to use and run dbms_lock.sleep but they are not allowed to grant EXECUTE privilege on the whole package but can grant for specific procedure in the package.

grant execute on sys.dbms_lock

You can give this permission to the users as follows.

SQL> grant execute on sys.dbms_lock to MSDBA;

Grant succeeded.

SQL> grant execute on sys.dbms_lock to BIGDATA;

Grant succeeded.

SQL>

Sometimes you can get the following error while granting this privilige.

We could not grant privilege directly to non-DBA users due to the following error:

SQL> conn <user_name>/<password> as sysdba
Connected.
SQL> GRANT EXECUTE ON sys.dbms_lock.sleep TO appuser;
GRANT EXECUTE ON sys.dbms_lock.sleep TO appuser
                              *
ERROR at line 1:
ORA-00905: missing keyword

But we can create another procedure under SYS and then grant the privilege to a non-DBA users with below steps:

-- create appuser user:
create user appuser identified by <PASSWORD>;
grant resource, connect to appuser;
alter user appuser account unlock;

-- create usersleep() procedure
CREATE OR REPLACE PROCEDURE sys.usersleep(n in number) IS
BEGIN
  DBMS_LOCK.sleep(n);
END;
/

-- grant privilege
GRANT EXECUTE ON sys.usersleep TO appuser;
  
 
-- Test Result:
SQL> show user
USER is "APPUSER"
SQL>
SQL> exec sys.usersleep(5);
-- Wait 5s --
PL/SQL procedure successfully completed.

If you want to learn more details about how to Grant and Revoke priviliges in Oracle, Read the following post. 

How to Grant and Revoke Privileges | Create and Drop any User in Oracle Database

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )