Hi all.
I'm an old-school SQLServer DBA (don't throw any fruit please!) and
now I'm working on a project with Oracle.  I'm trying to use PL/SQL to
view table structures, stored proc text, and view data in tables.

Things are working fine accept that I'm unable to see any stored
procedures.  My DBA said that he couldn't create a login that just has
stored proc view privledges without also giving my stored proc execute
privledges. Which of course he doesn't want to do.

So is there no way to give a user access to view stored procs with
giving them execute rights?

TIA
mark

[ Post a follow-up to this message ]

"Mark Sisson" <mark@corporatedigital.com> wrote in message
news:88cc4eb9.0410261510.2775214e@posting.google.com...
| Hi all.
| I'm an old-school SQLServer DBA (don't throw any fruit please!) and
| now I'm working on a project with Oracle.  I'm trying to use PL/SQL to
| view table structures, stored proc text, and view data in tables.
|
| Things are working fine accept that I'm unable to see any stored
| procedures.  My DBA said that he couldn't create a login that just has
| stored proc view privledges without also giving my stored proc execute
| privledges. Which of course he doesn't want to do.
|
| So is there no way to give a user access to view stored procs with
| giving them execute rights?
|
| TIA
| mark

that's about the size of it... why would you want a user to see code that
they have no rights to execute? except as developer examples, which there
are other ways to get...

other than for reference, can you provide a business function reason for
allowing a user to read SP code?

++ mcs

[ Post a follow-up to this message ]

mark@corporatedigital.com (Mark Sisson) wrote in
news:88cc4eb9.0410261510.2775214e@posting.google.com:

> Hi all.
> I'm an old-school SQLServer DBA (don't throw any fruit please!) and
> now I'm working on a project with Oracle.  I'm trying to use PL/SQL to
> view table structures, stored proc text, and view data in tables.
>
> Things are working fine accept that I'm unable to see any stored
> procedures.  My DBA said that he couldn't create a login that just has
> stored proc view privledges without also giving my stored proc execute
> privledges. Which of course he doesn't want to do.
>
> So is there no way to give a user access to view stored procs with
> giving them execute rights?
>
> TIA
> mark

Stored procedures can be seen from DBA_SOURCE, IIRC.
The DBA should be able to write a SP which can read
and display the contents of this view & then GRANT
you execute ONLY for this single SP.

HTH & YMMV

[ Post a follow-up to this message ]

"brad.madison" <Brad_IANAL_Madison@hotmail.com> wrote in message
news:Xns958EC056FEBC6SunnySD@68.6.19.6...
| mark@corporatedigital.com (Mark Sisson) wrote in
| news:88cc4eb9.0410261510.2775214e@posting.google.com:
|
| > Hi all.
| > I'm an old-school SQLServer DBA (don't throw any fruit please!) and
| > now I'm working on a project with Oracle.  I'm trying to use PL/SQL to
| > view table structures, stored proc text, and view data in tables.
| >
| > Things are working fine accept that I'm unable to see any stored
| > procedures.  My DBA said that he couldn't create a login that just has
| > stored proc view privledges without also giving my stored proc execute
| > privledges. Which of course he doesn't want to do.
| >
| > So is there no way to give a user access to view stored procs with
| > giving them execute rights?
| >
| > TIA
| > mark
|
| Stored procedures can be seen from DBA_SOURCE, IIRC.
| The DBA should be able to write a SP which can read
| and display the contents of this view & then GRANT
| you execute ONLY for this single SP.
|
| HTH & YMMV

good suggestion -- although this would require granting privileges from the
SYS schema to the DBA's schema, which some DBAs would not be too fond of

if the DBA is comfortable with this type of approach, then simply granting
SELECT on the DBA_SOURCE view to the developer would work,

another approach would be to grant SELECT_CATALOG_ROLE to the
developer(s) -- more of a standard administration option, but it also
includes access to hundreds of other dictionary views


++ mcs

[ Post a follow-up to this message ]

Hi,

Your DBA is partly correct the only way to give access to the stored
procedure code is to grant select on the view DBA_SOURCE or the
underlying table SYS.SOURCE$ but doing so would give the user access to
all source code held in the database. This can be demonstrated as
follows:

<pre>

SQL> connect system/manager
Connected.
SQL> drop user sp cascade;

User dropped.

SQL> create user sp identified by sp;

User created.

SQL> grant create session to sp;

Grant succeeded.

SQL> grant select on sys.dba_source to sp;

Grant succeeded.

SQL>

</pre>

Now we can try and access the source of a package procedure for example
DBMS_OUTPUT:

<pre>

SQL> connect sp/sp@sans
Connected.
SQL> select text
2  from dba_source
3  where name='DBMS_OUTPUT'
4  and owner='SYS';

TEXT
------------------------------------------------------------------------
--------
package dbms_output as

-- DE-HEAD     <- tell SED where to cut when generating fixed package

------------
--  OVERVIEW
...
{output snipped}
...

168 rows selected.

SQL>

</pre>

OK, so we can get read only access to the source code of procedures by
this method. the only privileges i have granted to the read only user
are CREATE SESSION and SELECT ON DBA_SOURCE.

