Feeds:
Posts
Comentários

Archive for agosto \17\UTC 2012

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.

 

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.

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

Read Full Post »