Let me SLEEP!
SELECT ... FOR UPDATE SKIP LOCKEDsyntax legal). So it's just fine that DBMS_LOCK is private to SYS. Except ...
... except that one of the sub-programs in the package is SLEEP(). And SLEEP() is highly useful. Most PL/SQL applications of any sophistication need the ability to pause processing for a short while, either a fixed time or perhaps polling for a specific event. So it is normal for PL/SQL applications to need access to DBMS_SLEEP.LOCK().
Commonly this access is granted at the package level, that is
grant execute on dbms_lock to joe_dev. Truth to be told, there's not much harm in that. The privilege is granted to a named account, and if somebody uses the access to implement a roll-your-own locking strategy which brings Production to its knees, well, the DBAs know who to look for.
But we can employ a schema instead. The chief virtue of a schema is managing rights on objects. So let's create a schema for mediating access to powerful SYS privileges:
create user sys_utils identified by &pw temporary tablespace temp / grant create procedure, create view, create type to sys_utils /
Note that SYS_UTILS does not get the
create sessionprivilege. Hence nobody can connect to the account, a sensible precaution for a user with potentially damaging privileges. Why bar connection in all databases and not just Production? The lessons of history tell us that developers will assume they can do in Production anything they can do in Development, and write their code accordingly.
Anyway, as well as granting privileges, the DBA user will need to build SYS_UTIL's objects on its behalf:
grant execute on dbms_lock to sys_utils / create or replace procedure sys_utils.sleep ( i_seconds in number) as begin dbms_lock.sleep(i_seconds); end sleep; / create public synonym sleep for sys_utils.sleep / grant execute on sys_utils.sleep to joe_dev /
I think it's a good idea to be proactive about creating an account like this; granting it some obviously useful privileges before developers ask for them, simply because some developers won't ask. The forums occasionally throw up extremely expensive PL/SQL loops whose sole purpose is to burn CPU cycles or wacky DBMS_JOB routines which run every second. These WTFs have their genesis in ignorance of, or lack of access to, DBMS_LOCK.SLEEP().