Re: Read-only proc privledges

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_at_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_at_x.washington.edu
(replace 'x' with 'u' to respond)