口令配置和资源配置文件一般分开创建
修改配置文件
ALTER PROFILE profile LIMIT
[FAILED_LOGIN_ATTEMPTS max_value]
[PASSWORD_LIFE_TIME max_value]
[ {PASSWORD_REUSE_TIME
|PASSWORD_REUSE_MAX} max_value]
[PASSWORD_LOCK_TIME max_value]
[PASSWORD_GRACE_TIME max_value]
[PASSWORD_VERIFY_FUNCTION
{function|NULL|DEFAULT} ]
若要将口令参数设置为不超过一天:
1小时:PASSWORD_LOCK_TIME = 1/24
10分钟:PASSWORD_LOCK_TIME = 10/1400
5分钟:PASSWORD_LOCK_TIME = 5/1440
删除配置文件
DROP PROFILE profile [CASCADE]
其中:
profile:是要删除的配置文件的名称
CASCADE:指定此选项可以删除当前分配给用户的配置文件。
查询配置文件
dba_users 查询用户所使用的配置文件
dba_profiles 查询所有的参数文件的基本信息
user_password_limits 查询用户设置的口令文件的每条参数的信息
user_resource_limits 查询设置的资源限制参数
实例
1.创建一个res_profile配置文件
SQL> create profileres_profile limitsessions_per_user4 connect_time60 idle_time20private_sga100K cpu_per_call100;
2.创建一个pwd_profile的配置文件
SQL> create profile pwd_profile limit failed_login_attempts 4 password_lock_time 10;
Profile created.
3.将概要文件分配给用户
SQL> create user user5 identified by user5 profile res_profile;
User created.
查询
SQL> select profile from dba_users where username='USER5';
PROFILE
------------------------------
RES_PROFILE
修改用户的概要文件
SQL> alter user user5 profile pwd_profile;
User altered.
SQL> select profile from dba_users where username='USER5';
PROFILE
------------------------------
PWD_PROFILE
4.修改概要文件,使用alter profile ...limit,执行该语句的用户必须拥有alter profile权限
SQL> alter profile pwd_profile limit password_life_time 10;
Profile altered.
5.查询概要文件pwd_profile内容
SQL> select * from dba_profiles where profile='PWD_PROFILE';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
PWD_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
PWD_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
PWD_PROFILE CPU_PER_SESSION KERNEL DEFAULT
PWD_PROFILE CPU_PER_CALL KERNEL DEFAULT
PWD_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
PWD_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
PWD_PROFILE IDLE_TIME KERNEL DEFAULT
PWD_PROFILE CONNECT_TIME KERNEL DEFAULT
PWD_PROFILE PRIVATE_SGA KERNEL DEFAULT
PWD_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD 4
PWD_PROFILE PASSWORD_LIFE_TIME PASSWORD 10
PWD_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
PWD_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
PWD_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PWD_PROFILE PASSWORD_LOCK_TIME PASSWORD 10
PWD_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
16 rows selected.
6.删除概要文件
SQL> drop profile pwd_profile;drop profile pwd_profile
*
ERROR at line 1:
ORA-02382: profile PWD_PROFILE has users assigned, cannot drop without CASCADE
SQL> drop profile pwd_profilecascade;
Profile dropped.
说明:如果删除的概要文件已经指定给用户,则必须在drop profile 中加入cascade
8.查询
查询口令文件为用户设置的策略信息
SQL> select * from user_password_limits;
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
查询资源配置文件为用户设置的策略信息
SQL> select * from user_resource_limits;
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED