Any alternative way of DBMS_METADATA.GET_DDL

Question

I am working on a application which is used to get ddl of an Oracle database object.

Using following query to get objects of the database

SELECT object_name, object_type  
FROM dba_objects 
where owner = '" + alternative_schema + "' 
order by object_type, object_name;

Using following query to get ddl of the db object

SELECT DBMS_METADATA.GET_DDL('TABLE','" + tableName + "','" + alternative_schema + "') 
FROM dual;

and It is working fine.

To execute above queries oracle user need following grants

GRANT SELECT_CATALOG_ROLE TO john;
GRANT SELECT ANY TABLE TO john;

Now due to some security issue, dba is not allowing to grant SELECT_CATALOG_ROLE and as my research says that with the SELECT_CATALOG_ROLE its not possible.

But someone suggest to do it by 'sys'.

So now I can list of all objects by following query

select * 
from sys.all_tables 
where owner ='mydb'

Its returning all the created object under the mydb schema.

But my next challenge is to get ddl

How can I get ddl without having SELECT_CATALOG_ROLE?

Is it possible by any way to execute DBMS_METADATA.GET_DDL without granting SELECT_CATALOG_ROLE?

Update: according to the link oracle Security Model

Nonprivileged users can see the metadata of only their own objects.

So owner does not need to have "SELECT_CATALOG_ROLE" permission to get_ddl()

and my user is readOnly type of user, I need to final solution to getDDL from readonly user (non-owner) account, which should not have SELECT_CATALOG_ROLE

Answer 1

First of all thanks to all for all suggestions. I got the solution for my scenario, according to the linkenter link description here

Nonprivileged users can see the metadata of only their own objects.

So owner does not need to have "SELECT_CATALOG_ROLE" permission to get_ddl(). and my requirement is to get ddl from the user which should not have "SELECT_CATALOG_ROLE" permission. So one solution is that if a function is created under the owner account which internally execute dbma_metadata.get_ddl() and return ddl string and if we grant select/execute permission of the new method to the user (Non-Owner) then she can get ddl without having "SELECT_CATALOG_ROLE" from out side the owner account.

create function get_ddl( i_owner in varchar2, i_name in varchar2, i_type in varchar2)
    return clob
is
begin
    dbms_output.put_line('Listing active session roles...');
    for r_role in (select role from sys.session_roles) loop
        dbms_output.put_line('Role : '||r_role.role);
    end loop;
    dbms_output.put_line('Roles listed');
    dbms_output.put_line('Userid is : '||sys_context('userenv', 'current_userid'));
    return dbms_metadata.get_ddl( schema => i_owner, name => i_name, object_type => i_type);
end;

Call this method from other user (non-owner). By this readonly user will have only access of getDDL() to get definition. But do not have other permission will be granted to readonly user.

Answer 2

The only scenario in which we need to run DBMS_METADATA.GET_DDL() is when we're working with a database whose schemas are not under proper source control. This is a bad situation and one which a DBA ought to want to improve.

So, you need to get the DBA on your side. This should be easy if the purpose of your request is to get the database into source control, that is, this is a one-off exercise and safe development practices are being put in place to ensure that all future changes to the database will be tracked under source control.

Suggested approach: work on a database which is not Production but has the same data structures as Production. This should be an easier sell than asking for privilege escalation in Production.

Ideally it should be already available (UAT, Pre-prod environment) but if you need to use DBMS_METADATA.GET_DDL() maybe you're working without such an environment. In which case you need the DBA to create a new database for you.

---

@AlexPoole makes a good suggestion. You can generate source files using a metadata export through Data Pump with the sqlfile option. Find out more.

Answer 3

Yes, it its possible to extract the DDL for an object from the data dictionary views, but it is nearly impossible to write code that is complete or correct or both.

Oracle's security concept has three basic roles:

1) The owner of the schema: You can do anything you want with your tables/views/objects without any permisssions, even execute DBMS_METADATA. You use the data dictionary views starting with USER_ like USER_TABLES, USER_TAB_COLUMNS etc.

2) An "application user" or somebody with a few privileges on other schema objects. You use the data dictionary views that start with ALL_ like ALL_TABLES, ALL_TAB_COLUMNS. Which other objects appear in your views, depend on many things, for instance select privs on tables. But frankly, nobody knows the full list (just ask in a job interview which Grants you need to see or change a materialized view in another schema)

3) A DBA. He/she can see and change all other objects in all schemas. You use the data dictionary views starting with DBA_ like DBA_TABLES, DBA_TAB_COLUMNS etc. Some newer packages run with a subset of DBA privileges like SELECT_CATALOG_ROLE in your example.

There is reason to believe that DBMS_METADATA was written for Oracle's export/import datapump product, which needs to see all objects and falls therefore in category 3)

So, you have three options to solve your task.

1) Go along route 1. Write a stored procedure in PL/SQL as the owner of the tables, use DBMS_METADATA to get the DDL and do what ever you need to do. This procedure can be granted to other users.

2) Go along route 2. Use the views ALL_ to extract a little bit of DDL. Be aware that there are 365 of those views in Oracle 11.2.

3) Go along route 3. Talk to your DBA, get them involved. Maybe they grant you the SELECT_CATALOG_ROLE on a dev database. Maybe they can contribute to the script / program and will execute it themselves.