Oracle PL/SQL: Retrieve list of permissioned procedures for account

Question

Hello, I've researched here and elsewhere but haven't found an answer for the following.

I'd like to get a list of all procedures available to my application's Oracle account (AFAIK they're part of one package), and have tried the following command in sqlplus:

 SELECT * from user_procedures;

However this only returns one row/procedure, when in fact the app has probably 20+ procedures it calls (successfully) on a regular basis. I can just look through the source code and extract all the stored procedure names, but I'd like to use the above and see it working, and as a basis for further examination of the db to assist in debugging (instead of always needing to run the app or write test client code, for example).

Does the above statement only return procedures my account owns explicitly, or should it show anything the account has access to? [I'm not very familiar with Oracle's specific features.]

I've tried other variations; for example, referencing 'dba_procedures' results in a 'table or view does not exist error.'

Are all of these symptoms the result of limited permissions on my app's Oracle account (which I'm using to connect via sqlplus)?

[Background: Dysfunctional environment--direct access to the DBMS and its external owners is extremely limited, so I'd like to be able to increase my understanding of the db design and get the information I need without assistance.]

Answer 1

user_procedures is a system view that holds all the procedures owned by certain schema (user). Not the ones that the schema (user) is granted to execute.

You can try DBMS_METADATA.GET_GRANTED_DDL stored procedure. You might find something useful there. But I don't know what kind of privileges you need to run it within your application.

Hope it helps.

Answer 2

If the procedures are part of a package (i.e. you're calling them by PACKNAME.PROCNAME, then you have an all-or-nothing grant on the package, not individual procedures within the package.

Answer 3

You can see which stand-alone procedure you can execute with this:

select ao.object_type, ao.owner ||'.'|| ao.object_name
from all_objects ao, user_tab_privs utp
where ao.object_type = 'PROCEDURE'
and utp.owner = ao.owner
and utp.table_name = ao.object_name
and utp.privilege = 'EXECUTE';

But if they are in a package you can't directly see the procedure names, AFAIK, but you can see which package you can execute with this:

select ao.object_type, ao.owner ||'.'|| ao.object_name
from all_objects ao, user_tab_privs utp
where ao.object_type = 'PACKAGE'
and utp.owner = ao.owner
and utp.table_name = ao.object_name
and utp.privilege = 'EXECUTE';

And then you can desc[ribe] the package to see the individual procedures and functions within it. I guess that is probably held somewhere in the data dictionary but don't knwo where off-hand...