My vendor is using SQLNAv5 and they need to be able to view the source code of their functions, procedures, triggers, etc. via SQLNav5.
I need to say that I usually work only with SQLPlus – so that I’m using DBMS_METADATA.GET_DDL and accessing DBA_SOURCE when I need it.
The application has a schema, say TEST. There is also a separate schema for the developers to use which has a Read-Only access to the TEST schema. Let’s say this schema is called DEVRO. Hence, the vendor will use DEVRO schema to read TEST’s source code.
Now,I am having difficulties on what to grant to their shema on just whatprivileges suffice this requirement. As of now, I’m stuck with givingthem SELECT ANY DICTIONARY.
Can anyone help me in listing the tables/views/procedures/packages I need to grant to my vendor’s schema?
Here’s what I have tried:
- I granted EXECUTE on DBMS_METADATA to schema DEVRO
2.I login to the DB using DEVRO via SQLNav5 and expand the “All Schemas”.I select the TEST schema and try to expand the Package node. I received the ORA-00942 error, so I know that DBMS_METADATA, DBA_OBJECTS, and DBA_SOURCE aren’t enough for me to be able to view TEST’s source code of its packages (and everything else).
I then granted SELECT ANY DICTIONARY and tried again. As expected, it worked – I could see TEST’s source code. But since I don’t want to do this unless I have no choice, I revoked this system privilege afterwards.
Next, I tried to experiment with cat and dict. What I did was to grant SELECT to all dict and cat tables manually to DEVRO. I just used a simple SELECT ‘GRANT SELECT ON ’ || table_name || ’ TO DEVRO;’ FROM cat|dict; statement to produce the script needed. I assumed that includes everything I needed.
I tried again, but alas, it still failed. Of course, regranting SELECT ANY DICTIONARY did the trick.
Then, I tried to create synonyms for DEVRO for all objects listed by dict and cat. I did this
out of pure curiosity and despair actually. I tried again viewing the source via SQLNav5 and it still failed. No luck again.
Oh, by the way, as long as I gave SELECT ANY DICTIONARY, I didn’t need to give EXECUTE privilege to DBMS_METADATA for DEVRO to be able to view TEST’s source code.
Any help is much appreciated.