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
|
# Nom du script : DDLAcces.ksh
# Date : 15 novembre 2005
# Auteur : Fabien Celaia
# Descriptif : Extrait les rôles d'une instance
# Syntaxe : DDLAcces.ksh -S SID -P MotDePasseSystem -f Fichiersql
# Modification : Auteur Date Description
# ------ -------- ---------------------------------
#!/bin/ksh
while getopts "S:P:f:h" opt
do
case $opt in
S) SID=$OPTARG;;
f) FILE=$OPTARG ;;
h) echo "Syntaxe : DDLAcces.ksh -S ORACLE -P MotDePasseSystem -f roles.sql" ; exit ;;
esac
done
$sqlplus system/${PWD}@${SID} > ${LOG}/$0.log << EOF
set linesize 150
set pagesize 0
set feedback off
spool ${FILE}
----------------------------------
-- Creation des roles utilisateurs
----------------------------------
select 'CREATE ROLE '|| role||' ;'
from dba_roles
where role not in
('CONNECT', 'RESOURCE', 'DBA', 'SELECT_CATALOG_ROLE', 'EXECUTE_CATALOG_ROLE', 'DELETE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE', 'RECOVERY_
CATALOG_OWNER', 'GATHER_SYSTEM_STATISTICS', 'LOGSTDBY_ADMINISTRATOR', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 'GLOBAL_AQ_USER_ROLE', 'SCHEDULER_ADMIN',
'HS_ADMIN_ROLE', 'CTXAPP', 'OEM_MONITOR', 'WM_ADMIN_ROLE', 'JAVAUSERPRIV', 'JAVAIDPRIV', 'JAVASYSPRIV', 'JAVADEBUGPRIV', 'EJBCLIENT', 'JAVA_ADMIN', 'JAVA
_DEPLOY', 'DMUSER_ROLE', 'DM_CATALOG_ROLE', 'XDBADMIN', 'AUTHENTICATEDUSER', 'OLAP_DBA', 'OLAP_USER', 'WKUSER', 'MGMT_USER ') ;
----------------------------------
--- Creation des utilisateurs
----------------------------------
select 'CREATE USER ' || username || ' PROFILE "' ||profile|| '" identified by "soleil" PASSWORD EXPIRE ACCOUNT UNLOCK ;'
from dba_users
where username not in ('MGMT_VIEW', 'SYSMAN', 'DBSNMP', 'SYSTEM', 'SYS', 'DIP', 'MDDATA', 'OLAPSYS', 'SI_INFORMTN_SCHEMA', 'ORDPLUGINS', 'WKPROXY', 'XDB'
, 'ANONYMOUS', 'CTXSYS', 'WKSYS', 'WMSYS', 'DMSYS', 'EXFSYS', 'ORDSYS', 'MDSYS', 'OUTLN') ;
-----------------------------------
--- Droits données aux droits
-----------------------------------
select 'GRANT '|| granted_role||' TO '|| grantee ||';'
from DBA_ROLE_PRIVS order by grantee ;
------------------------------------
--- Privileges systeme
-----------------------------------
select 'GRANT '|| privilege ||' TO '|| role ||
case when admin_option='YES' then ' WITH ADMIN OPTION ;' else ' ;' end
from ROLE_SYS_PRIVS ;
-----------------------------------
--- Privileges sur les tables
-----------------------------------
select 'GRANT '||privilege||
' ON ' || table_name ||
case when column_name is not null then '('|| column_name||')' end ||
' TO ' || role ||
case when grantable='YES' then ' WITH GRANT OPTION ;' else ' ;' END
from ROLE_TAB_PRIVS ;
-------------------------------------
--- Quotas sur les disques
-------------------------------------
select 'ALTER USER "'||username||'" QUOTA '|| case when max_bytes <0 then 'UNLIMITED' else cast(max_bytes as varchar(20)) end ||' ON "'||tablespace_name|
|'" ;
'
from dba_ts_quotas
spool off
exit
EOF |
