Oracle教程之管理安全和资源
使用Oracle Profile管理口令
[日期：2013-04-08]
来源：Linux社区
作者：Linux
[字体：大 中 小]
1、账户锁定
FAILED_LOGIN_ATTEMPTS：用于指定连续登录的最大失败次数。
PASSWORD_LOCK_TIME：用于指定账户被锁定的天数。
03:34:13 SQL> create profile lock_account limit
03:36:51 2 failed_login_attempts 3
03:37:07 3 password_lock_time 10;
Profile created.
02:56:45 SQL> select profile ,resource_name ,limit from dba_profiles
02:57:20 2 where profile='LOCK_ACCOUNT';
PROFILE RESOURCE_NAME LIMIT
------------------------------ -------------------------------- ----------------------------------------
LOCK_ACCOUNT COMPOSITE_LIMIT DEFAULT
LOCK_ACCOUNT SESSIONS_PER_USER DEFAULT
LOCK_ACCOUNT CPU_PER_SESSION DEFAULT
LOCK_ACCOUNT CPU_PER_CALL DEFAULT
LOCK_ACCOUNT LOGICAL_READS_PER_SESSION DEFAULT
LOCK_ACCOUNT LOGICAL_READS_PER_CALL DEFAULT
LOCK_ACCOUNT IDLE_TIME DEFAULT
LOCK_ACCOUNT CONNECT_TIME DEFAULT
LOCK_ACCOUNT PRIVATE_SGA DEFAULT
LOCK_ACCOUNT FAILED_LOGIN_ATTEMPTS 3
LOCK_ACCOUNT PASSWORD_LIFE_TIME DEFAULT
LOCK_ACCOUNT PASSWORD_REUSE_TIME DEFAULT
LOCK_ACCOUNT PASSWORD_REUSE_MAX DEFAULT
LOCK_ACCOUNT PASSWORD_VERIFY_FUNCTION DEFAULT
LOCK_ACCOUNT PASSWORD_LOCK_TIME .0006
LOCK_ACCOUNT PASSWORD_GRACE_TIME DEFAULT
16 rows selected.
03:38:02 SQL> create user test identified by test;
User created.
03:38:11 SQL> alter user test profile lock_account;
User altered
02:59:09 SQL> SELECT USERNAME,PROFILE FROM DBA_USERS
02:59:18 2 WHERE USERNAME='TEST';
USERNAME PROFILE
------------------------------ ------------------------------
TEST LOCK_ACCOUNT
03:38:31 SQL> select username,account_status from dba_users
03:39:23 2 where username='TEST';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
TEST OPEN
03:40:00 SQL> CONN test/123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to Oracle.
03:40:03 SQL> CONN test/123
ERROR:
ORA-01017: invalid username/password; logon denied
03:40:06 SQL> CONN test/123
ERROR:
ORA-01017: invalid username/password; logon denied
03:40:07 SQL> CONN test/123
ERROR:
ORA-28000: the account is locked
03:40:23 SQL> conn /as sysdba
Connected.
03:40:32 SQL> select username,account_status from dba_users
03:40:34 2 where username='TEST';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
TEST LOCKED(TIMED)
test账号3次密码输入错误被锁，10天后oracle自动解锁
手工解锁：
03:41:55 SQL> alter user test account unlock;
User altered.
03:41:57 SQL> select username,account_status from dba_users
03:41:59 2 where username='TEST';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
TEST OPEN
2、口令的有效期和终止期
PASSWORD_LIFE_TIME：用于指定口令有效期(单位：天)。
PASSWORD_GRACE_TIME：用于指定口令宽限期(单位：天)。
03:42:01 SQL> create profile password_life_time limit
03:45:09 2 password_life_time 10
03:45:19 3 password_grace_time 2;
Profile created
03:45:32 SQL> alter user test profile password_life_time;
User altered.
03:47:24 SQL> grant connect ,create session to test;
Grant succeeded.
03:47:26 SQL> conn test/test;
当建立了password_life_time，并将该PROFILE分配给用户DEVEP后，如果用户DEVEP在10天之内没有改变口令，那么在第10天登录时，会显示如下警告信息：
Sqlplus devep/admin@test
ERROR:
ORA-28002:the password will expire within 2 days
Connected.
如果第10天没有改变口令，那么在第11天、第12天登录时，仍然会显示类似的警告信息。如果第12仍然没有改变口令，那么当第13天登录时，oracle会强制你改变口令，否则不允许登录，并显示如下信息：
Sqlplus devep/admin@test
ERROR:
ORA-28001:the password has expried
Changing password for devep
New password：
3、口令历史
PASSWORD_REUSE_TIME：用于指定口令可重用时间(单位：天)。
PASSWORD_REUSE_MAX：用于指定在重用口令之前口令需要改变的次数。
03:51:24 SQL> conn /as sysdba
Connected.
03:51:32 SQL> create profile password_history limit
03:51:34 2 password_life_time 10 password_grace_time 2
03:51:38 3 password_reuse_time 10 password_reuse_max unlimited;
Profile created.
03:52:14 SQL> alter user test profile password_history;
User altered.
当将password_history 分配给DEVEP后，如果前12天没有修改用户口令，那么当第13天登录时，oracle会强制你改变口令。如果仍然使用过去的口令，则口令修改不能成功，并且显示如下错误信息：
Sqlplus devep/admin@test
ERROR:
ORA-28001:the password has expried
Changing password for devep
New password：
Retype new password：
ERROR:
ORA-28007：the password cannot be reused.
4、口令复杂性校验
(1)使用系统口令校验函数VERIFY_FUNCTION
安装oracle数据库产品时，oracle提供了sql脚本UTLPWDMG.SQL，该脚本用于建立系统口令校验函数VERIFY_FUNCTION，并且该口令校验函数实现了一下口令规则：
口令不能少于4个字符。
口令不能与用户名相同。
口令至少包含一个字符、一个数字和一个特殊符号($、_、#、! 等)。
03:57:13 SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
Function created.
Profile altered.
验证:
03:57:16 SQL> create user aa identified by aa;
create user aa identified by aa
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password same as or similar to user
03:58:12 SQL> create user aa identified by bb;
create user aa identified by bb
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 4
(2)建立自定义函数
Create or replace function password_function ….
函数建立过程略。
建立了口令校验函数password_function 后，为了使用该口令校验函数，需要修改 password_verify_function 选项。示例如下：
04:12:57 SQL> alter profile default limit
04:13:10 2 password_verify_function password_function ;
(3)禁用口令校验函数
如果要禁用口令校验函数，可以将password_verify_function 选项设置为NULL，示例如下：
04:12:34 SQL> alter profile password_history limit
04:12:39 2 password_verify_function null;
Profile altered.
04:12:46 SQL> create user aa identified by aa;
create user aa identified by aa
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password same as or similar to user
04:12:57 SQL> alter profile default limit
04:13:10 2 password_verify_function null;
Profile altered.
04:13:14 SQL> create user aa identified by aa;
User created.
04:13:16 SQL> drop user aa cascade;
User dropped.
5、删除profile
09:43:30 SQL> drop profile pass_profile ;
drop profile pass_profile
*
ERROR at line 1:
ORA-02382: profile PASS_PROFILE has users assigned, cannot drop without CASCADE
09:43:48 SQL> drop profile pass_profile cascade;
Profile dropped.
09:43:56 SQL>
--user 的profile 变成default profile
1885
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
