DDL of users

In this article, we going to discuss how we will get the ddl of users and roles. We can get the ddl of users and roles using toad also, but in this article, we are going to get metadata manually.

Generally, DBA’s need to check the following details for a user, listed below.

• SYSTEM GRANT
• ROLE GRANT
• OBJECTS GRANT

How to get user metadata?

You can use the following command to get user metadata.

SQL>select dbms_metadata.get_ddl('USER','USER_NAME')  from dual;

How to check system grant metadata?

Use the following command to get a system grant.

SQL>select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','USER_NAME') from dual;

Check roles grant DDL

The following command will be used to check role grant DDL.

SQL>select dbms_metadata.get_granted_ddl('ROLE_GRANT','USERS_NAME') from dual;

Check object grant DDL

Use the below command.

SQL>select dbms_metadata.get_granted_ddl('OBJECT_GRANT','USER_NAME') from dual;

Read - 19c data guard new features

Connect with me on:

Instagram: https://www.instagram.com/digitalshripal
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube:https://www.youtube.com/ocptechnology