创建和修改profile

 

1、查看密码策略

set pagesize 1000

set line 200

SELECT profile,resource_type,resource_name,limit FROM dba_profiles WHERE resource_type='PASSWORD'  AND profile='DEFAULT';

 

2、检查资源限制是否启动

show parameter resource_limit

 

3、启用资源限制

alter system set resource_limit = true;

 

4、检查密码复杂度函数是否启动

SQL> select owner,object_name from dba_objects where object_name like '%VERIFY_FUNCTION_11%';

 

no rows selected

 

5、启用密码复杂度函数

SQL> @?/rdbms/admin/utlpwdmg.sql

 

Function created.

 

 

Profile altered.

 

 

Function created.

 

 

6、先创建密码复杂度函数

CREATE PROFILE TEST_PROFILE LIMIT

  SESSIONS_PER_USER UNLIMITED

  CPU_PER_SESSION UNLIMITED

  CPU_PER_CALL UNLIMITED

  CONNECT_TIME UNLIMITED

  IDLE_TIME 10

  LOGICAL_READS_PER_SESSION UNLIMITED

  LOGICAL_READS_PER_CALL UNLIMITED

  COMPOSITE_LIMIT UNLIMITED

  PRIVATE_SGA UNLIMITED

  FAILED_LOGIN_ATTEMPTS 5

  PASSWORD_LIFE_TIME 180 

  PASSWORD_REUSE_TIME UNLIMITED

  PASSWORD_REUSE_MAX UNLIMITED

  PASSWORD_LOCK_TIME 1

  PASSWORD_GRACE_TIME 10

  PASSWORD_VERIFY_FUNCTION ;

 

7、检查相应用户的profile

SQL> select username,profile from dba_users where username= 'TEST1';

 

USERNAME                       PROFILE

------------------------------ ------------------------------

TEST1                               DEFAULT

 

8、更改用户的profile

SQL> alter user test1 profile TEST_PROFILE;

 

User altered.

 

 

9、create user monitor identified by "QAZ#wsx!123"  default tablespace users temporary tablespace temp;

 

create user work identified by "QAZ#wsx!123"  password expire ;

default tablespace users temporary tablespace temp;

 

10、修改默认的profile

ALTER PROFILE DEFAULT LIMIT

PASSWORD_LIFE_TIME UNLIMITED

PASSWORD_GRACE_TIME 3

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_REUSE_MAX  UNLIMITED

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME 1

PASSWORD_VERIFY_FUNCTION NULL;