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 [EMAIL PROTECTED]
Enviado por: [EMAIL PROTECTED]
Destinatarios: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
