Package to include a sleep inside a procedure [message #663398] |
Thu, 01 June 2017 20:08 |
|
Nagesh1985
Messages: 10 Registered: April 2016
|
Junior Member |
|
|
Hi,
Users are requesting execute access to DBMS_LOCK package to incorporate in their procedure. As a DBA, I am not very comfortable providing that. What are the risks involved in giving DBMS_LOCK access to application users ? Is there any alternative that we can use instead ?
Oracle is suggesting a cover package for this (lock_100_to_200), but the syntax does not work.
Thanks
Nagesh
|
|
|
|
|
|
|
|
|
Re: Package to include a sleep inside a procedure [message #663417 is a reply to message #663408] |
Fri, 02 June 2017 08:14 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
it actually very easy to do. Make the following procedure in a privileged account like system. setup up a public synonym and since the procedure is running with definer rights it has full access to the dbms_lock without actually giving access to the full DBMS_LOCK
create or replace PROCEDURE Sleep (V_number_of_seconds IN NUMBER)
IS
BEGIN
DBMS_LOCK.Sleep (V_number_of_seconds);
RETURN;
END Sleep;
/
create public synonym sleep for system.sleep;
GRANT EXECUTE ON SLEEP TO PUBLIC;
[Updated on: Fri, 02 June 2017 09:31] Report message to a moderator
|
|
|
|
|