Hi Pete
I see another restriction (as i choose to put it) with my code. In OUR setup, we use Packages
"Extensively" (front end being Java). We use RefCursors to send result set to Java.
When you describe such a package ORACLE describes the structure
of the Ref Cursor too which i could not get using user_arguments. If someone in the list has the time
and inclination extend my code it would be useful.
Here are some examples. (For Ref cursor as output)
PROCEDURE P_GET_AGENT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
I_AGENT_ID NUMBER IN
I_BUSINESS_NAME VARCHAR2 IN
I_TEL_NO  ! ; VARCHAR2 IN
O_AGENT_DET REF CURSOR OUT
RECORD OUT
AGENT_ID NUMBER(6) OUT
AGENT_NM &nb! sp; VARCHAR2(50) OUT
AGENT_PHONE_NO VARCHAR2(10) OUT
ADR_SYS_NO NUMBER(10) OUT
ADR_STREET VARCHAR2(50) OUT
ADR_LINE_2 VARCHAR2(50) OUT
&! nbsp; ADR_CITY VARCHAR2(30) OUT
ADR_COUNTRY VARCHAR2(20) OUT
ADR_ZIP VARCHAR2(5) OUT
ADR_ZIP_PLUS VARCHAR2(4) OUT
AG_REPORTING_PERIOD_FL VARCHAR2(1) &nb! sp; OUT
AG_PRS_CONTACT_NM VARCHAR2(75) OUT
O_ERR_CODE NUMBER OUT
O_ERR_MESG VARCHAR2 OUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
I_AGENT_ID NUMBER IN
I_BUSINESS_NAME VARCHAR2 IN
I_TEL_NO  ! ; VARCHAR2 IN
O_AGENT_DET REF CURSOR OUT
RECORD OUT
AGENT_ID NUMBER(6) OUT
AGENT_NM &nb! sp; VARCHAR2(50) OUT
AGENT_PHONE_NO VARCHAR2(10) OUT
ADR_SYS_NO NUMBER(10) OUT
ADR_STREET VARCHAR2(50) OUT
ADR_LINE_2 VARCHAR2(50) OUT
&! nbsp; ADR_CITY VARCHAR2(30) OUT
ADR_COUNTRY VARCHAR2(20) OUT
ADR_ZIP VARCHAR2(5) OUT
ADR_ZIP_PLUS VARCHAR2(4) OUT
AG_REPORTING_PERIOD_FL VARCHAR2(1) &nb! sp; OUT
AG_PRS_CONTACT_NM VARCHAR2(75) OUT
O_ERR_CODE NUMBER OUT
O_ERR_MESG VARCHAR2 OUT
I am wondering where to get the details of O_AGENT_DET from? (which dba_view).
HTH
GovindanK
<-----Original Message----->
<-----Original Message----->
From: Pete Finnigan Sent: 10/1/2003 4:02:29 AM To: [EMAIL PROTECTED] Subject: Re: Describe privilege on procedures & packages Hi Govindan Good thought!!, I was going to suggest the same idea, just to go and get the description of the package / procedure / function from the dictionary and then grant access to the dictionary views needed. One slight flaw with your code though, you have selected from user_% views but the OP wanted to be able to let another user describe *his* procedures and packages, you would need to use dba_% views as if the "other" person had not been granted access to the OP's procedures then they wouldn't be in ALL_% for him or in user_% views. kind regards Pete In article <[EMAIL PROTECTED]>writes >This was the closest i could get. > >set pagesize 60; >set linesize 180; >column position noprint; >column sequence noprint; >break on object_type skip 1; >break on package_name skip 1; >break on object_name skip 1; >column object_type format A15 wrap; >column package_name format A30 wrap; >column object_name format A30 wrap; >column argument_name format A30 wrap; >column in_out format A10 wrap; >column data_type format A15 wrap; >column default_value format A10 wrap; >column type_name format A10 wrap; >column type_subname format A10 wrap; >select > b.object_type > ,a.package_name > ,a.object_name > ,a.argument_name > ,a.position > ,a.sequence > ,a.in_out > ,a.data_type > ,a.default_value > ,a.t! ype_name > ,a.type_subname > from user_arguments a > ,user_objects b > where a.position > 0 > and b.object_id = a.object_id > order by > b.object_type > ,a.package_name > ,a.object_name > , a.position >/ > >Create a procedure which will dbms_output this and grant execute >priviliges on it. > ><-----Original Message-----> > > From: Gary Jackson >Sent: 9/30/2003 9:31:29 AM >To: [EMAIL PROTECTED] > >(Reposting from yesterday morning since I had no takers! :) > >Hello, >I wanted to give another user access to view my procedures & packages >(just >DESC capability), but it seems that the only way for him to be able to >DESC >them is for me to grant execute. Is this correct?? (I guess I have never >had this situation before, it just seems surprising if there i! s no way >to >grant a read-only privilege). > >Thanks! > >_________________________________________________________________ > >Author: Gary Jackson >INET: [EMAIL PROTECTED] > >. > > >_______________________________________________________________ >Get Your 10MB account for FREE at http://mail.arabia.com ! >Access MILLIONS of JOBS NOW! > -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan INET: [EMAIL PROTECTED] |
_______________________________________________________________
Get Your 10MB account for FREE at http://mail.arabia.com !
Access MILLIONS of JOBS NOW!