But what is wrong with this picture? Obviously this user can now see all
source stored in the database. This is not ideal unless you want to
allow a user read only access to all of the source code in the database.
This would be a big security risk though. A better solution would be to
do the same select as above but through the view ALL_SOURCE instead.
This view gives the user access to source code he owns and also to
source code of procedures he has been granted EXECUTE PRIVILEGES on.
This is where the your DBA got to and decided that he could not grant
EXECUTE PRIVILEGE on procedures to this user.

A better solution would be to create a new view based on DBA_SOURCE and
through this view expose the source code of the procedures that the
read-only user actually needs to see. I would guess that this is not as
complicated as it sounds. For instance the your read-only user probably
needs access to a set of application procedures owned by one or more
schema accounts. Creating a view based on DBA_SOURCE and restricting its
output to include certain schemas only or even certain procedures by
name only is a better solution. If the list of procedures your user
needs to see spans multiple schema accounts and does not include all
procedures for those accounts then either further restrict based on the
names of the procedures or even implement a look up table of procedures
names the user is allowed to see. Create the new view and optional look
up table in a secure schema that has access to DBA_SOURCE and then grant
select access on this view to the read-only user.

kind regards

Pete
--
Pete Finnigan (email:pete@petefinnigan.com)
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Book:Oracle security step-by-step Guide - see http://store.sans.org for deta
ils.

[ Post a follow-up to this message ]

Mark Sisson wrote:

> Hi all.
> I'm an old-school SQLServer DBA (don't throw any fruit please!) and
> now I'm working on a project with Oracle.  I'm trying to use PL/SQL to
> view table structures, stored proc text, and view data in tables.
>
> Things are working fine accept that I'm unable to see any stored
> procedures.  My DBA said that he couldn't create a login that just has
> stored proc view privledges without also giving my stored proc execute
> privledges. Which of course he doesn't want to do.
>
> So is there no way to give a user access to view stored procs with
> giving them execute rights?
>
> TIA
> mark

What do you mean by "see stored procedures"?

You can do this:
SQL> desc <procedure_name>

or this:

SQL> SELECT argument_name, data_type, in_out
FROM all_arguments
WHERE object_name = <procedure_name>;

and numerous other things ... but your request is quite ambiguous.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[ Post a follow-up to this message ]

Pete Finnigan wrote:

> Hi,
>
> Your DBA is partly correct the only way to give access to the stored
> procedure code is to grant select on the view DBA_SOURCE or the
> underlying table SYS.SOURCE$ but doing so would give the user access to
> all source code held in the database. This can be demonstrated as
> follows:
>
> <pre>
>
> SQL> connect system/manager
> Connected.
> SQL> drop user sp cascade;
>
> User dropped.
>
> SQL> create user sp identified by sp;
>
> User created.
>
> SQL> grant create session to sp;
>
> Grant succeeded.
>
> SQL> grant select on sys.dba_source to sp;
>
> Grant succeeded.
>
> SQL>
>
> </pre>
>
> Now we can try and access the source of a package procedure for example
> DBMS_OUTPUT:
>
> <pre>
>
> SQL> connect sp/sp@sans
> Connected.
> SQL> select text
>   2  from dba_source
>   3  where name='DBMS_OUTPUT'
>   4  and owner='SYS';
>
> TEXT
> ------------------------------------------------------------------------
> --------
> package dbms_output as
>
> -- DE-HEAD     <- tell SED where to cut when generating fixed package
>
>   ------------
>   --  OVERVIEW
> ...
> {output snipped}
> ...
>
> 168 rows selected.
>
> SQL>
>
> </pre>
>
> OK, so we can get read only access to the source code of procedures by
> this method. the only privileges i have granted to the read only user
> are CREATE SESSION and SELECT ON DBA_SOURCE.
>
> But what is wrong with this picture? Obviously this user can now see all
> source stored in the database. This is not ideal unless you want to
> allow a user read only access to all of the source code in the database.
> This would be a big security risk though. A better solution would be to
> do the same select as above but through the view ALL_SOURCE instead.
> This view gives the user access to source code he owns and also to
> source code of procedures he has been granted EXECUTE PRIVILEGES on.
> This is where the your DBA got to and decided that he could not grant
> EXECUTE PRIVILEGE on procedures to this user.
>
> A better solution would be to create a new view based on DBA_SOURCE and
> through this view expose the source code of the procedures that the
> read-only user actually needs to see. I would guess that this is not as
> complicated as it sounds. For instance the your read-only user probably
> needs access to a set of application procedures owned by one or more
> schema accounts. Creating a view based on DBA_SOURCE and restricting its
> output to include certain schemas only or even certain procedures by
> name only is a better solution. If the list of procedures your user
> needs to see spans multiple schema accounts and does not include all
> procedures for those accounts then either further restrict based on the
> names of the procedures or even implement a look up table of procedures
> names the user is allowed to see. Create the new view and optional look
> up table in a secure schema that has access to DBA_SOURCE and then grant
> select access on this view to the read-only user.
>
> kind regards
>
> Pete

But there is, as I pointed out, a work-around. Create a parameterized
view and use dbms_application_info to set an environment variable. One
could also, of course, use DBMS_RLS.

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[ Post a follow-up to this message ]