【密码】Oracle用户密码系列

密码Oracle用户密码系列

1.1  BLOG文档结构图

【密码】Oracle用户密码系列 

1.2  前言部分

1.2.1  导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~

用户的9种状态含义(重点)

如何解锁账户

如何修改密码无效状态

如何获取密码的密文,如何利用密文修改用户的密码(重点)

如何查询失败的登陆次数

11g 密码大小写问题

⑦ 11g 密码延迟验证

密码复杂性校验

 

Tips

本文在itpubhttp://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和微信公众号(xiaomaimiaolhr有同步更新

文章中用到的所有代码相关软件相关资料及本文的pdf版本都请前往小麦苗的云盘下载小麦苗的云盘地址见:http://blog.itpub.net/26736162/viewspace-1624453/

若网页文章代码格式有错乱,下载pdf格式的文档来阅读

本篇BLOG,代码输出部分一般放在一行一列的表格中。

本文有错误或不完善的地方请大家多多指正,您的批评指正是我写作的最大动力。

 

1.2.2  本文简介

客户的一个账户密码过期了,但是客户设置了永不过期,问到我为什么。我当时觉得设置了永不过期那肯定是生效的,只是这个部分的内容忘得差不多了,当时还想到可能是resource_limit这个参数没有设置为TRUE的缘故,后来查了官方文档才知道并不是这个原因。于是下决心把这部分的内容系统学习一下,自己总结的内容分享给大家。

1.3  使用profile管理用户口令

Oracle用户的状态是由密码来决定的,而Oracle中的密码是由PROFILE来配置的。PROFILE是口令限制资源限制的命令集合当建立数据库Oracle会自动建立名称为DEFAULTPROFILE。当创建用户没有指定PROFILE选项Oracle就会将DEFAULT分配给用户。

通过如下的命令可以查出与密码相关的PROFILE的值:

 SELECT *

   FROM DBA_PROFILES D

  WHERE D.PROFILE = ‘DEFAULT’

    AND (D.RESOURCE_NAME LIKE ‘%PASSWORD%’ OR

        D.RESOURCE_NAME = ‘FAILED_LOGIN_ATTEMPTS’);

【密码】Oracle用户密码系列 

每个参数的含义如下所示:

l FAILED_LOGIN_ATTEMPTS  设定登录到Oracle数据库时可以失败的次数。一旦某用户尝试登录数据库的达到该值时,该用户的帐户就被锁定,只能由DBA能解锁。

l PASSWORD_LIFE_TIME  设定口令的有效时间(天数),一旦超过这一时间,必须重新设口令。缺省为UNLIMITED

l PASSWORD_REUSE_TIME  许多系统不许用户重新启用过去用过的口令。该资源项设定了一个失效口令要经过多少天,用户才可以重新使用该口令,缺省为UNLIMITED

l PASSWORD_REUSE_MAX   重新启用一个先前用过的口令前必须对该口令进行重新设置的次数(重复用的次数)。

l PASSWORD_LOCK_TIME  设定帐户被锁定的天数(当登录失败达到FAILED_LOGIN_ATTEMPTS时)。

l PASSWORD_GRACE_TIME  设定在口令失效前,给予的重新设该口令的宽限天。当口令失效之后回,在登录时会出现警告信息显示该天数。如果没有在宽限天内修改口令,口令将失效。

l PASSWORD_VERITY_FUNCTION  该资源项允许调用一个PL/SQL来验证口令。Oracle已提供该应用的脚本,为$ORACLE_HOME/rdbms/admin/utlpwdmg.sql。但是,用户可以制定自己的验证脚本。该参数的设定就是PL/SQL函数的名称,缺省为NULL

1.3.1  修改密码为永不过期

 

SYS@lhrdb> select username, account_status,EXPIRY_DATE,profile from dba_users where username = ‘LHRSYS’;

 

USERNAM ACCOUNT_STATUS                  EXPIRY_DATE         PROFILE

——- ——————————- ——————- ——————————

LHRSYS  OPEN                            2016-12-07 15:20:36 TESTPROFILE

 

SYS@lhrdb> alter user lhrsys password expire;

 

User altered.

 

SYS@lhrdb> select username, account_status,EXPIRY_DATE,profile from dba_users where username = ‘LHRSYS’;

 

USERNAM ACCOUNT_STATUS                  EXPIRY_DATE         PROFILE

——- ——————————- ——————- ——————————

LHRSYS  EXPIRED                         2016-12-02 16:36:24 TESTPROFILE

 

SYS@lhrdb> ALTER PROFILE TESTPROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED;

 

Profile altered.

 

SYS@lhrdb> select username, account_status,EXPIRY_DATE,profile from dba_users where username = ‘LHRSYS’;

 

USERNAM ACCOUNT_STATUS                  EXPIRY_DATE         PROFILE

——- ——————————- ——————- ——————————

LHRSYS  EXPIRED                         2016-12-02 16:36:24 TESTPROFILE

 

SYS@lhrdb> SELECT NB.PASSWORD FROM USER$ NB WHERE NB.NAME =’LHRSYS’;

 

PASSWORD

——————————

F809740420A44EFC

 

SYS@lhrdb> ALTER USER LHRSYS   IDENTIFIED BY VALUES ‘F809740420A44EFC’;

 

User altered.

 

SYS@lhrdb> select username, account_status,EXPIRY_DATE,profile from dba_users where username = ‘LHRSYS’;

 

USERNAM ACCOUNT_STATUS                  EXPIRY_DATE         PROFILE

——- ——————————- ——————- ——————————

LHRSYS  OPEN                                                TESTPROFILE

 

SYS@lhrdb>

 

1.3.2  ACCOUNT_STATUS的九种状态

【密码】Oracle用户密码系列 

SYS@lhrdb> SELECT * FROM USER_ASTATUS_MAP;

 

   STATUS# STATUS

———- ——————————–

         0 OPEN

         1 EXPIRED

         2 EXPIRED(GRACE)

         4 LOCKED(TIMED)

         8 LOCKED

         5 EXPIRED & LOCKED(TIMED)

         6 EXPIRED(GRACE) & LOCKED(TIMED)

         9 EXPIRED & LOCKED

        10 EXPIRED(GRACE) & LOCKED

 

