Database Reference
In-Depth Information
The dynamic agenda here is that users want to minimize the time spent with passwords. This is the same
dynamic with an organization; for example, individual departments wish to minimize time spent on risk reduction as
they care less about the overall well-being of the organization long term than they do about their department's short-
term productivity and profits. It is the security and compliance functions' job to make them spend the right amount of
time on risk reduction as befits the interests of the organization as a whole.
So let's turn on password history. First, let's see which profile is active for our test user.
SQL> select profile from dba_users where username='PWTEST';
PROFILE
--------------------------------------------------------------------------------
DEFAULT
Then, let's see what the values are:
SQL> select resource_name, limit from dba_profiles where resource_name in
('PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX');
RESOURCE_NAME LIMIT
-------------------------------- ------------------------
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
Both are still unlimited, so there is no password history. So let's turn them on.
ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME 80 PASSWORD_REUSE_MAX 10;
The above says that a user with a default profile can only repeat a password after 80 days or after ten other
passwords (whichever is the most restrictive). This still works well in 12c, as I have tested it below for you.
SQL> alter user pwtest identified by asecure123456password replace asecure12password;
alter user pwtest identified by asecure123456password replace asecure12password
*
ERROR at line 1:
ORA-28007: the password cannot be reused
SQL> desc sys.user_history$;
Name Null? Type
----------------------------------------- -------- ---------------
USER# NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
PASSWORD_DATE DATE
SQL> set wrap off
SQL> set linesize 150
SQL> column PASSWORD_DATE format a25;
SQL> column PASSWORD format a25;
SQL> select * from sys.user_history$;
