1. 登陆数据库主机
2. su – oracle
3. sqlplus / as sysdba
4. show parameter remote_login_passwordfile;
5. 限制sysdba远程登陆，下次启动生效
alter system set remote_login_passwordfile=none scope=spfile;
6. 执行密码复杂度函数脚本，脚本内容见最后附录
@your/path/multipwd.sql
7. 创建profile
CREATE or REPLACE PROFILE app_user2 LIMIT
FAILED_LOGIN_ATTEMPTS 6 --用户在登录尝试失败n次后被锁定
PASSWORD_LIFE_TIME 90 --用户口令的生命周期
PASSWORD_REUSE_TIME 60 --口令在多少天内不能重复使用
PASSWORD_REUSE_MAX 5 --在达到PASSWORD_REUSE_TIME指定时间后，要再次使用同一口令前必须改变的次数
PASSWORD_VERIFY_FUNCTION verify_function_11G_new --以指定的function定义用户密码复杂度
PASSWORD_LOCK_TIME 1/24 --超过最大登陆失败次数后，用户被锁定的天数
PASSWORD_GRACE_TIME 30; --用户口令使用时间超过其生命周期后，可以延续使用的天数，并且可延续时间内登录会有相应口令即将过期的提示
ALTER USER XX PROFILE app_user2;
8. 查看使用默认密码的用户（11G中新添视图）
select * from dba_users_with_defpwd order by 1
9. 修改用户密码
alter user *** identified by “***”;
10. 建立登录信息表
CREATE TABLE LOGIN_LOG
(
SESSION_ID NUMBER(8,0) NOT NULL,
LOGIN_ON_TIME DATE,
LOGIN_OFF_TIME DATE,
USER_IN_DB VARCHAR2(50),
MACHINE VARCHAR2(50),
IP_ADDRESS VARCHAR2(20),
RUN_PROGRAM VARCHAR2(50)
)
11. 创建登陆信息触发器
CREATE OR REPLACE TRIGGER SYS.LOGIN_ON_INFO
AFTER LOGON
ON DATABASE
BEGIN
INSERT INTO LOGIN_LOG(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program)
SELECT AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program
FROM v$session WHERE AUDSID=USERENV('SESSIONID');
END;
/
12. 创建登出信息触发器
CREATE OR REPLACE TRIGGER SYS.LOGIN_OFF_INFO
BEFORE LOGOFF
ON DATABASE
Begin
update login_log set login_off_time=sysdate where session_id=USERENV('SESSIONID');
exception when others then
null;
END;
/
13. 创建登陆失败信息表
CREATE TABLE USER_LOGIN_AUDIT
(
SESSION_ID NUMBER(10),
SESSION_USER VARCHAR2(30 BYTE),
HOST VARCHAR2(30 BYTE),
IP VARCHAR2(30 BYTE),
CLIENT VARCHAR2(50 BYTE),
OS_USER VARCHAR2(50 BYTE),
STATUS CHAR(1 BYTE),
LOGIN_TIME DATE
);
14. 创建触发器记录失败信息
CREATE OR REPLACE TRIGGER SYS.USER_LOGIN_DENIED_AUDIT
AFTER SERVERERROR
ON DATABASE
BEGIN
IF (ora_is_servererror (1017))
THEN
INSERT INTO user_login_audit
VALUES (SYS_CONTEXT ('USERENV', 'SESSIONID'), SYS_CONTEXT ('USERENV', 'SESSION_USER'), SYS_CONTEXT ('USERENV', 'HOST'), SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), SYS_CONTEXT ('USERENV', 'MODULE'), SYS_CONTEXT ('USERENV', 'OS_USER'), '9', SYSDATE);
END IF;
END SYS.USER_LOGIN_DENIED_AUDIT;
/
15. 设置监听器的启动和关闭密码
lsnrctl
change_password
save_config
16. 设置访问控制地址
vim $ORACLE_HOME/network/admin/sqlnet.ora
tcp.validnode_checking = yes
tcp.invited_nodes = (ip1,ip2…)
备注:Oracle 10的IP貌似不能写成网段形式的,得一个个加IP地址,不然会报错
17. lsnrctl reload
附录：
-- multipwd.sql
-- 修改自10G版本与11G版本
-- 1.将11G版本中密码规则只需要数字和字母更换为10G中的数字、字母和符号
-- 2.扩充符号类型，除\/“‘@5种以外的符号均可作为密码使用
-- 3.修改创建的function名称
-- 4.注释掉更新默认profile（default）的语句，即不更新default
Rem
Rem $Header: rdbms/admin/multipwd.sql /st_rdbms_11.2.0/1 2013/01/31 01:34:11 skayoor Exp $
Rem
Rem multipwd.sql
Rem
Rem Copyright (c) 2006, 2013, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem utlpwdmg.sql - script for Default Password Resource Limits
Rem
Rem DESCRIPTION
Rem This is a script for enabling the password management features
Rem by setting the default password resource limits.
Rem
Rem NOTES
Rem This file contains a function for minimum checking of password
Rem complexity. This is more of a sample function that the customer
Rem can use to develop the function for actual complexity checks that the
Rem customer wants to make on the new password.
Rem
Rem MODIFIED (MM/DD/YY)
Rem skayoor 01/17/13 - Backport skayoor_bug-14671375 from main
Rem asurpur 05/30/06 - fix - 5246666 beef up password complexity check
Rem nireland 08/31/00 - Improve check for username=password. #1390553
Rem nireland 06/28/00 - Fix null old password test. #1341892
Rem asurpur 04/17/97 - Fix for bug479763
Rem asurpur 12/12/96 - Changing the name of password_verify_function
Rem asurpur 05/30/96 - New script for default password management
Rem asurpur 05/30/96 - Created
Rem
-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is also provided.
-- This function makes the minimum complexity checks like
-- the minimum length of the password, password not same as the
-- username, etc. The user may enhance this function according to
-- the need.
-- This function must be created in SYS schema.
-- connect sys/<password> as sysdba before running the script
CREATE OR REPLACE FUNCTION verify_function_11G_new
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
db_name varchar2(40);
digitarray varchar2(20);
punctarray varchar2(27);
chararray varchar2(52);
i_char varchar2(10);
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='`~!#$%^&*()-_=+[]{}|:;,.<>?';
-- Check for the minimum length of the password
IF length(password) < 8 THEN
raise_application_error(-20001, 'Password length less than 8');
END IF;
-- Check if the password is same as the username or username(1-100)
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20002, 'Password same as or similar to user');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to user name ');
END IF;
END LOOP;
-- Check if the password is same as the username reversed
FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
raise_application_error(-20003, 'Password same as username reversed');
END IF;
-- Check if the password is the same as server name and or servername(1-100)
select name into db_name from sys.v$database;
if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
raise_application_error(-20004, 'Password same as or similar to server name');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar to server name ');
END IF;
END LOOP;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password is the same as oracle (1-100)
simple_password := 'oracle';
FOR i IN 1..100 LOOP
i_char := to_char(i);
if simple_password || i_char = NLS_LOWER(password) THEN
raise_application_error(-20007, 'Password too simple ');
END IF;
END LOOP;
-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
-- 3. Check for the punctuation
<<findpunct>>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
<<endsearch>>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);
differ := abs(differ);
IF differ < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3 THEN
raise_application_error(-20011, 'Password should differ from the \
old password by at least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/
GRANT EXECUTE ON verify_function_11G_new TO PUBLIC;
-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.
-- ALTER PROFILE DEFAULT LIMIT
-- PASSWORD_LIFE_TIME 180
-- PASSWORD_GRACE_TIME 7
-- PASSWORD_REUSE_TIME UNLIMITED
-- PASSWORD_REUSE_MAX UNLIMITED
-- FAILED_LOGIN_ATTEMPTS 10
-- PASSWORD_LOCK_TIME 1
-- PASSWORD_VERIFY_FUNCTION verify_function_11G;
Be First to Comment