9 rows selected.

 

 

以上九种可以分为两大类:1.基本状态;2.组合状态。

前五种是基本状态:

         0 OPEN

         1 EXPIRED

         2 EXPIRED(GRACE)

         4 LOCKED(TIMED)

         8 LOCKED

 

后四种是组合状态:

         5 EXPIRED & LOCKED(TIMED)

         6 EXPIRED(GRACE) & LOCKED(TIMED)

         9 EXPIRED & LOCKED

        10 EXPIRED(GRACE) & LOCKED

规律是这样的:后四种的组合状态可以通过状态号STATUS#获得它是哪两种状态的组合,例如10=2+810 EXPIRED(GRACE) & LOCKED = 2 EXPIRED(GRACE) + 8 LOCKED)。因此只要了解基本状态的含义其他便可无师自通。

这五种基本状态又可以分为三类:1.正常状态;2.锁定状态;3.密码过期状态。

1OPEN表示用户处于正常状态。

2)用户被锁定状态,LOCKEDLOCKED(TIMED)两种状态都属于锁定状态

用户被锁定一般分为两种:一种是DBA显式的通过SQL语句对用户进行锁定;另外一种是被动的锁定,例如默认情况下如果密码输入错误超过10次(这个限制是由PROFILE中的FAILED_LOGIN_ATTEMPTS控制的,该信息可以通过DBA_PROFILES视图查询),用户将被锁定。

【密码】Oracle用户密码系列

1.3.2.1  锁定状态

一、 LOCKED

显式锁定LHRSYS用户LOCKED状态演示

SELECT D.USERNAME,

       D.ACCOUNT_STATUS,

       D.LOCK_DATE,

       D.EXPIRY_DATE,

       D.PROFILE,

       NVL(D.PASSWORD,

           (SELECT NB.PASSWORD FROM USER$ NB WHERE NB.NAME = D.USERNAME)) PASSWORD

  FROM DBA_USERS D

 WHERE D.USERNAME = ‘LHRSYS’;

【密码】Oracle用户密码系列 

SYS@lhrdb> ALTER USER LHRSYS ACCOUNT LOCK;

 

User altered.

 

SYS@lhrdb> conn lhrsys/lhr

ERROR:

ORA-28000: the account is locked

 

 

Warning: You are no longer connected to ORACLE.

@> conn /  as sysdba

Connected.

 

SYS@lhrdb> select username, account_status,d.lock_date from dba_users d where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE

—————————— ——————————– ——————-

LHRSYS                         LOCKED                           2016-12-02 09:33:50

 

SYS@lhrdb> ALTER USER LHRSYS ACCOUNT UNLOCK;

 

User altered.

 

SYS@lhrdb> select username, account_status,d.lock_date from dba_users d where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE

—————————— ——————————– ——————-

LHRSYS                         OPEN

 

 

二、 LOCKED(TIMED)

输入10次错误密码后被动锁定LOCKED(TIMED)状态演示

尝试输入10次错误密码后再次查询用户状态。

SYS@lhrdb> SELECT * FROM Dba_Profiles d WHERE d.profile=’DEFAULT’ AND D.resource_name LIKE ‘%FAILED_LOGIN_ATTEMPTS%’ ;

 

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

—————————— ——————————– ——– ——————————-

DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10

 

 

SYS@lhrdb> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

Warning: You are no longer connected to ORACLE.

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-28000: the account is locked<<<<<<<<<——超过10次后用户被锁定

 

@> CONN / AS SYSDBA

Connected.

 

SYS@lhrdb> select username, account_status,d.lock_date from dba_users d where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE

—————————— ——————————– ——————-

LHRSYS                        LOCKED(TIMED)                    2016-12-02 09:37:20

SYS@lhrdb>

SYS@lhrdb>

 

SYS@lhrdb> ALTER USER LHRSYS ACCOUNT UNLOCK;

 

User altered.

 

SYS@lhrdb> select username, account_status,d.lock_date from dba_users d where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE

—————————— ——————————– ——————-

LHRSYS                         OPEN

 

 

1.3.2.2  过期状态

用户密码过期状态,EXPIREDEXPIRED(GRACE)两种状态都属于密码过期状态

【密码】Oracle用户密码系列 

一、 EXPIRED

密码是否过期是通过修改PROFILE中的PASSWORD_LIFE_TIME实现的,密码过期后还可以使用的天数是通过PROFILE中的PASSWORD_GRACE_TIME控制的。

关于密码过期我们也可以使用SQL显式的去完成,简单演示一下。

SYS@lhrdb> select username, account_status,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE

—————————— ——————————– ——————-

LHRSYS                         OPEN

 

SYS@lhrdb> alter user lhrsys password expire;

 

User altered.

 

SYS@lhrdb> select username, account_status,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE

—————————— ——————————– ——————-

LHRSYS                         EXPIRED                          2016-12-01 16:29:01

 

SYS@lhrdb> conn lhrsys/lhr

ERROR:

ORA-28001: the password has expired

 

 

Changing password for lhrsys

New password:

Retype new password:

Password changed

Connected.

 

LHRSYS@lhrdb> conn / as sysdba

Connected.

SYS@lhrdb> select username, account_status,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE

—————————— ——————————– ——————-

LHRSYS                         OPEN

 

 

 

下面通过修改系统的日期来演示:

SYS@lhrdb> SELECT D.USERNAME,

  2         D.ACCOUNT_STATUS,

  3         D.LOCK_DATE,

  4         D.EXPIRY_DATE,

  5         D.PROFILE,

  6         NVL(D.PASSWORD,

  7             (SELECT NB.PASSWORD FROM USER$ NB WHERE NB.NAME = D.USERNAME)) PASSWORD

  8    FROM DBA_USERS D

  9   WHERE D.USERNAME = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE           EXPIRY_DATE         PROFILE                        PASSWORD

—————————— ——————————– ——————- ——————- —————————— ——————————

LHRSYS                         OPEN                                                                     DEFAULT                        F809740420A44EFC

 

