profile使用者密碼管理

pwz1688發表於2014-04-24
密碼
--檢視系統所有profile檔案
SQL> col profile for a10
SQL> col resource_name for a30
SQL> col resource for a10
SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- ------------------------------ -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- ------------------------------ -------- ----------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
16 rows selected
一、profile相關引數詳解
1、對資料庫資源做限制
{ { SESSIONS_PER_USER 每個使用者名稱所允許的並行會話數
| CPU_PER_SESSION 一個會話一共可以使用的CPU時間，單位是百分之一秒
| CPU_PER_CALL 一次SQL呼叫(解析、執行和獲取)允許使用的CPU時間
| CONNECT_TIME 限制會話連線時間，單位是分鐘
| IDLE_TIME 允許空閒會話的時間，單位是分鐘
| LOGICAL_READS_PER_SESSION 限制會話對資料塊的讀取，單位是塊
| LOGICAL_READS_PER_CALL 限制SQL呼叫對資料塊的讀取，單位是塊
| COMPOSITE_LIMIT “組合打法”
} { integer | UNLIMITED | DEFAULT }
| PRIVATE_SGA 限制會話在SGA中Shared Pool中私有空間的分配 { size_clause | UNLIMITED | DEFAULT}
}
2、對密碼做限制
{ { FAILED_LOGIN_ATTEMPTS 帳戶被鎖定之前可以錯誤嘗試的次數
| PASSWORD_LIFE_TIME 密碼可以被使用的天數，單位是天，預設值180天
| PASSWORD_REUSE_TIME 密碼可重用的間隔時間(結合PASSWORD_REUSE_MAX)
| PASSWORD_REUSE_MAX 密碼的最大改變次數(結合PASSWORD_REUSE_TIME)
| PASSWORD_LOCK_TIME 超過錯誤嘗試次數後，使用者被鎖定的天數，預設1天
| PASSWORD_GRACE_TIME 當密碼過期之後還有多少天可以使用原密碼
} { expr | UNLIMITED | DEFAULT }
| PASSWORD_VERIFY_FUNCTION { function | NULL | DEFAULT }
}
二、建立、修改profile
SQL> create profile prod_prof limit
  2 FAILED_LOGIN_ATTEMPTS 5
  3 PASSWORD_LIFE_TIME 100
  4 PASSWORD_LOCK_TIME 1/24/60
  5 PASSWORD_GRACE_TIME 10;
Profile created.
SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- ------------------------------ -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- ------------------------------ -------- ----------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
PROD_PROF COMPOSITE_LIMIT KERNEL DEFAULT
PROD_PROF SESSIONS_PER_USER KERNEL DEFAULT
PROD_PROF CPU_PER_SESSION KERNEL DEFAULT
PROD_PROF CPU_PER_CALL KERNEL DEFAULT
PROD_PROF LOGICAL_READS_PER_SESSION KERNEL DEFAULT
PROD_PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT
PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- ------------------------------ -------- ----------------------------------------
PROD_PROF IDLE_TIME KERNEL DEFAULT
PROD_PROF CONNECT_TIME KERNEL DEFAULT
PROD_PROF PRIVATE_SGA KERNEL DEFAULT
PROD_PROF FAILED_LOGIN_ATTEMPTS PASSWORD 5
PROD_PROF PASSWORD_LIFE_TIME PASSWORD 100
PROD_PROF PASSWORD_REUSE_TIME PASSWORD DEFAULT
PROD_PROF PASSWORD_REUSE_MAX PASSWORD DEFAULT
PROD_PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PROD_PROF PASSWORD_LOCK_TIME PASSWORD .0006
PROD_PROF PASSWORD_GRACE_TIME PASSWORD 10
32 rows selected.
--修改profile配置資訊
SQL> alter profile PROD_PROF limit PASSWORD_REUSE_TIME 1/24/60;
Profile altered.
SQL> alter profile PROD_PROF limit PASSWORD_REUSE_MAX 3;
Profile altered.
三、修改使用者預設profile檔案
--檢視使用者profile資訊
SQL> select username,profile from dba_users;
USERNAME PROFILE
------------------------------ ------------------------------
SCOTT DEFAULT
OPS$ORACLE DEFAULT
HA DEFAULT
TOM DEFAULT
TSMSYS DEFAULT
DIP DEFAULT
DBSNMP DEFAULT
OUTLN DEFAULT
SYS DEFAULT
SYSTEM DEFAULT
10 rows selected.
由此可見，預設都是使用defult profile
--修改tom使用者的profile檔案
SQL> alter user tom profile prod_prof;
User altered.
--測試tom使用者使用新profile的效應（連輸錯五次密碼，看帳號是否被鎖）
SQL> conn tom/1
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn tom/2
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tom/3
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tom/4
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tom/5
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tom/tom
ERROR:
ORA-28000: the account is locked
--一分鐘後帳號解鎖，並提示密碼快過期（期限為10天）
SQL> conn tom/tom
ERROR:
ORA-28002: the password will expire within 10 days

--修改tom密碼，三次內且一分鐘內不可重用密碼設定
SQL> alter user tom identified by tom;
User altered.
SQL> alter user tom identified by tom;
alter user tom identified by tom
*
ERROR at line 1:
ORA-28007: the password cannot be reused
SQL> alter user tom identified by tom1;
User altered.
SQL> alter user tom identified by tom2;
User altered.
SQL> alter user tom identified by tom3;
User altered.
SQL> alter user tom identified by tom;
alter user tom identified by tom
*
ERROR at line 1:
ORA-28007: the password cannot be reused
注意：雖然PASSWORD_REUSE_MAX過了3次限制，但因PASSWORD_REUSE_TIME未達1分鐘，也會受此限制。
--一分鐘後，再試
SQL> alter user tom identified by tom;
User altered.
修改密碼為tom成功。
四、刪除profile
SQL> drop profile prod_prof;
drop profile prod_prof
*
ERROR at line 1:
ORA-02382: profile PROD_PROF has users assigned, cannot drop without CASCADE
注意：因為tom使用者使用了prod_prof，所以不能直接刪除，需採用級聯刪除方式，如下：
SQL> drop profile prod_prof cascade;
Profile dropped.
SQL> select username,profile from dba_users;
USERNAME PROFILE
------------------------------ ----------
SCOTT DEFAULT
OPS$ORACLE DEFAULT
HA DEFAULT
TOM DEFAULT
TSMSYS DEFAULT
DIP DEFAULT
DBSNMP DEFAULT
OUTLN DEFAULT
SYS DEFAULT
SYSTEM DEFAULT
10 rows selected.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1148946/，如需轉載，請註明出處，否則將追究法律責任。

相關文章