Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: custom DD views to allow users to see source without needing

RE: custom DD views to allow users to see source without needing

From: <Jared.Still_at_radisys.com>
Date: Thu, 06 Jun 2002 12:05:20 -0800
Message-ID: <F001.00476984.20020606120520@fatcity.com> 





I once rewrote the necessary system views to accomodate SQL Navigator.



Creating the users own copies did not work at that time, I had to modify 
the


actual SYS copies of the necessary ALL_* views.



It was not fun, and tends to break when Oracle is upgraded.



Jared







"Miller, Jay" <JayMiller_at_TDWaterhouse.com>
Sent by: root_at_fatcity.com


06/05/2002 01:03 PM


Please respond to ORACLE-L

 

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: custom DD views to allow users to see source without needing






We have the same problem with SQL Navigator.



Any suggestions would be great.



Jay Miller



-----Original Message-----


Sent: Tuesday, June 04, 2002 7:08 PM


To: Multiple recipients of list ORACLE-L
needing exe rights




Hi, Jack & list,



We had the same problem here and we finally resolved it in a similar way.
We created 3 views as sys : all_objects_xx, all_arguments_xx and
all_source_xx,


synonyms for both views and granted select permissions to user.



But, we still have a problem. User needs to debug packages (step by step)
and it seems that when you use dbms_debug the views are not enough.
The only way to achieve this is by granting create any procedure to user.
We want to avoid grant such permission. Do you have faced the same problem
?


If yes, how do you resolve it ?



Best regards,


Mario.







Por favor, responda a ORACLE-L_at_fatcity.com



Enviado por:      root_at_fatcity.com



Destinatarios:     Multiple recipients of list ORACLE-L
       <ORACLE-L_at_fatcity.com>




CC:


Asunto:           custom DD views to allow users to see source without
       needing exe rights




Clasificación:




Good afternoon co-listers,



Recently we had a problem with TOAD and I thought I
would share our solution.



TOAD looks at the views ALL_ARGUMENTS and ALL_OBJECTS
to see procedural code. Unless a user has the ability
to execute a package/procedure/function, they cannot
see the source code through these views, and can't see
the source in TOAD.



This limitiation is hard-coded in the view structure.
Upon reflection, it occured to me that I could
recreate these views in the users' schema, customized
to remove the necessity of having execute priv to see
the code, and since Oracle looks local first during
object name resolution, it would probably use these
views instead of the data dictionary views.



This worked. The two views that I customized are below
- feel free to use.



jack silvey





ALL_ARGUMENTS:


select


u.name owner, /* OWNER */


nvl(a.procedure$,o.name) object_name, /*
OBJECT_NAME */



decode(a.procedure$,null,null, o.name)


package_name, /*PACKAGE_NAME */


o.obj# object_id, /* OBJECT_ID */


decode(a.overload#,0,null,a.overload#) overload,
/*OVERLOAD */


a.argument argument_name, /* ARGUMENT_NAME */
a.position# position, /* POSITION */
a.sequence# sequence, /* SEQUENCE */
a.level# data_level, /* DATA_LEVEL */




decode(a.type#,  /* DATA_TYPE */


0, null,


1, decode(a.charsetform, 2, 'NVARCHAR2',


'VARCHAR2'),



2, decode(a.scale, -127, 'FLOAT', 'NUMBER'),
3, 'NATIVE INTEGER',
8, 'LONG',
9, decode(a.charsetform, 2, 'NCHAR VARYING',




'VARCHAR'),



11, 'ROWID',
12, 'DATE',
23, 'RAW',
24, 'LONG RAW',
29, 'BINARY_INTEGER',
69, 'ROWID',
96, decode(a.charsetform, 2, 'NCHAR', 'CHAR'),
102, 'REF CURSOR',
104, 'UROWID',
105, 'MLSLABEL',
106, 'MLSLABEL',
110, 'REF',
111, 'REF',
112, decode(a.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, 'OBJECT',
122, 'TABLE',
123, 'VARRAY',
178, 'TIME',
179, 'TIME WITH TIME ZONE',
180, 'TIMESTAMP',
181, 'TIMESTAMP WITH TIME ZONE',
231, 'TIMESTAMP WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR TO MONTH',
183, 'INTERVAL DAY TO SECOND',
250, 'PL/SQL RECORD',




251, 'PL/SQL TABLE',



252, 'PL/SQL BOOLEAN',



'UNDEFINED') data_type,



default$ default_value, /* DEFAULT_VALUE */
deflength default_length, /* DEFAULT_LENGTH */
decode(in_out,null,'IN',1,'OUT',2,'IN/OUT','Undefi
ned') in_out, /* IN_OUT */


length data_length, /* DATA_LENGTH */


precision# data_precision, /* DATA_PRECISION */
scale data_scale, /* DATA_SCALE */


radix radix, /* RADIX */


decode(a.charsetform, 1, 'CHAR_CS',           /*
CHARACTER_SET_NAME */


2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(a.charsetid),
4, 'ARG:'||a.charsetid) char_cs,
a.type_owner type_owner, /* TYPE_OWNER */
a.type_name type_name, /* TYPE_NAME */
a.type_subname type_subname, /* TYPE_SUBNAME */


a.type_linkname type_link, /* TYPE_LINK */
a.pls_type pls_type /* PLS_TYPE */


from sys.obj$ o,sys.argument$ a,sys.user$ u
where o.obj# = a.obj#


and o.owner# = u.user#



ALL_OBJECTS:


select u.name owner,

o.name object_name,
o.subname subobject_name,
o.obj# object_id,
o.dataobj# data_object_id,




decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2,


'TABLE', 3, 'CLUSTER',




4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',



7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',


11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21,


'LOB',





22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',

28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA

RESOURCE',



32, 'INDEXTYPE', 33, 'OPERATOR',



34, 'TABLE SUBPARTITION', 35, 'INDEX



SUBPARTITION',


39, 'LOB PARTITION', 40, 'LOB SUBPARTITION',
43, 'DIMENSION',
44, 'CONTEXT', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA


DATA',



'UNDEFINED') object_type,



o.ctime created,


o.mtime last_ddl_time,


to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS')
timestamp,


decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID')
status,


decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N')
temporary,


decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N')
generated,


decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N')
secondary


from sys.obj$ o, sys.user$ u


where o.owner# = u.user#


and o.linkname is null


and (o.type# not in (1  /* INDEX - handled below
*/,


10 /* NON-EXISTENT */)



or


(o.type# = 1 and 1 = (select 1


from sys.ind$ i


where i.obj# = o.obj#

and i.type# in (1, 2, 3, 4, 6, 7, 9))))
and o.name != '_NEXT_OBJECT'
and o.name != '_default_auditing_options_'


union all


select u.name, l.name, NULL, to_number(null),
to_number(null),


'DATABASE LINK',




l.ctime, to_date(null), NULL, 'VALID','N','N', 'N'
from sys.link$ l, sys.user$ u


where l.owner# = u.user#







Do You Yahoo!?


Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
  INET: jack_silvey_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


http://www.telecom.com.ar


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mario J Gonzalez
  INET: mjgonzal_at_ta.telecom.com.ar

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Miller, Jay
  INET: JayMiller_at_TDWaterhouse.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Thu Jun 06 2002 - 15:05:20 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US