SYS@lhrdb> create profile TESTPROFILE LIMIT password_life_time 5 password_grace_time 0;<<<<<<<<<——这里将password_grace_time设置为0

 

Profile created.

 

SYS@lhrdb> alter user LHRSYS profile TESTPROFILE;

 

User altered.

 

 

SYS@lhrdb>  select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         OPEN                             TESTPROFILE                    2016-12-07 14:50:36

 

修改系统时间

[root@orcltest ~]# date ‘12071450’

Wed Dec  7 14:50:00 CST 2016

[root@orcltest ~]#

系统查询:

SYS@lhrdb>  select sysdate from dual;

 

SYSDATE

——————-

2016-12-07 14:50:19

 

SYS@lhrdb>  conn LHRSYS/lhr

Connected.

LHRSYS@lhrdb>  conn LHRSYS/lhr

ERROR:

ORA-28001: the password has expired

 

 

Changing password for LHRSYS

New password:

Password unchanged

Warning: You are no longer connected to ORACLE.

 

@> conn / as sysdba

Connected.

SYS@lhrdb>  select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         EXPIRED                          TESTPROFILE                    2016-12-12 10:36:06

 

 

SYS@lhrdb> alter user LHRSYS identified by lhr;

 

User altered.

 

SYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         OPEN                             TESTPROFILE                    2016-12-12 14:52:54

 

 

二、 EXPIRED(GRACE)

当设置了PASSWORD_GRACE_TIME以后,第一次成功登录后到口令到期后有多少天时间可改变口令,在这段时间内,帐户被提醒修改口令并可以正常登陆,account_status显示为EXPIRED(GRACE)expired(grace) locked(timed)系统的profile来进行控制的。

SYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         OPEN                             TESTPROFILE                    2016-12-07 14:09:09

 

SYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-02 14:09:32

 

 

SYS@lhrdb> ALTER PROFILE TESTPROFILE LIMIT password_grace_time 3;

 

Profile altered.

 

SYS@lhrdb>  SELECT *

  2     FROM DBA_PROFILES D

  3    WHERE D.PROFILE = ‘TESTPROFILE’

  4      AND (D.RESOURCE_NAME LIKE ‘%PASSWORD%’ OR

  5          D.RESOURCE_NAME = ‘FAILED_LOGIN_ATTEMPTS’);

 

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

—————————— ——————————– ——– —————————————-

TESTPROFILE                    FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT

TESTPROFILE                    PASSWORD_LIFE_TIME               PASSWORD 5

TESTPROFILE                    PASSWORD_REUSE_TIME              PASSWORD DEFAULT

TESTPROFILE                    PASSWORD_REUSE_MAX               PASSWORD DEFAULT

TESTPROFILE                    PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT

TESTPROFILE                    PASSWORD_LOCK_TIME               PASSWORD DEFAULT

TESTPROFILE                    PASSWORD_GRACE_TIME              PASSWORD 3

 

7 rows selected.

 

SYS@lhrdb>

 

 

修改系统时间

[root@orcltest ~]# date ‘12071408’<<<<<<<<<——14:09过期,我们设置到14:08

Wed Dec  7 14:08:00 CST 2016

[root@orcltest ~]#

 

系统查询:

LHRSYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-07 14:08:03

 

LHRSYS@lhrdb> conn lhrsys/lhr

Connected.

 

LHRSYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-07 14:09:06

 

LHRSYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-07 14:09:09

 

LHRSYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-07 14:09:11<<<<<<<<<——已过了密码有效期

 

LHRSYS@lhrdb>  select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         OPEN                             TESTPROFILE                    2016-12-07 14:09:09 <<<<<<<<<——但该用户的状态未改变,下面尝试第一次登陆

 

LHRSYS@lhrdb> conn lhrsys/lhr

ERROR:

ORA-28002: the password will expire within 3 days<<<<<<<<<——第一次登陆后报错,但用户依然可以登陆,且EXPIRY_DATE已经变化

 

 

Connected.

LHRSYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         EXPIRED(GRACE)                   TESTPROFILE                    2016-12-10 14:09:34

<<<<<<<<<——再次查询状态,变为了EXPIRED(GRACE)

 

再次调整日期:

[root@orcltest ~]# date ‘12081430’

Thu Dec  8 14:30:00 CST 2016

 

LHRSYS@lhrdb>

LHRSYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-08 14:30:12

 

LHRSYS@lhrdb>  conn lhrsys/lhr

ERROR:

ORA-28002: the password will expire within 2 days<<<<<<<<<——变为了2

 

 

Connected.

LHRSYS@lhrdb>

继续更改日期:

[root@orcltest ~]# date ‘12101409’

Sat Dec 10 14:09:00 CST 2016

[root@orcltest ~]#

查询:

LHRSYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-10 14:09:07

 

 

LHRSYS@lhrdb> conn lhrsys/lhr

ERROR:

ORA-28002: the password will expire within 0 days<<<<<<<<<——变为了0

 

 

Connected.

 

LHRSYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         EXPIRED(GRACE)                   TESTPROFILE                    2016-12-10 14:09:34

 

LHRSYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-10 14:09:39<<<<<<<<<——GRACE日期已过

 

LHRSYS@lhrdb>  conn lhrsys/lhr

ERROR:

ORA-28001: the password has expired

 

 

Changing password for lhrsys

New password:

Password unchanged

Warning: You are no longer connected to ORACLE.

 

@> conn / as sysdba

Connected.

SYS@lhrdb>

SYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         EXPIRED                          TESTPROFILE                    2016-12-10 14:09:34

 

SYS@lhrdb>  alter user LHRSYS identified by lhr;

 

User altered.

 

SYS@lhrdb>  select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         OPEN                             TESTPROFILE                    2016-12-15 14:13:08

 

SYS@lhrdb>

 

 

1.3.2.3  组合状态

关于四种组合状态的解释

因为锁定的两种状态(LOCKEDLOCKED(TIMED))和密码过期的两种状态(EXPIREDEXPIRED(GRACE))之间没有关系。因此他们之间可以任意组合,2×2=4,因此有四种组合状态:

         5 EXPIRED & LOCKED(TIMED)

         6 EXPIRED(GRACE) & LOCKED(TIMED)

         9 EXPIRED & LOCKED

        10 EXPIRED(GRACE) & LOCKED

 

