How to grant execution rights on DBMS packages to a PDB user?

Question

I'd like to grant execution right on dbms_lock package to a local PDB user.

I have created new PDB with a local administrative user. I'm assuming I can use that local user for all management tasks in PDB. But when I try to grant execution right on dbms_lock to another local user I get ORA-01031: insufficient privileges.

I'm using Oracle 12c database app development VM.

The steps to re-produce the problem:

Create new PDB as CDB SYS user

Creating a PDB Using the Seed

create pluggable database test1
admin user test1admin identified by test1admin
roles = (DBA)
file_name_convert = ('/pdbseed/', '/test1/')
;

alter pluggable database test1 open;

Log in to PDB as test1admin and create new local non-administrative user

create user test1 identified by test1;
grant connect to test1;
grant execute on dbms_lock to test1;

Error starting at line : 3 in command -
grant execute on dbms_lock to test1
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

I have used test1admin succesfully to grant several other privileges to th elocal users. I have also verified test1admin user has access to dbms_lock package. But it seems that dbms_lock-package (and btw dbms_crypto too) is different.

Answer 1

By default, noone is granted the EXECUTE privilege on DBMS_LOCK with grant option. Even the DBA role does not have the grant option. test1admin has access to DBMS_LOCK, but test1admin is not authorized to grant privileges on DBMS_LOCK to additional users. You need to connect as SYS to grant privileges on DBMS_LOCK to additional users.

Connect to the CDB root as SYS and switch to your pluggable database:

sqlplus / as sysdba
alter session set container = test1;

Or connect to the PDB directly:

sqlplus sys@test1 as sysdba

And issue the grant:

grant execute on dbms_lock to test1;

If you want test1admin to be able to grant privileges to DBMS_LOCK to others, then as SYS, issue the below:

grant execute on dbms_lock to test1admin with grant option;