Blog

Step 1: Login to Oracle Database:

Connect to Oracle Database To find the DDL of a user,
you need to connect to an Oracle Database using SQL*Plus or any other tool that supports SQL queries.

Step 2:Find the reference username:

Once you’re connected to the database, identify the user for which you want to find the DDL.

select username from dba_users where username=’USER1′;

Example :

select username from dba_users where username=’JOHN’;

Step 3: Find the DDL of the user:

Once you identify the username, use the syntax as stated below and find the DDL of the user.

Syntax :

set head off
set pagesize 0
set long 999999
set echo off

SELECT DBMS_METADATA.GET_DDL(‘USER’,upper(‘JOHN’)) || ‘/’ DDL
FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,upper(‘JOHN’)) || ‘/’ DDL
FROM DUAL
where exists (select ‘x’ from dba_role_privs drp where drp.grantee=upper(‘JOHN’))
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,upper(‘JOHN’)) || ‘/’ DDL
FROM DUAL
where exists (select ‘x’ from dba_sys_privs drp where drp.grantee=upper(‘JOHN’))
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,upper(‘JOHN’)) || ‘/’ DDL
FROM DUAL
where exists(select ‘x’ from dba_tab_privs drp where drp.grantee=upper(‘JOHN’));

Replace username ‘JOHN’ with the name of the user for which you want to find the DDL and the output will come like below :

There are also separately system and object grants are there which we can get for the user.

To get DDL of the system grants privileges to user:

Syntax:
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,'<username>’) from dual;

Example:

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’JOHN’) from dual;

To get DDL of the object grants privileges to user:

Syntax:
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,'<username>’) from dual;

Example :
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’JOHN’) from dual;

To get DDL of the role granted to user in Oracle Database:

Syntax:
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,'<username>’) from dual;

Example:
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’JOHN’) from dual;

Step 4: Analyze the DDL
The SQL query will return the DDL of the specified user as a single text. The DDL will contain all the information about the user, including its name, what kind of privileges it has, the system and the object grants. If you want to modify the user, you can modify the DDL accordingly and execute it using SQL commands.

So here are the steps that you can find the DDL of a user in Oracle database. Hope this helps!!

Reference Article :

https://dbapostmortem.com/ddl-of-a-tablespace-in-oracle-database/