一、 EXPIRED & LOCKED

EXPIRED & LOCKED状态表示用户密码过期且同时处于锁定状态

SYS@lhrdb> select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         OPEN                             2016-12-07 15:02:56

SYS@lhrdb>

SYS@lhrdb> alter user lhrsys password expire;

 

User altered.

 

SYS@lhrdb> select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         EXPIRED                          2016-12-02 15:11:12

 

SYS@lhrdb> alter user lhrsys  account lock;

 

User altered.

 

SYS@lhrdb> select username, account_status,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE

—————————— ——————————– ——————-

LHRSYS                         EXPIRED & LOCKED                 2016-12-01 16:51:38

 

SYS@lhrdb> alter user lhrsys  account unlock;

 

User altered.

 

SYS@lhrdb>  select username, account_status,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE

—————————— ——————————– ——————-

LHRSYS                         EXPIRED                          2016-12-01 16:51:38

 

SYS@lhrdb>  alter user lhrsys identified by lhr;

 

User altered.

 

SYS@lhrdb>  select username, account_status,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE

—————————— ——————————– ——————-

LHRSYS                         OPEN

 

 

 

 

二、 EXPIRED & LOCKED(TIMED)

EXPIRED & LOCKED(TIMED)状态表示用户密码过期后,错误密码尝试次数超过PROFILE中的FAILED_LOGIN_ATTEMPTS的限制

 

SYS@lhrdb> select username, account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         OPEN

 

 

SYS@lhrdb> alter user lhrsys password expire;

 

User altered.

 

SYS@lhrdb> select username, account_status,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE

—————————— ——————————– ——————-

LHRSYS                         EXPIRED                          2016-12-02 10:07:27

 

SYS@lhrdb> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

Warning: You are no longer connected to ORACLE.

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> conn / as sysdba

Connected.

 

SYS@lhrdb> select username, account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         EXPIRED & LOCKED(TIMED)          2016-12-02 10:07:27 2016-12-02 10:09:03

 

SYS@lhrdb>

SYS@lhrdb> alter user lhrsys  account unlock;

 

User altered.

 

SYS@lhrdb> select username, account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         EXPIRED                          2016-12-02 10:07:27

 

SYS@lhrdb> alter user lhrsys identified by lhr;

 

User altered.

 

SYS@lhrdb> select username, account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         OPEN

 

SYS@lhrdb>

 

 

 

三、 EXPIRED(GRACE) & LOCKED

EXPIRED(GRACE) & LOCKED状态表示用户在密码过期后的有效期内被DBA手工锁定。

LHRSYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         OPEN                             TESTPROFILE                    2016-12-07 14:39:20

 

LHRSYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-07 14:39:17

 

LHRSYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-07 14:39:25

 

 

LHRSYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         OPEN                             TESTPROFILE                    2016-12-07 14:39:20

 

LHRSYS@lhrdb> conn lhrsys/lhr

ERROR:

ORA-28002: the password will expire within 3 days

 

 

Connected.

LHRSYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         EXPIRED(GRACE)                   TESTPROFILE                    2016-12-10 14:39:54

 

LHRSYS@lhrdb> alter user lhrsys account lock;

 

User altered.

 

SYS@lhrdb>  select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         EXPIRED(GRACE) & LOCKED          2016-12-10 14:39:54 2016-12-07 14:40:20

 

 

LHRSYS@lhrdb> alter user lhrsys account unlock;

 

User altered.

 

LHRSYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         EXPIRED(GRACE)                   TESTPROFILE                    2016-12-10 14:39:54

 

LHRSYS@lhrdb>  alter user LHRSYS identified by lhr;

 

User altered.

 

LHRSYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         OPEN                             TESTPROFILE                    2016-12-12 14:40:46

 

LHRSYS@lhrdb>

 

四、 EXPIRED(GRACE) & LOCKED(TIMED)

EXPIRED(GRACE) & LOCKED(TIMED)状态表示用户在密码过期后的有效期内,失败登录次数超过PROFILE中的FAILED_LOGIN_ATTEMPTS的限制

SYS@lhrdb>  select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         OPEN                             2016-12-07 14:50:06

 

LHRSYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-02 14:50:13

 

修改系统日期:

[root@orcltest ~]# date ‘12071450’

Wed Dec  7 14:50:00 CST 2016

[root@orcltest ~]#

查询:

LHRSYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-07 14:50:03

 

LHRSYS@lhrdb> select sysdate from dual;

 

SYSDATE

——————-

2016-12-07 14:50:12

 

SYS@lhrdb>  select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         OPEN                             2016-12-07 14:50:06

 

LHRSYS@lhrdb> conn lhrsys/lhr

ERROR:

ORA-28002: the password will expire within 3 days

 

 

Connected.

SYS@lhrdb>  select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         EXPIRED(GRACE)                   2016-12-10 14:50:21

 

LHRSYS@lhrdb> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

Warning: You are no longer connected to ORACLE.

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

 

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> @> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-28000: the account is locked

 

 

@> conn / as sysdba

Connected.

SYS@lhrdb> select username,account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         EXPIRED(GRACE) & LOCKED(TIMED)   2016-12-10 14:50:21 2016-12-07 14:53:30

 

 

SYS@lhrdb> alter user lhrsys account unlock;

 

User altered.

 

SYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         EXPIRED(GRACE)                   TESTPROFILE                    2016-12-10 14:50:21

 

SYS@lhrdb>  alter user LHRSYS identified by lhr;

 

User altered.

 

SYS@lhrdb> select username,account_status,profile,EXPIRY_DATE from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   PROFILE                        EXPIRY_DATE

—————————— ——————————– —————————— ——————-

LHRSYS                         OPEN                             TESTPROFILE                    2016-12-12 14:55:04

 

 

 

1.3.3  在不知道用户密码的情况下如何更改密码

