SQL & PL/SQL

Hi,

We have been requested to give support staff the ability to see table triggers, stored procedures, etc only for specific schemas and they don't have access to the schema password. We can't give them SELECT_CATALOG_ROLE because they are not allowed to see all schemas. I wish there were a way to grant a version of SELECT_CATALOG_ROLE for only certain schemas......

I've played around with dbms_metadata.get_ddl with no luck (as per the documentation, but just had to try it anyway). I've even considered the script below, but it requires creating a view under the SYS schema and I can't figure out how to add triggers to the view.

Any ideas would be greatly appreciated!

Thanks,
Susan


accept 1 prompt "Enter Owner:"

create or replace view all_dev_source
(OWNER, NAME, TYPE, LINE, TEXT)
as
select u.name, o.name,
decode(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 13, 'TYPE', 14, 'TYPE BODY',
'UNDEFINED'),
s.line, s.source
from sys.obj$ o, sys.source$ s, sys.user$ u
where
u.name = upper('&&1') and
o.obj# = s.obj#
and o.owner# = u.user#
and o.type# in (7, 8, 9, 11, 13, 14)
and
(
o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */)
or
(
(
(
(o.type# = 7 or o.type# = 8 or o.type# = 9)
and
o.obj# in (select obj# from sys.objauth$
where grantee# in (select kzsrorol from x$kzsro)
and privilege# = 12 /* EXECUTE */)
)
or
exists
(
select null from sys.sysauth$
where grantee# in (select kzsrorol from x$kzsro)
and
(
(
/* procedure */
(o.type# = 7 or o.type# = 8 or o.type# = 9)
or
(
privilege# = -144 /* EXECUTE ANY PROCEDURE */
or
privilege# = -141 /* CREATE ANY PROCEDURE */
)
)
or
(
/* package body */
o.type# = 11 or
privilege# = -141 /* CREATE ANY PROCEDURE */
)
or
(
/* type */
o.type# = 13
or
(
privilege# = -184 /* EXECUTE ANY TYPE */
or
privilege# = -181 /* CREATE ANY TYPE */
)
)
or
(
/* type body */
o.type# = 14 and
privilege# = -181 /* CREATE ANY TYPE */
)
)
)
)
)
)
union
select u.name, o.name, 'JAVA SOURCE', s.joxftlno, s.joxftsrc
from sys.obj$ o, x$joxfs s, sys.user$ u
where
u.name = upper('&&1') and
o.obj# = s.joxftobn
and o.owner# = u.user#
and o.type# = 28
and
(
o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */)
or
(
(
(
o.obj# in (select obj# from sys.objauth$
where grantee# in (select kzsrorol from x$kzsro)
and privilege# = 12 /* EXECUTE */)
)
or
exists
(
select null from sys.sysauth$
where grantee# in (select kzsrorol from x$kzsro)
and
(
(
/* procedure */
(
privilege# = -144 /* EXECUTE ANY PROCEDURE */
or
privilege# = -141 /* CREATE ANY PROCEDURE */
)
)
)
)
)
)
)
/
comment on table all_dev_source is
'Current source on stored objects that user is allowed to create'
/
comment on column all_dev_source.OWNER is
'Owner of the object'
/
comment on column all_dev_source.NAME is
'Name of the object'
/
comment on column all_dev_source.TYPE is
'Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE BODY" or "JAVA SOURCE"'
/
comment on column all_dev_source.LINE is
'Line number of this line of source'
/
comment on column all_dev_source.TEXT is
'Source text'
/
grant select on all_dev_source to <username>
/