Why can't I grant exec on dbms_lock.sleep() OR create a procedure using it (but I can run it fine on its own)

Question

I am trying to write a small bit of PL/SQL that has a non-CPU burning sleep in it.

The following works in sqldeveloper

begin
  dbms_lock.sleep(5);
end;

BUT (as the same user), I can't do the following:

create or replace
procedure sleep(seconds in number)
is
begin
  dbms_lock.sleep(seconds);
end;

without the error "identifer "DBMS_LOCK" must be declared... Funny as I could run it without a procedure.

Just as strange, when I log in as a DBA, I can run the command

grant exec on dbms_lock to public;

and I get

ERROR at line 1:
ORA-00990: missing or invalid privilege

This is oracle version "Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production"

EXEC is not valid privilege name !!! – Sep 14 '12 at 14:00 — , Sep 14 '12 at 14:00

score 1 · Answer 1 · 2012-09-14T14:24:39.500

According to http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9013.htm

GRANT EXECUTE ON "object" TO PUBLIC;

To create a stand-alone procedure or function, or package specification or body, you must meet the following prerequisites:

You must have the CREATE PROCEDURE system privilege to create a procedure or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a procedure or package in another user's schema.

Attention: To create without errors, that is, to compile the procedure or package successfully, requires the following additional privileges: The owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code; the owner cannot have obtained required privileges through roles.

If the privileges of a procedure's or package's owner change, the procedure must be reauthenticated before it is executed. If a necessary privilege to a referenced object is revoked from the owner of the procedure (or package), the procedure cannot be executed.

That does not work - did you see the bottom half of the question - I had already tried that. — Richard Green, Sep 14 '12 at 09:29
Are you sure that EXEC is valid privilege? did you try EXECUTE instead of EXEC? — , Sep 14 '12 at 09:45

score 1 · Accepted Answer · answered Sep 14 '12 at 12:44

1

[richard@f1 ~]$ sqlplus /nolog 

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 14 13:33:18 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> connect sys@xe as sysdba

Enter password: ******

Connected.
SQL> grant execute on sys.dbms_lock to richard;

Grant succeeded.

answered Sep 14 '12 at 12:44

Richard Green

91
1
1
6

So you use my solution.What about accept my answer? – Sep 14 '12 at 14:01
As it wasn't the "grant execute..." that was the issue, but it was the sqlplus / nolog followed by the connect sys@xe as sysdba which was the correct way to authenticate to the server with the appropriate level of privileges to enable me to grant execute – Richard Green Sep 17 '12 at 09:54

Why can't I grant exec on dbms_lock.sleep() OR create a procedure using it (but I can run it fine on its own)

2 Answers2