Oracle若用户的密码变为锁定状态(LOCKEDLOCKED(TIMED))则DBA直接执行ALTER USER用户名 ACCOUNT UNLOCK就可以解锁了。但是如果用户的状态变成过期状态EXPIREDEXPIRED(GRACE)),DBA必须要更改用户的密码账户才能重新使用但有些时候因为各种原因并不知道原密码的明文是什么这时候可以有如下2种办法来更新密码。

1.3.3.1  用原密码的密文来更改密码

Oracle 10g中,DBA_USERS视图的PASSWORD字段提供了密码的密文形式,而在Oracle 11g中,该字段被弃用了,内容为空,但是在基表USER$中的PASSWORD字段依然有记录密文形式,所以可以通过如下的形式来获取密码的密文形式:

SELECT D.USERNAME,

       D.ACCOUNT_STATUS,

       D.LOCK_DATE,

       D.EXPIRY_DATE,

       D.PROFILE,

       NVL(D.PASSWORD,

           (SELECT NB.PASSWORD FROM USER$ NB WHERE NB.NAME = D.USERNAME)) PASSWORD

  FROM DBA_USERS D

 WHERE D.USERNAME = ‘LHRSYS’;

【密码】Oracle用户密码系列 

另外,可以通过DBMS_METADATA.GET_DDL包或者expdpexp命令来获取创建用户的语句从而获取密码的密文形式:

SYS@lhrdb> set long 9999

SYS@lhrdb> SELECT DBMS_METADATA.GET_DDL(‘USER’, ‘LHRSYS’) DDL_SQL FROM DUAL;

 

DDL_SQL

——————————————————————————–

 

   CREATE USER “LHRSYS” IDENTIFIED BY VALUES ‘S:853EA80BAE11F79D6946453F38059E30313FE84C96AE2EE4F3AA35A648BD;F809740420A44EFC

      DEFAULT TABLESPACE “USERS”

      TEMPORARY TABLESPACE “TEMP”

 

 

SYS@lhrdb>

SYS@lhrdb>

 

 

获取了密码的密文后就可以用如下的命令来修改了,注意:使用密文的命令中多了一个values关键字:

SYS@lhrdb>

SYS@lhrdb> alter user LHRSYS identified by values ‘F809740420A44EFC’;

 

User altered.

 

SYS@lhrdb> CONN  LHRSYS/lhr@192.168.59.129/lhrdb

Connected.

LHRSYS@192.168.59.129/lhrdb> conn / as sysdba

Connected.

SYS@lhrdb> alter user LHRSYS identified by values ‘S:853EA80BAE11F79D6946453F38059E30313FE84C96AE2EE4F3AA35A648BD;F809740420A44EFC’;

 

User altered.

 

SYS@lhrdb> CONN  LHRSYS/lhr@192.168.59.129/lhrdb

Connected.

LHRSYS@192.168.59.129/lhrdb>

 

 

这种情况下虽然我们不知道原密码是什么但可以用它的密文来更改密码这样在不知道原密码的情况下既保持了密码不改变又可以把expired的状态更改掉

MOS The Impact of PASSWORD_LIFE_TIME Database Profile Parameter Default to 180 Days on Network Charging and Control (文档 ID 1543668.1)中搜到了如下的命令也可以直接获取密码:

SELECT SQLTEXT

  FROM (SELECT NAME,

               ‘alter user ‘ || NAME || ‘ identified by values ”’ ||

               PASSWORD || ”’;’ SQLTEXT

          FROM USER$

         WHERE SPARE4 IS NULL

           AND PASSWORD IS NOT NULL

        UNION

        SELECT NAME,

               ‘alter user ‘ || NAME || ‘ identified by values ”’ || SPARE4 || ‘;’ ||

               PASSWORD || ”’;’ SQLTEXT

          FROM USER$

         WHERE SPARE4 IS NOT NULL

           AND PASSWORD IS NOT NULL)

 WHERE NAME = ‘LHRSYS’;

SYS@lhrdb> SELECT SQLTEXT

  2    FROM (SELECT NAME,

  3                 ‘alter user ‘ || NAME || ‘ identified by values ”’ ||

  4                 PASSWORD || ”’;’ SQLTEXT

  5            FROM USER$

  6           WHERE SPARE4 IS NULL

  7             AND PASSWORD IS NOT NULL

  8          UNION

  9          SELECT NAME,

10                 ‘alter user ‘ || NAME || ‘ identified by values ”’ || SPARE4 || ‘;’ ||

11                 PASSWORD || ”’;’ SQLTEXT

12            FROM USER$

13           WHERE SPARE4 IS NOT NULL

14             AND PASSWORD IS NOT NULL)

15   WHERE NAME = ‘LHRSYS’;

 

SQLTEXT

——————————————

alter user LHRSYS identified by values ‘S:853EA80BAE11F79D6946453F38059E30313FE84C96AE2EE4F3AA35A648BD;F809740420A44EFC’;

 

 

1.3.3.2  直接更新USER$基表

不管用户的状态是什么,通过更新USER$表可以让用户处于OPEN状态:

SYS@lhrdb> select username, account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         EXPIRED                          2016-12-02 10:40:09

 

SYS@lhrdb> UPDATE USER$ SET ASTATUS=0 WHERE NAME=’LHRSYS’;

 

1 row updated.

 

SYS@lhrdb> commit;<<<<<<<<<——及时提交

 

Commit complete.

 

SYS@lhrdb> select username, account_status,EXPIRY_DATE,lock_date from dba_users where username = ‘LHRSYS’;

 

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         LOCK_DATE

—————————— ——————————– ——————- ——————-

LHRSYS                         OPEN

 

SYS@lhrdb>

 

 

1.3.4  user$.lCOUNT列记录了失败的登陆次数

登录失败,lcount1;只要成功登录后,lcount栏位就会置0

SYS@lhrdb> select NAME,LCOUNT  from user$ a WHERE a.NAME=’LHRSYS’;

 

NAME                               LCOUNT

—————————— ———-

LHRSYS                                  0

 

SYS@lhrdb> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

Warning: You are no longer connected to ORACLE.

@> CONN  LHRSYS/XXXX@192.168.59.129/lhrdb

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

@> conn / as sysdba

Connected.

