| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Describe privilege on procedures & packages
Here we go:
http://www.geocities.com/dba_assist/scripts/show_procedure.html
HTH
GovindanK
<-----Original Message----->
From: Jamadagni, Rajendra;Jamadagni, Rajendra
Sent: 10/1/2003 1:25:33 PM
To: ORACLE-L_at_fatcity.com
all_arguments
Raj
-----Original Message----- From: Govindan K [mailto:gkatteri_at_omanmail.com] Sent: Wednesday, October 01, 2003 4:00 PM To: Multiple recipients of list ORACLE-L Subject: Re: Describe privilege on procedures & packages 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 I am wondering where to get the details of O_AGENT_DET from?
HTH GovindanK <-----Original Message-----> From: Pete Finnigan
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 , Govindan K
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: ORACLE-L_at_fatcity.com > >(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 havenever
>to >grant a read-only privilege). > >Thanks! > >_________________________________________________________________ > >Author: Gary Jackson >INET: fred_fred_1_at_hotmail.com > >. > > >_______________________________________________________________ >Get Your 10MB account for FREE at http://mail.arabia.com ! >Access MILLIONS of JOBS NOW! >
-- Pete Finnigan email:pete_at_petefinnigan.com 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: oracle_list_at_peterfinnigan.demon.co.uk _______________________________________________________________ Get Your 10MB account for FREE at http://mail.arabia.com ! Access MILLIONS of JOBS NOW! <http://ads.arabia.com/?SHT=text_email_english> _______________________________________________________________ Get Your 10MB account for FREE at http://mail.arabia.com ! Access MILLIONS of JOBS NOW! <http://ads.arabia.com/?SHT=text_email_english> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Govindan K INET: gkatteri_at_omanmail.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Thu Oct 02 2003 - 14:04:36 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |