Noob Oracle Security question

Question

I want to be able to do a code comparison between stored procedures in production and code in QA to visually verify what is about to be implemented.

Then, once implemented, I want to be able to compare production code to QA to verify that everything was deployed as expected.

To do this, I need read access to db objects in the production environment.

I assume that it is possible to set up oracle security such that people can be given READ ACCESS to SPs w/o also being access to update the code.

Please confirm.

Answer 1

To access the code of stored procedures, you need to select from ALL_SOURCE:-

SELECT owner, name, text
FROM all_source
WHERE owner = '<your schema name>'
ORDER BY owner, name, text, type, line;

If you have access to run something, you can see it in ALL_SOURCE. So you could login with the same username/password as the application and run the above select.

Answer 2

You could also use a tool like PL/SQL developer, which can perform this kind of comparison automatically and highlight the changes...