SYS@lhrdb> select NAME,LCOUNT  from user$ a WHERE a.NAME=’LHRSYS’;

 

NAME                               LCOUNT

—————————— ———-

LHRSYS                                  2

 

SYS@lhrdb>

SYS@lhrdb> CONN  LHRSYS/lhr

Connected.

LHRSYS@lhrdb> conn / as sysdba

Connected.

SYS@lhrdb> select NAME,LCOUNT  from user$ a WHERE a.NAME=’LHRSYS’;

 

NAME                               LCOUNT

—————————— ———-

LHRSYS                                  0

 

SYS@lhrdb>

 

 

另外,审计表也记录了登陆失败的信息:

SELECT d.username,d.timestamp,d.action_name,d.os_username,d.terminal

  FROM DBA_AUDIT_TRAIL D

 WHERE D.RETURNCODE = 1017

   AND D.USERNAME = ‘LHRSYS’

 ORDER BY d.timestamp DESC ;

【密码】Oracle用户密码系列 

 

1.3.5  11g密码区分大小写–sec_case_sensitive_logon

Property

Description

Parameter type

Boolean

Default value

true

Modifiable

ALTER SYSTEM

Range of values

true | false

Basic

No

Oracle 11g开始,密码区分大小写,采用参数sec_case_sensitive_logon控制,该参数默认为TRUE

 

SEC_CASE_SENSITIVE_LOGON enables or disables password case sensitivity in the database.

Values:

trueDatabase logon passwords are case sensitive.

falseDatabase logon passwords are not case sensitive.

 

1.3.6  密码延迟验证

11g开始,如果一个用户使用不正确的密码尝试登录数据库,那么随着登录失败次数的增加,每次登录验证前延迟等待的时间也会增加。

 

通过设置EVENTS 28401可以屏蔽密码延迟验证:

SQL> ALTER SYSTEM SET EVENT = ‘28401 TRACE NAME CONTEXT FOREVER, LEVEL 1’ SCOPE = SPFILE

 

设置该事件后重启数据库即可。

[oracle@orcltest ~]$ oerr ora 28401

28401, 00000, “Event to disable delay after three failed login attempts”

// *Document: NO

// *Cause: N/A

// *Action: Set this event in your environment to disable the login delay

//          which will otherwise take place after three failed login attempts.

// *Note: THIS IS NOT A USER ERROR NUMBER/MESSAGE. THIS DOES NOT NEED TO BE

//        TRANSLATED OR DOCUMENTED.

[oracle@orcltest ~]$

 

 

1.3.7  哪些用户密码没有被修改过

Oracle11g中对于安全方面进行了很大的改进,比如增加了密码大小写验证,增加了密码复杂度的验证等等。在Oracle 11g中还提供了一个视图DBA_USERS_WITH_DEFPWD用来指出那些用户的密码没有被修改过,仍然是数据库默认密码。Oracle并不是简单的监测是否密码被修改,而是检查密码是否修改为别的值,如果新密码和旧密码保持一致,那么即使密码被修改,这个用户仍然在DBA_USERS_WITH_DEFPWD视图中。

SELECT * FROM DBA_USERS_WITH_DEFPWD;

【密码】Oracle用户密码系列 

 

1.3.8  密码复杂性校验

脚本位置:$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

【密码】Oracle用户密码系列

[oracle@orcltest ~]$ ll $ORACLE_HOME/rdbms/admin/utlpwdmg.sql

-rw-r–r– 1 oracle oinstall 11555 Aug 13  2006 /u02/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlpwdmg.sql

[oracle@orcltest ~]$

 

SYS@lhrdb> @?/rdbms/admin/utlpwdmg.sql

 

Function created.

 

 

Profile altered.

 

 

Function created.

 

SYS@lhrdb>

 

 

该脚本中有如下的一段:

ALTER PROFILE DEFAULT LIMIT

PASSWORD_LIFE_TIME 180

PASSWORD_GRACE_TIME 7

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_REUSE_MAX UNLIMITED

FAILED_LOGIN_ATTEMPTS 10

PASSWORD_LOCK_TIME 1

PASSWORD_VERIFY_FUNCTION verify_function_11G;

 

更改之后查看:

SYS@lhrdb> SELECT * FROM Dba_Profiles d WHERE d.profile=’DEFAULT’;

 

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 180

DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION_11G

DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1

DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7

 

16 rows selected.

 

SYS@lhrdb> create user lhrpwd identified by lhr;

create user lhrpwd identified by lhr

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20001: Password length less than 8

 

 

SYS@lhrdb>

SYS@lhrdb> alter profile default limit PASSWORD_VERIFY_FUNCTION null;<<<<<<<<<——取消复杂性验证

 

Profile altered.

 

SYS@lhrdb> create user lhrpwd identified by lhr;

 

User created.

 

SYS@lhrdb> alter profile default limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;<<<<<<<——启用密码复杂性验证

 

Profile altered.

 

SYS@lhrdb>

 

 

 

1.3.9  resource_limit

官方文档资料:

To create a profile, you must have the CREATE PROFILE system privilege.

To specify resource limits for a user, you must:

?Enable resource limits dynamically with the ALTER SYSTEM statement or with the initialization parameter RESOURCE_LIMIT. This parameter does not apply to password resources. Password resources are always enabled.

?Create a profile that defines the limits using the CREATE PROFILE statement

?Assign the profile to the user using the CREATE USER or ALTER USER statement

 

1)用户所有拥有的PROFILE中有关密码的限制永远生效,不受限制。
2)用户所有拥有的PROFILE中有关资源的限制与resource_limit参数的设置有关,当为TRUE时生效,当为FALSE时(默认值是FALSE)无效。

About Me

……………………………………………………………………………………………………………….

本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

本文在itpubhttp://blog.itpub.net/26736162)、博客园http://www.cnblogs.com/lhrbest和个人微信公众号(xiaomaimiaolhr)上有同步更新

本文itpub地址:http://blog.itpub.net/26736162/viewspace-2129595/

本文博客园地址:http://www.cnblogs.com/lhrbest/p/6127710.html

本文pdf小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群:230161599     微信群:私聊

