密码复杂度设置
1.查询是否应用了密码复杂度函数
SELECT profile,resource_type,resource_name,limit FROM dba_profiles WHERE resource_type='PASSWORD' AND profile='DEFAULT';
2.创建密码复杂函数
可去自己数据库此路径下打开sql文件进行复制执行内容
D:apppx_paproduct11.2.0dbhome_1RDBMSADMINutlpwdmg.sql
1 CREATE OR REPLACE FUNCTION verify_function_11G 2 (username varchar2, 3 password varchar2, 4 old_password varchar2) 5 RETURN boolean IS 6 n boolean; 7 m integer; 8 differ integer; 9 isdigit boolean; 10 ischar boolean; 11 ispunct boolean; 12 db_name varchar2(40); 13 digitarray varchar2(20); 14 punctarray varchar2(25); 15 chararray varchar2(52); 16 i_char varchar2(10); 17 simple_password varchar2(10); 18 reverse_user varchar2(32); 19 20 BEGIN 21 digitarray:= '0123456789'; 22 chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 23 24 -- Check for the minimum length of the password 25 IF length(password) < 8 THEN 26 raise_application_error(-20001, 'Password length less than 8'); 27 END IF; 28 29 30 -- Check if the password is same as the username or username(1-100) 31 IF NLS_LOWER(password) = NLS_LOWER(username) THEN 32 raise_application_error(-20002, 'Password same as or similar to user'); 33 END IF; 34 FOR i IN 1..100 LOOP 35 i_char := to_char(i); 36 if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN 37 raise_application_error(-20005, 'Password same as or similar to user name '); 38 END IF; 39 END LOOP; 40 41 -- Check if the password is same as the username reversed 42 43 FOR i in REVERSE 1..length(username) LOOP 44 reverse_user := reverse_user || substr(username, i, 1); 45 END LOOP; 46 IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN 47 raise_application_error(-20003, 'Password same as username reversed'); 48 END IF; 49 50 -- Check if the password is the same as server name and or servername(1-100) 51 select name into db_name from sys.v$database; 52 if NLS_LOWER(db_name) = NLS_LOWER(password) THEN 53 raise_application_error(-20004, 'Password same as or similar to server name'); 54 END IF; 55 FOR i IN 1..100 LOOP 56 i_char := to_char(i); 57 if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN 58 raise_application_error(-20005, 'Password same as or similar to server name '); 59 END IF; 60 END LOOP; 61 62 -- Check if the password is too simple. A dictionary of words may be 63 -- maintained and a check may be made so as not to allow the words 64 -- that are too simple for the password. 65 IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN 66 raise_application_error(-20006, 'Password too simple'); 67 END IF; 68 69 -- Check if the password is the same as oracle (1-100) 70 simple_password := 'oracle'; 71 FOR i IN 1..100 LOOP 72 i_char := to_char(i); 73 if simple_password || i_char = NLS_LOWER(password) THEN 74 raise_application_error(-20007, 'Password too simple '); 75 END IF; 76 END LOOP; 77 78 -- Check if the password contains at least one letter, one digit 79 -- 1. Check for the digit 80 isdigit:=FALSE; 81 m := length(password); 82 FOR i IN 1..10 LOOP 83 FOR j IN 1..m LOOP 84 IF substr(password,j,1) = substr(digitarray,i,1) THEN 85 isdigit:=TRUE; 86 GOTO findchar; 87 END IF; 88 END LOOP; 89 END LOOP; 90 91 IF isdigit = FALSE THEN 92 raise_application_error(-20008, 'Password must contain at least one digit, one character'); 93 END IF; 94 -- 2. Check for the character 95 <<findchar>> 96 ischar:=FALSE; 97 FOR i IN 1..length(chararray) LOOP 98 FOR j IN 1..m LOOP 99 IF substr(password,j,1) = substr(chararray,i,1) THEN 100 ischar:=TRUE; 101 GOTO endsearch; 102 END IF; 103 END LOOP; 104 END LOOP; 105 IF ischar = FALSE THEN 106 raise_application_error(-20009, 'Password must contain at least one 107 digit, and one character'); 108 END IF; 109 110 111 <<endsearch>> 112 -- Check if the password differs from the previous password by at least 113 -- 3 letters 114 IF old_password IS NOT NULL THEN 115 differ := length(old_password) - length(password); 116 117 differ := abs(differ); 118 IF differ < 3 THEN 119 IF length(password) < length(old_password) THEN 120 m := length(password); 121 ELSE 122 m := length(old_password); 123 END IF; 124 125 FOR i IN 1..m LOOP 126 IF substr(password,i,1) != substr(old_password,i,1) THEN 127 differ := differ + 1; 128 END IF; 129 END LOOP; 130 131 IF differ < 3 THEN 132 raise_application_error(-20011, 'Password should differ from the 133 old password by at least 3 characters'); 134 END IF; 135 END IF; 136 END IF; 137 -- Everything is fine; return TRUE ; 138 RETURN(TRUE); 139 END; 140 / 141 142 -- This script alters the default parameters for Password Management 143 -- This means that all the users on the system have Password Management 144 -- enabled and set to the following values unless another profile is 145 -- created with parameter values set to different value or UNLIMITED 146 -- is created and assigned to the user. 147 148 ALTER PROFILE DEFAULT LIMIT 149 PASSWORD_LIFE_TIME 180 150 PASSWORD_GRACE_TIME 7 151 PASSWORD_REUSE_TIME UNLIMITED 152 PASSWORD_REUSE_MAX UNLIMITED 153 FAILED_LOGIN_ATTEMPTS 10 154 PASSWORD_LOCK_TIME 1 155 PASSWORD_VERIFY_FUNCTION verify_function_11G; 156 157 158 159 -- Below is the older version of the script 160 161 -- This script sets the default password resource parameters 162 -- This script needs to be run to enable the password features. 163 -- However the default resource parameters can be changed based 164 -- on the need. 165 -- A default password complexity function is also provided. 166 -- This function makes the minimum complexity checks like 167 -- the minimum length of the password, password not same as the 168 -- username, etc. The user may enhance this function according to 169 -- the need. 170 -- This function must be created in SYS schema. 171 -- connect sys/<password> as sysdba before running the script 172 173 CREATE OR REPLACE FUNCTION verify_function 174 (username varchar2, 175 password varchar2, 176 old_password varchar2) 177 RETURN boolean IS 178 n boolean; 179 m integer; 180 differ integer; 181 isdigit boolean; 182 ischar boolean; 183 ispunct boolean; 184 digitarray varchar2(20); 185 punctarray varchar2(25); 186 chararray varchar2(52); 187 188 BEGIN 189 digitarray:= '0123456789'; 190 chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 191 punctarray:='!"#$%&()``*+,-/:;<=>?_'; 192 193 -- Check if the password is same as the username 194 IF NLS_LOWER(password) = NLS_LOWER(username) THEN 195 raise_application_error(-20001, 'Password same as or similar to user'); 196 END IF; 197 198 -- Check for the minimum length of the password 199 IF length(password) < 4 THEN 200 raise_application_error(-20002, 'Password length less than 4'); 201 END IF; 202 203 -- Check if the password is too simple. A dictionary of words may be 204 -- maintained and a check may be made so as not to allow the words 205 -- that are too simple for the password. 206 IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN 207 raise_application_error(-20002, 'Password too simple'); 208 END IF; 209 210 -- Check if the password contains at least one letter, one digit and one 211 -- punctuation mark. 212 -- 1. Check for the digit 213 isdigit:=FALSE; 214 m := length(password); 215 FOR i IN 1..10 LOOP 216 FOR j IN 1..m LOOP 217 IF substr(password,j,1) = substr(digitarray,i,1) THEN 218 isdigit:=TRUE; 219 GOTO findchar; 220 END IF; 221 END LOOP; 222 END LOOP; 223 IF isdigit = FALSE THEN 224 raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation'); 225 END IF; 226 -- 2. Check for the character 227 <<findchar>> 228 ischar:=FALSE; 229 FOR i IN 1..length(chararray) LOOP 230 FOR j IN 1..m LOOP 231 IF substr(password,j,1) = substr(chararray,i,1) THEN 232 ischar:=TRUE; 233 GOTO findpunct; 234 END IF; 235 END LOOP; 236 END LOOP; 237 IF ischar = FALSE THEN 238 raise_application_error(-20003, 'Password should contain at least one 239 digit, one character and one punctuation'); 240 END IF; 241 -- 3. Check for the punctuation 242 <<findpunct>> 243 ispunct:=FALSE; 244 FOR i IN 1..length(punctarray) LOOP 245 FOR j IN 1..m LOOP 246 IF substr(password,j,1) = substr(punctarray,i,1) THEN 247 ispunct:=TRUE; 248 GOTO endsearch; 249 END IF; 250 END LOOP; 251 END LOOP; 252 IF ispunct = FALSE THEN 253 raise_application_error(-20003, 'Password should contain at least one 254 digit, one character and one punctuation'); 255 END IF; 256 257 <<endsearch>> 258 -- Check if the password differs from the previous password by at least 259 -- 3 letters 260 IF old_password IS NOT NULL THEN 261 differ := length(old_password) - length(password); 262 263 IF abs(differ) < 3 THEN 264 IF length(password) < length(old_password) THEN 265 m := length(password); 266 ELSE 267 m := length(old_password); 268 END IF; 269 270 differ := abs(differ); 271 FOR i IN 1..m LOOP 272 IF substr(password,i,1) != substr(old_password,i,1) THEN 273 differ := differ + 1; 274 END IF; 275 END LOOP; 276 277 IF differ < 3 THEN 278 raise_application_error(-20004, 'Password should differ by at 279 least 3 characters'); 280 END IF; 281 END IF; 282 END IF; 283 -- Everything is fine; return TRUE ; 284 RETURN(TRUE); 285 END; 286 /
3. 修改profile应用新创建的函数
Alter profile default limit password_verify_function verify_function_11G;
控制台修改方法：
10小时不活动，自动断开连接
安全审计
查看审计功能是否开启
show parameter audit;
开启审计功能
alter system set audit_sys_operations=TRUE scope=spfile;
alter system set audit_trail=db,extended scope=spfile;
如果AUDIT_TRAIL参数设置为DB或DB, EXTENDED，审计结果存在AUD$，DBA_AUDIT_TRAIL表中
关闭审计功能
alter system set audit_trail = none scope=spfile;
查看审计结果
Select * from aud$ t WHERE t.sqltext IS NOT NULL order by t.sessionid DESC;
查询审计结果：
SELECT * FROM DBA_AUDIT_TRAIL WHERE SQL_TEXT IS NOT NULL AND to_char(EXTENDED_TIMESTAMP,'yyyy-mm-dd') LIKE '2020-06-23' ORDER BY username DESC;
设置审计内容：
在每次动作发生时都对其进行审计(by access)或者只审计一次(by session)。默认是by session。对gdlisnet用户进行审计
audit all by gdlisnet by access;
对用户的四种类型语句进行审计
audit select table, update table, insert table, delete table by gdlisnet by access;
对gdlisnet用户执行程序时进行审计
audit execute procedure by gdlisnet by access;
审计结果定期备份文件
复制入文本文档，改后缀名为bat文件，加入计算机任务
@echo off
Set OrclSid=gdlisnet
Set ExpUser=gdlisnet
Set ExpPass=gdlisnet123
Set FileDir=D:/backup
Set SysDate=%date:~0,4%-%date:~5,2%-%date:~8,2%.%time:~0,2%%time:~3,2%
@echo * * * * * * * * * * * * * * * * * * * *
@echo * * * * * * * * * * * * * * * * * * * *
@echo * * * * * ORACLE 数据库导出 * * * * * *
@echo * * * * * * * * * * * * * * * * * * * *
@echo * * * * * * * * * * * * * * * * * * * *
@echo 服务名 = %OrclSid%
@echo 用户名 = %ExpUser%
@echo 密 码 = %ExpPass%
@echo 目 录 = %FileDir%
@echo 时 间 = %SysDate%
exp %ExpUser%/%ExpPass%@%OrclSid% tables= DBA_AUDIT_TRAIL file=%FileDir%/%ExpUser%~%SysDate%.dmp
@echo 数据库导出完成
关闭远程管理
关闭远程管理
Alter system set remote_login_passwordfile=none scope=spfile;
查看状态
Show parameter REMOTE_LOGIN_PASSWORDFILE