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

From: Jack Silvey <jack_silvey_at_yahoo.com>
Date: Tue, 04 Jun 2002 13:50:08 -0800
Message-ID: <F001.004743CF.20020604135008@fatcity.com>

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

Received on Tue Jun 04 2002 - 16:50:08 CDT