第十五章： 口令安全和资源管理
1、profile： 策略文件，管理口令的安全和资源管理
2、profile管理：dba可以 建立自定义的profile，然后分配各用户，如果没有指定 ，用户会启用 default profile
09:07:15 SQL> select username ,profile from dba_users;
USERNAME PROFILE
------------------------------ ------------------------------
MGMT_VIEW DEFAULT
SYS DEFAULT
SYSTEM DEFAULT
DBSNMP MONITORING_PROFILE
SYSMAN DEFAULT
SCOTT DEFAULT
OUTLN DEFAULT
MDSYS DEFAULT
ORDSYS DEFAULT
EXFSYS DEFAULT
DMSYS DEFAULT
WMSYS DEFAULT
CTXSYS DEFAULT
ANONYMOUS DEFAULT
XDB DEFAULT
ORDPLUGINS DEFAULT
SI_INFORMTN_SCHEMA DEFAULT
USERNAME PROFILE
------------------------------ ------------------------------
OLAPSYS DEFAULT
TSMSYS DEFAULT
BI DEFAULT
PM DEFAULT
MDDATA DEFAULT
IX DEFAULT
SH DEFAULT
DIP DEFAULT
3、profile 管理口令的安全：
09:10:28 SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
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
4、建立新的profile
09:19:59 SQL> create profile pass_profile limit
09:24:35 2 FAILED_LOGIN_ATTEMPTS 3
09:24:42 3 PASSWORD_LIFE_TIME 10
09:24:48 4 PASSWORD_REUSE_TIME 2
09:24:52 5 PASSWORD_LOCK_TIME 1/1440
09:25:27 6 PASSWORD_GRACE_TIME 2;
-----将profile 应用到用户
Profile created.
09:26:18 SQL> create user tom identified by tom profile pass_profile default tablespace users;
User created.
09:26:46 SQL> select username,profile from dba_users
09:26:56 2 where username='TOM';
USERNAME PROFILE
------------------------------ ------------------------------
TOM PASS_PROFILE
09:27:44 SQL> select * from dba_profiles where profile='PASS_PROFILE';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
PASS_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
PASS_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
PASS_PROFILE CPU_PER_SESSION KERNEL DEFAULT
PASS_PROFILE CPU_PER_CALL KERNEL DEFAULT
PASS_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
PASS_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
PASS_PROFILE IDLE_TIME KERNEL DEFAULT
PASS_PROFILE CONNECT_TIME KERNEL DEFAULT
PASS_PROFILE PRIVATE_SGA KERNEL DEFAULT
PASS_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD 3
PASS_PROFILE PASSWORD_LIFE_TIME PASSWORD 10
PASS_PROFILE PASSWORD_REUSE_TIME PASSWORD 2
PASS_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
PASS_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PASS_PROFILE PASSWORD_LOCK_TIME PASSWORD .0006
PASS_PROFILE PASSWORD_GRACE_TIME PASSWORD 2
验证：
09:28:26 SQL> grant create session to tom;
Grant succeeded.
09:28:32 SQL> conn tom/t123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
09:28:37 SQL> conn tom/t1234
ERROR:
ORA-01017: invalid username/password; logon denied
09:28:40 SQL> conn tom/t1235
ERROR:
ORA-01017: invalid username/password; logon denied
09:28:44 SQL> conn tom/t1236
ERROR:
ORA-28000: the account is locked
09:28:46 SQL> conn /as sysdba
Connected.
09:29:07 SQL> select username ,account_status from dba_users
09:29:14 2 where username='TOM';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
TOM LOCKED(TIMED)
---建立口令复杂性要求
09:38:22 SQL> @?/rdbms/admin/utlpwd
---取消口令复杂性
09:41:21 SQL> alter profile pass_profile limit
09:41:45 2 PASSWORD_VERIFY_FUNCTION null;
Profile altered.
09:43:07 SQL> alter profile default limit
09:43:25 2 PASSWORD_VERIFY_FUNCTION null;
Profile altered.
---------删除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
resource manager （资源管理）（cpu 时间）
1、资源管理，启用参数：
09:44:54 SQL> show parameter resource
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
resource_manager_plan string
09:51:22 SQL> alter system set resource_limit=true;
System altered.
09:51:39 SQL> show parameter resource
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
resource_manager_plan string
1、资源管理分类：
1）基于会话
2）基于语句
09:56:24 SQL> create profile res_profile limit
09:56:26 2 SESSIONS_PER_USER 2;
Profile created.
验证：
09:56:39 SQL> alter user tom profile res_profile;
User altered.
09:56:57 SQL> conn tom/tom
Connected.
09:57:09 SQL> conn tom/tom
Connected.
09:57:17 SQL> conn tom/tom
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
Warning: You are no longer connected to ORACLE.
