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.