How much do you know about Oracle's password policy?

Today suddenly a customer asked me a question, the database to add a monitoring user, want to do a limit on the number of sessions, here to do a small test, weekday maintenance time also need to pay attention to the database resource constraints.

<roidb1:orcl1:/home/oracle> $sqlplus/as SYSDBA

Sql*plus:release11.2.0.4.0Production on Mon Sep 18 18:16:19 2017

Copyright (c) 1982, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the partitioning, Real application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real application testing options



Sql> Show Parameter Resource_limit

NAME TYPE VALUE
------------------------------------ ----------- ---------
Resource_limit BooleanFALSE--Default value
Sql>


Sql> Set Linesize 160
Sql>SELECT * from Dba_profiles order by 1,3,2;

Profile Resource_name RESOURCE LIMIT
------------------------- -------------------------------- ------------ ---------
DEFAULT Composite_limit KERNEL UNLIMITED
DEFAULT connect_time KERNEL UNLIMITED
DEFAULT Cpu_per_call KERNEL UNLIMITED
DEFAULT cpu_per_session KERNEL UNLIMITED
DEFAULT idle_time KERNEL UNLIMITED
DEFAULT Logical_reads_per_call KERNEL UNLIMITED
DEFAULT logical_reads_per_session KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT Sessions_per_user KERNEL UNLIMITED
DEFAULT failed_login_attempts PASSWORD 10
DEFAULT Password_grace_time PASSWORD 7

Profile Resource_name RESOURCE LIMIT
------------------------- -------------------------------- ------------ --------
DEFAULT Password_life_time PASSWORD 180
DEFAULT password_lock_time PASSWORD 1
DEFAULT Password_reuse_max PASSWORD UNLIMITED
DEFAULT password_reuse_time PASSWORD UNLIMITED
DEFAULT password_verify_function PASSWORD NULL
Monitoring_profile Composite_limit KERNEL DEFAULT
Monitoring_profile connect_time KERNEL DEFAULT
Monitoring_profile Cpu_per_call KERNEL DEFAULT
Monitoring_profile cpu_per_session KERNEL DEFAULT
Monitoring_profile idle_time KERNEL DEFAULT
Monitoring_profile Logical_reads_per_call KERNEL DEFAULT

Profile Resource_name RESOURCE LIMIT
------------------------- -------------------------------- ------------ ---------------
Monitoring_profile logical_reads_per_session KERNEL DEFAULT
Monitoring_profile PRIVATE_SGA KERNEL DEFAULT
Monitoring_profile Sessions_per_user KERNEL DEFAULT
Monitoring_profile failed_login_attempts PASSWORD UNLIMITED
Monitoring_profile password_grace_time PASSWORD DEFAULT
Monitoring_profile password_life_time PASSWORD DEFAULT
Monitoring_profile password_lock_time PASSWORD DEFAULT
Monitoring_profile Password_reuse_max PASSWORD DEFAULT
Monitoring_profile password_reuse_time PASSWORD DEFAULT
Monitoring_profile password_verify_function PASSWORD DEFAULT

Rows selected.


Test whether the 1:failed_login_attempts=10 is in effect

Sql> create user roidba identified by ROIDBA;

User created.

Sql> Grant CONNECT,RESOURCE,DBA to ROIDBA;

Grant succeeded.

Sql> exit


Omitted.................. After 10 landings ......



<roidb1:orcl1:/home/oracle> $sqlplus Roidba/roidbaa

Sql*plus:release 11.2.0.4.0 Production on Mon Sep 18 18:26:37 2017

Copyright (c) 1982, Oracle. All rights reserved.

ERROR:
Ora-28000:the account is locked


Enter User-name:
ERROR:
Ora-01017:invalid Username/password; Logon denied


Enter User-name:


Experiments show that regardless of whether Resource_limit is true, and password-related restrictions are in effect, other and password-related people can test themselves.


DEFAULT failed_login_attempts PASSWORD 10--Password input 10 times is error, user lock
DEFAULT password_lock_time PASSWORD 1--lock automatically after one day
DEFAULT password_life_time PASSWORD 180--Password life cycle 180 days, after password expiration
DEFAULT password_grace_time PASSWORD 7-Grace continuation period, the grace period login will be prompted.


After installing the database, Password_life_time is generally set to unlimited.
sql> alter profile default limit Password_life_time unlimited;
Profile altered.



Continue Test 2:
Sql> Create profile Sess limit
2 Sessions_per_user 2;

Profile created.


sql> alter user ROIDBA profile Sess;

User altered.

Sql> alter system set resource_limit=true;

System altered.


Open three windows, the first two have successfully landed, the third one appears the following error.


<roidb1:orcl1:/home/oracle> $sqlplus ROIDBA/ROIDBA

Sql*plus:release 11.2.0.4.0 Production on Mon Sep 18 18:48:24 2017

Copyright (c) 1982, Oracle. All rights reserved.

ERROR:
ora-02391:exceeded simultaneous Sessions_per_user limit


Enter User-name:

Small partners, do not just watch and do not practice, spend a very 20 minutes to do a hands-on ha!

This article is from the "ROIDBA" blog, make sure to keep this source http://roidba.blog.51cto.com/12318731/1966441

How much do you know about Oracle's password policy?