Andre Spadini
André Spadini | MultiCloud | DBA

André Spadini | MultiCloud | DBA

Oracle: Script Export Owners and Password

Oracle: Script Export Owners and Password

Andre Spadini's photo
Andre Spadini

Published on Aug 23, 2021

1 min read

Subscribe to my newsletter and never miss my upcoming articles

Exportando usuários e senhas

SQL> 
spool user.sql
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) DDL FROM DBA_USERS
where username not in
('ANONYMOUS','CTXSYS','DBSNMP','HR','MDSYS',
'ODM','ODM_MTR','OE','OLAPSYS','ORDPLUGINS',
'ORDSYS','OUTLN','PERFSTAT','PM','QS','QS_ADM',
'QS_CB','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS',
'SCOTT','SH','SYS','SYSTEM','WKPROXY',
'WKSYS','WMSYS','XDB','OAS_PUBLIC', 'SQLNAV','TRACESRV',
'DIP','DMSYS','EXFSYS','MGMT_VIEW','MDDATA','SI_INFORMTN_SCHEMA',
'AURORA$ORB$UNAUTHENTICATED','AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN',
'SYSMAN','WK_TEST','OAS_PUBLIC', 'SQLNAV','TRACESVR','TSMSYS','JMEDEIROS',
'AUDSYS','APPQOSSYS','APEX_REST_PUBLIC_USER','C##DBAAS_BACKUP CASCADE',
'C##DBAAS_MONITOR','DBSFWUSER','DVF','DVSYS','FLOWS_FILES','GGSYS','GSMADMIN_INTERNAL',
'GSMCATUSER','GSMUSER','LBACSYS','OJVMSYS','ORACLE_OCM','ORDDATA','ORDS_METADATA',
'ORDS_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SYS$UMF','SYSBACKUP',
'SYSDG','SYSKM','SYSRAC','XS$NULL','SPATIAL_WFS_ADMIN_USR','APEX_PUBLIC_USER','APEX_040200','NBS');
spool off

Exportando Grants

SQL> 
spool grant.sql
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
select
'PROMPT Concedendo roles para '||username||'...'||
DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',username) linha
from
(select distinct u.username
from dba_users u join dba_role_privs r
on u.username = r.grantee
where username not in
('ANONYMOUS','CTXSYS','DBSNMP','HR','MDSYS',
'ODM','ODM_MTR','OE','OLAPSYS','ORDPLUGINS',
'ORDSYS','OUTLN','PERFSTAT','PM','QS','QS_ADM',
'QS_CB','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS',
'SCOTT','SH','SYS','SYSTEM','WKPROXY',
'WKSYS','WMSYS','XDB','OAS_PUBLIC', 'SQLNAV','TRACESRV',
'DIP','DMSYS','EXFSYS','MGMT_VIEW','MDDATA','SI_INFORMTN_SCHEMA',
'AURORA$ORB$UNAUTHENTICATED','AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN',
'SYSMAN','WK_TEST','OAS_PUBLIC', 'SQLNAV','TRACESVR','TSMSYS','JMEDEIROS',
'AUDSYS','APPQOSSYS','APEX_REST_PUBLIC_USER','C##DBAAS_BACKUP CASCADE',
'C##DBAAS_MONITOR','DBSFWUSER','DVF','DVSYS','FLOWS_FILES','GGSYS','GSMADMIN_INTERNAL',
'GSMCATUSER','GSMUSER','LBACSYS','OJVMSYS','ORACLE_OCM','ORDDATA','ORDS_METADATA',
'ORDS_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SYS$UMF','SYSBACKUP',
'SYSDG','SYSKM','SYSRAC','XS$NULL','SPATIAL_WFS_ADMIN_USR','APEX_PUBLIC_USER','APEX_040200','NBS'))
order by username;
spool off

Exportando Privilégios de System

SQL> 
spool system.sql
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
select 'PROMPT Concedendo privilegios de sistema para '||username||'...'||
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',username) linha
from
(select distinct u.username
from dba_users u join dba_sys_privs s
on u.username = s.grantee
where username not in
('ANONYMOUS','CTXSYS','DBSNMP','HR','MDSYS',
'ODM','ODM_MTR','OE','OLAPSYS','ORDPLUGINS',
'ORDSYS','OUTLN','PERFSTAT','PM','QS','QS_ADM',
'QS_CB','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS',
'SCOTT','SH','SYS','SYSTEM','WKPROXY',
'WKSYS','WMSYS','XDB','OAS_PUBLIC', 'SQLNAV','TRACESRV',
'DIP','DMSYS','EXFSYS','MGMT_VIEW','MDDATA','SI_INFORMTN_SCHEMA',
'AURORA$ORB$UNAUTHENTICATED','AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN',
'SYSMAN','WK_TEST','OAS_PUBLIC', 'SQLNAV','TRACESVR','TSMSYS','JMEDEIROS',
'AUDSYS','APPQOSSYS','APEX_REST_PUBLIC_USER','C##DBAAS_BACKUP CASCADE',
'C##DBAAS_MONITOR','DBSFWUSER','DVF','DVSYS','FLOWS_FILES','GGSYS','GSMADMIN_INTERNAL',
'GSMCATUSER','GSMUSER','LBACSYS','OJVMSYS','ORACLE_OCM','ORDDATA','ORDS_METADATA',
'ORDS_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SYS$UMF','SYSBACKUP',
'SYSDG','SYSKM','SYSRAC','XS$NULL'))
order by username;
spool off
 
Share this