Criar novo usuário e copiar permissões de usuário existente

 

Criar novo usuário e copiar permissões de usuário existente

agosto 17, 2012 por pidkiller

Essa semana fiz um script simples para criar um novo usuário e copiar todas as permissões de um outro usuário já existente no banco de dados.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
set linesize 150
set echo off
set feedback off
set verify off
set head off
 
define v_newusername = 'NEWUSER'
define v_newuserpass = 'P4ssW0rd'
define v_usertocopyfrom = 'OLDUSER'
 
set termout off
 
-- Create user, define default tablespace, temporary tablespace and profile
select '-- Create user, define default tablespace, temporary tablespace and profile' from dual
union all
select 'create user &v_newusername identified by &v_newuserpass' ||
' default tablespace ' || default_tablespace ||
' temporary tablespace '|| temporary_tablespace ||
' profile '|| profile ||
';'
from dba_users
where username = upper('&v_usertocopyfrom')
union all
-- Grant Roles to new user
select '-- Grant Roles to new user' from dual
union all
select 'grant '|| granted_role || ' to &v_newusername' ||
decode(admin_option, 'YES', ' with admin option') || ';'
from dba_role_privs
where grantee = upper('&v_usertocopyfrom')
union all
-- Grant System Privs...
select '-- Grant System Privs' from dual
union all
select 'grant '|| privilege || ' to &v_newusername' ||
decode(admin_option, 'YES', ' with admin option') || ';'
from dba_sys_privs
where grantee = upper('&v_usertocopyfrom')
union all
-- Grants on database objects
select '-- Grant on database objects' from dual
union all
select 'grant '|| privilege || ' on ' || owner || '.' || table_name ||' to &v_newusername' ||
decode(grantable, 'YES', ' with admin option') || ';'
from dba_tab_privs
where grantee = upper('&v_usertocopyfrom')
union all
-- Grant Column Privs...
select '-- Grant Column Privs' from dual
union all
select 'grant '|| privilege || '(' || column_name || ') on ' || owner || '.' || table_name ||
' to &v_newusername;'
from dba_col_privs
where grantee = upper('&v_usertocopyfrom')
union all
-- Set Default Role...
select '-- Set default role' from dual
union all
select 'alter user &v_newusername default role ' || granted_role ||';'
from dba_role_privs
where grantee = upper('&v_usertocopyfrom')
and default_role = 'YES'
union all
-- Set quotas to user
select '-- Set quotas to new user' from dual
union all
select 'alter user &v_newusername quota ' || max_bytes ||' on ' || tablespace_name || ';'
from dba_ts_quotas
where username = upper('&v_usertocopyfrom');
 
undefine v_newusername
undefine v_newuserpass
undefine v_usertocopyfrom
 
set head on
set verify on
set echo on
set termout on
set feedback on

Isto vai gerar um output como o abaixo.

– Create user, define default tablespace, temporary tablespace and profile
create user NEWUSER identified by P4ssW0rd default tablespace USERS temporary tablespace TEMP_USERS profile VMU_USER_PROFILE;
– Grant Roles to new user
grant RESOURCE to NEWUSER;
grant CONNECT to NEWUSER;
– Grant System Privs
grant UNLIMITED TABLESPACE to NEWUSER;
– Grant on database objects
– Grant Column Privs
– Set default role
alter user NEWUSER default role RESOURCE;

Basta, então, copiar este output e colar no sqlplus para criar o novo usuário NEWUSER com as mesmas permissões que OLDUSER.

Também tem a versão usando o comando spool. Mas nesse caso, o arquivo gerado pelo spool deverá ser executado no sqlplus com o @. Se quiser também pode habilitar o ACCEPT no lugar de DEFINE.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
set head off
set pages 0
set long 10000
set linesize 150
set echo off
set feedback off
set verify off
set trimspool on
 
spool /home/oracle/copy_olduser2newuser.sql
 
--Prompts the user for new username, new pass and existing user to copy from
 
--accept v_newusername CHAR PROMPT '--Enter new username: ';
--accept v_newuserpass CHAR PROMPT '--Enter new user password: ';
--accept v_usertocopyfrom CHAR PROMPT '--Enter existing username to copy from: ';
 
define v_newusername = 'HR2'
define v_newuserpass = 'HR2'
define v_usertocopyfrom = 'HR'
 
set termout off
 
-- Create user, define default tablespace, temporary tablespace and profile
select '-- Create user, define default tablespace, temporary tablespace and profile' from dual;
 
select 'create user &v_newusername identified by &v_newuserpass' ||
' default tablespace ' || default_tablespace ||
' temporary tablespace '|| temporary_tablespace ||
' profile '|| profile ||
' password expire;'
from dba_users
where username = upper('&v_usertocopyfrom');
 
-- Grant Roles to new user
select '-- Grant Roles to new user' from dual;
 
select 'grant '|| granted_role || ' to &v_newusername' ||
decode(admin_option, 'YES', ' with admin option') || ';'
from dba_role_privs
where grantee = upper('&v_usertocopyfrom');   
 
-- Grant System Privs...
select '-- Grant System Privs' from dual;
 
select 'grant '|| privilege || ' to &v_newusername' ||
decode(admin_option, 'YES', ' with admin option') || ';'
from dba_sys_privs
where grantee = upper('&v_usertocopyfrom');
 
-- Grants on database objects
select '-- Grant on database objects' from dual;
 
select 'grant '|| privilege || ' on ' || owner || '.' || table_name ||' to &v_newusername' ||
decode(grantable, 'YES', ' with admin option') || ';'
from dba_tab_privs
where grantee = upper('&v_usertocopyfrom');
 
-- Grant Column Privs...
select '-- Grant Column Privs' from dual;
 
select 'grant '|| privilege || '(' || column_name || ') on ' || owner || '.' || table_name ||
' to &v_newusername;'
from dba_col_privs
where grantee = upper('&v_usertocopyfrom');
 
-- Set Default Role...
select '-- Set default role' from dual;
 
select 'alter user &v_newusername default role ' || granted_role ||';'
from dba_role_privs
where grantee = upper('&v_usertocopyfrom')
and default_role = 'YES';
 
-- Set quotas to user
select '-- Set quotas to new user' from dual;
 
select 'alter user &v_newusername quota ' || max_bytes ||' on ' || tablespace_name || ';'
from dba_ts_quotas
where username = upper('&v_usertocopyfrom');
 
spool off
 
undefine v_newusername
undefine v_newuserpass
undefine v_usertocopyfrom
 
set head on
set verify on
set echo on
set termout on
set feedback on