Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
There are 56 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Pete Finnigan's Oracle security weblog


Home » Archives » October 2004 » Allowing a user read-only access to stored procedure source code

[Previous entry: "Writing to the alert log"] [Next entry: "Oracle applications auditing"]

Allowing a user read-only access to stored procedure source code

October 27th, 2004 by Pete


I found an interesting post to titled Read-only proc privledges on comp.databases.oracle.server this morning. The poster said:

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


His 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:



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>



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



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>



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 posters DBA got to and decided that he could not grant EXECUTE PRIVILEGE on procedures to this user.

OK, so a solution suggested in the same thread was for the DBA to write a procedure to give access to the source needed. Is this the ideal solution? - Maybe, without further details of the solution its hard to comment. A better solution would be to create a new view based on DBA_SOURCE or even SYS.SOURCE$ if necessary 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 poster 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 only is a better solution. If the list of procedures he 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 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.
October 2004
SMTWTFS
     12
3456789
10111213141516
17181920212223
24252627282930
31      

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Weblog Home
Weblog Archives


Home
Oracle Security Tools page
Oracle security papers
Oracle Security alerts

Web Development
SQL Server Security

RSS 1.0 FEED
RSS 2.0 FEED
Atom 0.3 FEED
Powered by gm-rss 2.0.0


Valid XHTML 1.0!