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

From: Miller, Jay <JayMiller_at_TDWaterhouse.com>
Date: Wed, 05 Jun 2002 12:03:31 -0800
Message-ID: <F001.00475855.20020605120331@fatcity.com>

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).

Received on Wed Jun 05 2002 - 15:03:31 CDT