联系我请加QQ好友(642808185),注明添加缘由

2016-12-01 15:00 ~ 2016-12-02 23:00农行完成

文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

版权所有,欢迎分享本文,转载请保留出处

……………………………………………………………………………………………………………….

手机长按下图识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号:xiaomaimiaolhr,免费学习最实用的数据库技术。

【密码】Oracle用户密码系列

 

【密码】Oracle用户密码系列

JDBC connect SCAN IP

SCAN(Single Client Access Name)是Oracle从11g R2开始推出的,客户端可以通过SCAN特性负载均衡地连接到RAC数据库。SCAN提供一个域名来访问RAC,域名可以解析1个到3个(注意,最多3个)SCAN IP,我们可以通过DNS或者GNS来解析实现。其中DNS大家都很熟悉,这里不多说。GNS(Grid Naming Service)则是Oracle 11g R2的新功能,可以通过DHCP服务为节点和SCAN分配VIP和SCAN IP。另外还有个优点是,对于新加入集群的节点,它会自动分配VIP地址,更新集群资源,客户端依然通过SCAN特性负载均衡地连接到新增集群节点上。除了DNS和GNS解析方法外,SCAN也可以使用hosts文件来解析。客户原来使用的Oracle 10g R2单机,现在使用的是Oracle 11g R2 RAC,客户应用程序模块不完全是通过Weblogic的jdbc数据源来连接数据库,有个别模块单独执行jdbc连接,其连接代码如下:

 Connection con=null;
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con=DriverManager.getConnection("jdbc:oracle:thin:@10.10.12.3:1521:RLZY","xxxxx","xxxxx");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}  

程序执行出现如下错误

 java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
 
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
	at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:553)
	at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:254)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
	at java.sql.DriverManager.getConnection(DriverManager.java:571)
	at java.sql.DriverManager.getConnection(DriverManager.java:215)
	at t.testdb.execute(testdb.java:19)
	at t.testdb.main(testdb.java:63)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
 
	at oracle.net.ns.NSProtocol.connect(NSProtocol.java:399)
	at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340)
	... 8 more
Exception in thread "main" java.lang.NullPointerException
	at t.testdb.execute(testdb.java:30)
	at t.testdb.main(testdb.java:63)  

错误信息说明不能识别连接字符串中的SID,这里为什么会显示使用的是SID,我们指定的是SERVICE_NAME,这就与JDBC连接字符串的写法有关。如果使用jdbc:oracle:thin:@10.10.12.3:1521:RLZY,JDBC会将RLZY解析为SID,如果是10.10.12.3:1521/RLZY,JDBC会将RLZY解析为服务名。对于单实例来说,一般SID与SERVICE_NAME相同,但是对于RAC来说,SID与SERVICE_NAME是不一样的。对于11g RAC要使用SCAN IP来连接,那么只能使用SERVICE_NAME,将代码修改成如下格式:

 Connection con=null;
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con=DriverManager.getConnection("jdbc:oracle:thin:@10.10.12.3:1521/RLZY","xxx","xxx");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}  

总结,对于jdbc连接数据库不管是单实例还是RAC,都建议使用在连接字符串中使用jdbc:oracle:thin:@IP:PORT/SERVICE_NAME这种方法就可以完美处理这种问题。

ASM Setting Larger AU Sizes

ASM分配单元
ASM分配空间以chunks为单位,叫作分配单元(AUs)。一个AU是最细粒度的分配–每个ASM磁盘都以相 同大小的AU进行划分。ASM 1MB条带大小对于Oracle数据库来说已经证明是最佳条带深度并且将会支 持最大I/O请求。这个最佳条带大小,再加上均匀分布磁盘组中的区与RDBMS中的buffer cache,防止热点。

对于VLDBs设置 Large AU Size
对于非常大的数据库(VLDBs)–例如,数据库大小为10TB与更大的来说,改变缺省AU大小是有意义的 。以下是对于VLDB改变缺省大小的优点:
.减小RDBMS实例中管理区映射的大小
.增加文件大小限制
.减小数据库打开的时间,因为VLDB通常有许多大的数据文件

增加AU大小可以提高oracle 10g打开大数据库的时间,也会减小区映射所消耗共享池的大小。使用 1MB AU与固定大小区,对于一个10TB数据库来说区映射的大小大约是90MB,在打开数据库时会被读取并被保存在内存中。使用16M AU,对于10TB数据库来说区映射大小减小为大约5.5MB。在Oracle 10g 中,一个文件整个区映射是在文件打开时从磁盘进行读取的。

Oracle 11g通过按需读取区映射显著的最小化了文件打开延迟问题。在Oracle 10g中,对于每个文件 的打开,完整的区映射需要构建并且从ASM实例发送给RDBMS实例。对于大文件,延长文件打开时间这 是不必要的。在oracle 11g中,在文件打开时只有区映射中的前60个区会被发送。剩下的以批量方式被发送到RDBMS。

在Oracle 11g中设置Large AU Size
对于Oracle 11g ASM系统,下面的create diskgroup命令可以被执行用来设置合适的AU大小:

SQL> CREATE DISKGROUP DATA DISK '/dev/raw/raw15', '/dev/raw/raw16',
'/dev/raw/raw17' ATTRIBUTE 'au_size' = '16M', 'compatible.asm' = '11.1'
'compatible.rdbms' = '11.1';

对Oracle 10g设置Large AU Size
在Oracle 10g中,ASM提供了两个隐藏参数(_asm_ausize,_asm_stripesize)来允许你使用16MB的AU大小来创建磁盘组并且对于1MB(代 替128K)有更好的细粒度条带。

SQL> set long 200
SQL> set linesize 200
SQL> select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ
  2  from x$ksppi x, x$ksppcv y
  3  where x.inst_id=USERENV('Instance')
  4  and y.inst_id=USERENV('Instance')
  5  and x.indx=y.indx
  6  and x.ksppinm='_asm_stripesize';

NAME                           VALUE                DESCRIB
------------------------------ -------------------- ------------------------------
_asm_stripesize                131072               ASM file stripe size

