Oracle开启审计及密码复杂度

密码复杂度设置

1.查询是否应用了密码复杂度函数

SELECT profile,resource_type,resource_name,limit FROM dba_profiles WHERE resource_type='PASSWORD' AND profile='DEFAULT';

2.创建密码复杂函数

可去自己数据库此路径下打开sql文件进行复制执行内容

D:\app\px_pa\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlpwdmg.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 /
View Code

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

posted @ 2020-09-21 14:35  px_passion  阅读(1745)  评论(0)    收藏  举报