Shulou(Shulou.com)06/01 Report--

Generate scripts to create users set pagesize 0

set escape on

select 'create user 'U.username' identified '

DECODE(password,

NULL, 'EXTERNALLY',

' by values '''''password''''

)

chr(10)

'default tablespace 'default_tablespacechr(10)

'temporary tablespace 'temporary_Tablespacechr(10)

' profile 'profilechr(10)

'quota '

decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes)

' on 'default_tablespace

decode (account_status,'LOCKED', ' account lock',

'EXPIRED', ' password expire',

'EXPIRED & LOCKED', ' account lock password expire',

null)

';'

from dba_users U, dba_ts_quotas Q

-- Comment this clause out to include system & default users

where U.username not in ('SYS','SYSTEM',

'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',

'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',

'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',

'QS_CB','QS_CS','PERFSTAT')

and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)

;

set pagesize 100

set escape off

Script to generate object authorization statements

set verify off

set feedback off

set termout off

set pagesize 500

set heading off

set recsep off

set termout on

select 'Creating object grant script by user... ' from dual;

set termout off

create table g_temp (seq NUMBER, grantor_owner varchar2(20),

text VARCHAR2(800));

DECLARE

cursor grant_cursor is

SELECT ur$.name, uo$.name, o$.name, ue$.name,

m$.name, t$.sequence#,

decode(NVL(t$.option$,0), 1, ' WITH GRANT OPTION;',';')

FROM sys.objauth$ t$, sys.obj$ o$, sys.user$ ur$,

sys.table_privilege_map m$, sys.user$ ue$, sys.user$ uo$

WHERE o$.obj# = t$.obj# AND t$.privilege# = m$.privilege AND

t$.col# IS NULL AND t$.grantor# = ur$.user# AND

t$.grantee# = ue$.user# and

o$.owner#=uo$.user# and

t$.grantor# != 0

order by sequence#;

lv_grantor sys.user$.name%TYPE;

lv_owner sys.user$.name%TYPE;

lv_table_name sys.obj$.name%TYPE;

lv_grantee sys.user$.name%TYPE;

lv_privilege sys.table_privilege_map.name%TYPE;

lv_sequence sys.objauth$.sequence#%TYPE;

lv_option VARCHAR2(30);

lv_string VARCHAR2(800);

lv_first BOOLEAN;

procedure write_out(p_seq INTEGER, p_owner VARCHAR2, p_string VARCHAR2) isbegin insert into g_temp (seq, grantor_owner,text) values (lv_sequence, lv_grantor, lv_string);end;

BEGIN

OPEN grant_cursor;

LOOP

FETCH grant_cursor INTO lv_grantor,lv_owner,lv_table_name,lv_grantee,

lv_privilege,lv_sequence,lv_option;

EXIT WHEN grant_cursor%NOTFOUND;

lv_string := 'GRANT ' || lv_privilege || ' ON ' || lower(lv_owner) ||

'. ' ||

lower(lv_table_name) || ' TO ' || lower(lv_grantee) ||

lv_option;

write_out(lv_sequence, lv_grantor,lv_string);

END LOOP;

CLOSE grant_cursor;

END;

/

spool tfscsopv.lst

break on guser skip 1

col text format a60 word_wrap

select 'connect ' || grantor_owner || '/' guser, text

from g_temp

order by seq, grantor_owner

/

spool off

drop table g_temp;

Script to generate system authorization statements

set verify off

set feedback off

set termout off

set pagesize 0

set termout on

select 'Creating system privilege grant script... ' from dual;

set termout off

spool tfscsspv.sql

select 'GRANT ' || rpad(lower(privilege),30) || ' TO ' || lower(grantee) || decode(admin_option,'YES',' WITH ADMIN OPTION;',';')

from sys.dba_sys_privs

where grantee not in ('CONNECT','RESOURCE','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE')

order by grantee

/

spool off

Generate role-granting scripts

set verify off

set feedback off

set termout off

set pagesize 0

set termout on

select 'Creating role build script... ' from dual;

set termout off

spool tfscsrol.sql

select 'CREATE ROLE ' || lower(role) || ' NOT IDENTIFIED;'

from sys.dba_roles

where role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE','IMP_FULL_DATABASE')

and password_required='NO'

/

select 'CREATE ROLE ' || lower(role) || ' IDENTIFIED BY VALUES ' ||

'''' || password || '''' || ';'

from sys.dba_roles, sys.user$

where role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE','IMP_FULL_DATABASE')

and password_required='YES' and dba_roles.role=user$.name

and user$.type=0

/

select 'GRANT ' || lower(granted_role) || ' TO ' || lower(grantee) ||

' WITH ADMIN OPTION;'

from sys.dba_role_privs

where admin_option='YES'

and granted_role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE','IMP_FULL_DATABASE')

order by grantee

/

spool off

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.