SQL> select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ
  2  from x$ksppi x, x$ksppcv y
  3  where x.inst_id=USERENV('Instance')
  4  and y.inst_id=USERENV('Instance')
  5  and x.indx=y.indx
  6  and x.ksppinm='_asm_ausize'
  7  ;

NAME                           VALUE                DESCRIB
------------------------------ -------------------- ------------------------------
_asm_ausize                    1048576              allocation unit size

AU参数只在创建磁盘组时才使用,而且在磁盘组创建之后现有磁盘组的AU大小是不会改变的。下面的例子使用16MB AU大小来创建一个磁盘组并且对所有数据库文件允许使用1MB的细粒度条带。

1.关闭ASM实例

oracle@jyrac3 ~]$ export ORACLE_SID=+ASM1
[oracle@jyrac3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Nov 30 11:12:02 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown immediate
ASM diskgroups dismounted
ASM instance shutdown


oracle@jyrac4 ~]$ export ORACLE_SID=+ASM2
[oracle@jyrac4 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Nov 30 11:12:02 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown immediate
ASM diskgroups dismounted
ASM instance shutdown

2.编辑ASM实例的initSID.ora文件增加以下参数:

[oracle@jyrac3 dbs]$ vi init+ASM1.ora
...省略...
#ASM AU 16MB
_asm_ausize=16777216
#ASM fine grain stripesize 1MB
_asm_stripesize=1048576

[oracle@jyrac4 dbs]$ vi init+ASM2.ora
...省略...
#ASM AU 16MB
_asm_ausize=16777216
#ASM fine grain stripesize 1MB
_asm_stripesize=1048576

3.重新ASM实例。为了使用新参数生效ASM实例必须重启。在设置完隐藏参数并重启ASM实例,在这之 后创建的磁盘组将使用新的AU大小与细粒度条带大小。

SQL> col name for a20
SQL> col value for a20
SQL> col describ for a20
SQL> select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ
  2  from x$ksppi x, x$ksppcv y
  3  where x.inst_id=USERENV('Instance')
  4  and y.inst_id=USERENV('Instance')
  5  and x.indx=y.indx
  6  and x.ksppinm='_asm_ausize';

NAME                 VALUE                DESCRIB
-------------------- -------------------- --------------------
_asm_ausize          16777216             allocation unit size

SQL> select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ
from x$ksppi x, x$ksppcv y
  2    3  where x.inst_id=USERENV('Instance')
  4  and y.inst_id=USERENV('Instance')
  5  and x.indx=y.indx
  6  and x.ksppinm='_asm_stripesize';

NAME                 VALUE                DESCRIB
-------------------- -------------------- --------------------
_asm_stripesize      1048576              ASM file stripe size

4.创建磁盘组data_nrml

SQL> create diskgroup data_nrml  normal redundancy failgroup fg1 disk '/dev/raw/raw5'  failgroup fg2 disk '/dev/raw/raw6';

Diskgroup created.

5.查询v$asm_diskgroup_stat或v$asm_diskgroup中的allocation_unit_size来验证磁盘组data_nrml 的AU大小是否为16MB.

SQL> select name, allocation_unit_size from v$asm_diskgroup where name='DATA_NRML';

NAME                 ALLOCATION_UNIT_SIZE
-------------------- --------------------
DATA_NRML                        16777216

6.通过查询v$asm_template视图来查看磁盘组data_nrml的ASM文件模板,为了完成1MB的条带大小需 要将磁盘组中所要存储的所有文件类型的条带类型设置为FINE

SQL> select * from v$asm_template where group_number=2;

GROUP_NUMBER ENTRY_NUMBER REDUNDANCY   STRIPE       SY NAME
------------ ------------ ------------ ------------ -- --------------------
           2            0 MIRROR       COARSE       Y  PARAMETERFILE
           2            1 MIRROR       COARSE       Y  DUMPSET
           2            2 HIGH         FINE         Y  CONTROLFILE
           2            3 MIRROR       COARSE       Y  ARCHIVELOG
           2            4 MIRROR       FINE         Y  ONLINELOG
           2            5 MIRROR       COARSE       Y  DATAFILE
           2            6 MIRROR       COARSE       Y  TEMPFILE
           2            7 MIRROR       COARSE       Y  BACKUPSET
           2            8 MIRROR       COARSE       Y  AUTOBACKUP
           2            9 MIRROR       COARSE       Y  XTRANSPORT
           2           10 MIRROR       COARSE       Y  CHANGETRACKING
           2           11 MIRROR       FINE         Y  FLASHBACK
           2           12 MIRROR       COARSE       Y  DATAGUARDCONFIG
SQL> declare
  2  cursor jl is select * from v$asm_template where group_number=2 and STRIPE='COARSE';
  3  begin
  4      for r in jl loop
  5       execute immediate 'alter diskgroup data_nrml alter template '||r.name||'  attributes (fine)';
  6      end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select * from v$asm_template where group_number=2;

GROUP_NUMBER ENTRY_NUMBER REDUNDANCY   STRIPE       SY NAME
------------ ------------ ------------ ------------ -- --------------------
           2            0 MIRROR       FINE         Y  PARAMETERFILE
           2            1 MIRROR       FINE         Y  DUMPSET
           2            2 HIGH         FINE         Y  CONTROLFILE
           2            3 MIRROR       FINE         Y  ARCHIVELOG
           2            4 MIRROR       FINE         Y  ONLINELOG
           2            5 MIRROR       FINE         Y  DATAFILE
           2            6 MIRROR       FINE         Y  TEMPFILE
           2            7 MIRROR       FINE         Y  BACKUPSET
           2            8 MIRROR       FINE         Y  AUTOBACKUP
           2            9 MIRROR       FINE         Y  XTRANSPORT
           2           10 MIRROR       FINE         Y  CHANGETRACKING
           2           11 MIRROR       FINE         Y  FLASHBACK
           2           12 MIRROR       FINE         Y  DATAGUARDCONFIG

这种改变的目的是尽管使用大AU设置,仍然保持1MB的文件区分布。如果使用缺省的coarse条带,那 么将使用16MB进行条带。在Oracle 11g中当使用可变区大小就不需要执行这种改变。