在测试环境学习oracle,出现用户密码过期的问题,更改密码提示验证未通过,因为为测试环境,为方便期间,不需要经常修改密码,在此发现profile的使用方法,记录如下。
SQL> SELECT * FROM dba_profiles ;
查看用户所属的profile,一般为DEFAULT
SQL>select * from dba_profiles where resource_type='PASSWORD'; SQL> alter user lh identified by lh; alter user lh identified by lh ORA-28003: password verification for the specified password failed ORA-20001: Password same as or similar to user |
取消密码管理:
SQL>alter profile DEFAULT limit unlimited;
如:
SQL>alter profile DEFAULT limit password_reuse_time unlimited;
停止密码检验函数:
SQL>alter profile DEFAULT limit password_verify_function null;
创建 用户 专用 profile:
CREATE PROFILE test LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL 3000 CONNECT_TIME 45 LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL 1000 PRIVATE_SGA 15K COMPOSITE_LIMIT 5000000; |
如需更改:
SQL>alter profile test limit PASSWORD_LIFE_TIME 60 ;
更改用户profile
SQL>alter user scott profile test;
****************************************************************
Oracle数据库用户密码过期处理
1、sqlplus 用户/密码@数据库服务 as sysdba;
2、select username,profile from dba_users;
3、select * from dba_profiles s where s.profile='default' and resource_name='password_life_time';
4、alter profile default limit password_life_time unlimited;
如果用户已经被锁定,则执行5,否则不用执行5;
5、alter user 用户 identified by 密码 account unlock;