dbms_metadata makes it simple to clone a user. Here's a demonstration.
First, I create a small schema with some simple grants:
The first user (a_user) is needed to contain a few tables to test if grants to these tables will be cloned:
create user a_user identified by a_password;
The second user (user_to_be_cloned) is the user that I will actually duplicate:
create user user_to_be_cloned
identified by "Secret*49"
quota 10M on users;
I also need a role through which I will grant an object privilege:
create role a_role;
Creating the mentioned tables:
create table a_user.table_01 (id number);
create table a_user.table_02 (id number);
create table a_user.table_03 (id number);
Granting some privileges:
grant
create session,
create table
to
user_to_be_cloned;
grant select, insert on a_user.table_01 to user_to_be_cloned;
grant all on a_user.table_02 to a_role;
grant a_role to user_to_be_cloned;
Now, I want an sql script that contains the necessary statements to clone the user. The following script uses the SQL*Plus spool command to create the file (so it is designed to be run in SQL*Plus).
It also uses replace a lot to change USER_TO_BE_CLONED to CLONED_USER.
set heading off
set pages 0
set long 1000000
set termout off
exec dbms_metadata.set_transform_param(
dbms_metadata.session_transform,
'SQLTERMINATOR',
true);
spool create_cloned_user.sql
select replace(
dbms_metadata.get_ddl(
'USER',
'USER_TO_BE_CLONED'
),
'"USER_TO_BE_CLONED"',
'CLONED_USER'
) from dual;
select replace(
dbms_metadata.get_granted_ddl(
'SYSTEM_GRANT',
'USER_TO_BE_CLONED'
),
'"USER_TO_BE_CLONED"',
'CLONED_USER'
) from dual;
select replace(
dbms_metadata.get_granted_ddl(
'OBJECT_GRANT',
'USER_TO_BE_CLONED'
),
'"USER_TO_BE_CLONED"',
'CLONED_USER'
) from dual;
select replace(
dbms_metadata.get_granted_ddl(
'ROLE_GRANT',
'USER_TO_BE_CLONED'
),
'"USER_TO_BE_CLONED"',
'CLONED_USER'
) from dual;
select replace(
dbms_metadata.get_granted_ddl(
'TABLESPACE_QUOTA',
'USER_TO_BE_CLONED'
),
'"USER_TO_BE_CLONED"',
'CLONED_USER'
) from dual;
spool off
set termout on
The created script can now be used to create the cloned user. After logging on with CLONED_USER you can verify that the grants are cloned, too, by trying to select from a_user.table_01, a_user.table_02 and a_user.table_03.
Links
No comments:
Post a Comment