Oracle newsgroups and blogs are filled with suggestions, tips, techniques and scripts intended to help the DBA with his or her chores, and many of these offerings utilize 'standard' packages and procedures installed by Oracle at database creation. Of course some of these packages/procedures/functions aren't meant for the common, every-day user to utilize, and the privileges on those objects are limited to specific types of accounts. Some of these are even restricted to use by SYS as SYSDBA and no one else. There are many, though, that are suitable for any user to execute, provided that user has the requisite privileges. And, unfortunately, such privileges may not have been granted to the user desiring access; calling or attempting to describe such procedures/packages/functions then results in the following undesired output:
SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist
SQL>
And, from a PL/SQL block you can get the following unnerving message:
PLS-00201: identifier 'dbms_lock' must be declared
But, hey, you KNOW it's there, because all of these wonderful scripts can't be wrong. And they're not; the user account in use simply has not been granted execute privilege on that package. And the same rules apply here that I listed in my prior post:* the user has no execute privilege on the package/procedure/function
* a synonym is missing and the user is attempting to access the object by name
How to fix this glaring omission? Either grant execute on the desired object to the requesting user, or create a synonym to allow access by name. How can you tell which is required? If this:
SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist
SQL>
and this:
SQL> desc sys.dbms_lock
ERROR:
ORA-04043: object sys.dbms_lock does not exist
SQL>
are the end results then the user has no execute privilege on the package/procedure/function. If, however, access by name fails:
SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist
SQL>
but access by owner.name succeeds:
SQL> desc sys.dbms_lock
PROCEDURE ALLOCATE_UNIQUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCKNAME VARCHAR2 IN
LOCKHANDLE VARCHAR2 OUT
EXPIRATION_SECS NUMBER(38) IN DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID NUMBER(38) IN
LOCKMODE NUMBER(38) IN
TIMEOUT NUMBER IN DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCKHANDLE VARCHAR2 IN
LOCKMODE NUMBER(38) IN
TIMEOUT NUMBER IN DEFAULT
FUNCTION RELEASE RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID NUMBER(38) IN
FUNCTION RELEASE RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCKHANDLE VARCHAR2 IN
FUNCTION REQUEST RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ID NUMBER(38) IN
LOCKMODE NUMBER(38) IN DEFAULT
TIMEOUT NUMBER(38) IN DEFAULT
RELEASE_ON_COMMIT BOOLEAN IN DEFAULT
FUNCTION REQUEST RETURNS NUMBER(38)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOCKHANDLE VARCHAR2 IN
LOCKMODE NUMBER(38) IN DEFAULT
TIMEOUT NUMBER(38) IN DEFAULT
RELEASE_ON_COMMIT BOOLEAN IN DEFAULT
PROCEDURE SLEEP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SECONDS NUMBER IN
SQL>
then the issue is a missing synonym. Knowing the corrective action required (and, of course, taking that action) will allow the user to access the desired package/procedure/function.Knowing what packages/procedures/functions you CAN access is information which is fairly easy to obtain:
select owner, object_name
from all_objects
where object_type in ('PACKAGE','FUNCTION','PROCEDURE');
You'll get a list (possibly a LONG list) of packages, procedures and functions (and the assiciated owners) which you're allowed to execute:
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS STANDARD
SYS DBMS_STANDARD
SYS DBMS_REGISTRY
SYS DBMS_REGISTRY_SERVER
SYS XML_SCHEMA_NAME_PRESENT
SYS UTL_RAW
SYS PLITBLM
SYS SYS_STUB_FOR_PURITY_ANALYSIS
SYS PIDL
SYS DIANA
SYS DIUTIL
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS SUBPTXT2
SYS SUBPTXT
SYS DBMS_PICKLER
SYS DBMS_JAVA_TEST
SYS DBMS_SPACE_ADMIN
SYS DBMS_LOB
SYS UTL_SYS_COMPRESS
SYS UTL_TCP
SYS UTL_HTTP
SYS DBMS_TRANSACTION_INTERNAL_SYS
SYS DBMS_SQL
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS DBMS_SYS_SQL
SYS DBMS_OUTPUT
SYS DBMS_LOGSTDBY
SYS DBMS_SESSION
SYS DBMS_LOCK
SYS UTL_FILE
SYS DBMS_TYPES
SYS GETTVOID
SYS XMLSEQUENCEFROMXMLTYPE
SYS XQSEQUENCEFROMXMLTYPE
SYS XMLSEQUENCEFROMREFCURSOR
...
If the package/procedure/function is in that list, but you still can't access it by name you're simply missing a synonym. And, if it's not in that list you have no access to that object so you'll need to discuss that issue with your DBA.I'll state again in this post that not all Oracle users are destined to access or use all of the installed packages/procedures/functions Oracle supplies. There may be very good reasons in your organization for not having access to a specific package, procedure or function, so don't be surprised if your request is met with a glorious
"Nope, sorry, can't do that."
Security is the watchword of late, and some organizations may frown upon just any user having privilege to execute certain code, because granting such access may open security holes in the database. Pete Finnigan has an excellent website listing the security issues with Oracle releases; it's worth the time to peruse his site to get a feel for what could disrupt an Oracle installation and give you a 'heads up' on why, possibly, you can't use a certain package or procedure.
It never hurts to ask. Just don't be surprised if the answer is "No" because there is probably a very good reason for that response.
1 comments:
hey..Great info.
Thanks a lot for a very useful post. it helped me a lot.
--Harsh--
Post a Comment