Toad World® Forums

Required Privileges to View Objects' Source Code


#1

Peace all,

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:

  1. 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).

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

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

  3. I tried again, but alas, it still failed. Of course, regranting SELECT ANY DICTIONARY did the trick.

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

Thanks,
e.e


#2

Elros,

Wouldn’t it be better to post this question in an Oracle discussion group?

BTW, maybe the link below will help somewhat:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:52284726269554


#3

I may not understand the question completely, but we have source code we are responsible to maintain but can not edit (Directly in the database), in order to see the data in these areas we are granted select to various dba views and then we turn on in the View–>preferences — under General–>session – we check the “Enable using DBA dictionary views” option. Not sure if this is any help or not…


#4

Peace Radar,

I’ll try, but I’ve raised this to Metalink with no luck.
Basically, Oracle wouldn’t know how third party utilities work

Thanks,
e.e


#5

Peace Lozier,

That might be related to the necessity of my granting SELECT ANY DICTIONARY to the vendor’s schema.

So, without doing that, it means that you cannot view the source at all? Am I correct?

Thanks,
e.e


#6

I would say that is correct. As the other replys suggest, Oracle metalink may have another solution, but there is nothing else I can think of…