DDL for User Creation Roles and Privileges
Query to generate the DDL for User Creation, Roles, and Privileges
With the article “DDL for User Creation Roles and Privileges”, we provide a series of statements with which we can extract the creation code of a USER.
Additionally, it is possible to extract the roles, system privileges, object privileges, and the default tablespace quota of a specific USER.
During a data alignment phase, it is crucial to extract the user from the test environment, as the password may differ from the same SCHEMA present in the production environment. In light of this type of activity, in the test environment, we must save the DDL for creating the user that we need to import and subsequently (although we can also do without it since they are reported by the import), we can also save the ROLES and PRIVILEGES associated with the user.
Therefore, the operations we will need to perform are saving the SCHEMA with the DBMS_METADATA.GET_DDL package as indicated in the article below. Next, DROP the SCHEMA, still in the test environment, and finally recreate it with the previously saved DDL.
Statements to extract the creation code for User, Roles, and Privileges:
--DDL Utente - grant set head off set pages 1000 set long 9999999 undef user select dbms_metadata.get_ddl('USER',username) || '/' usercreate from dba_users where USERNAME = upper('&user') --GRANT Utente SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',upper('&&user')) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',upper('&&user')) FROM DUAL;SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',upper('&&user')) FROM DUAL;SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA',upper('&&user')) FROM DUAL; /
PTo view my guides on CRS Commands, click here!
At this LINK, instead, you’ll find the SQL queries.
Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin
0 Comments