Dear Readers,
My name is Franz Devantier, creator of this blog. I am an Oracle Certified
Professional (OCP DBA 11g) Security DBA.
I will be sharing with you the basic duties of an Oracle DBA, and also
some of the undocumented, and not so well known tasks.
I will make a deal with you: If you refer me to a company that needs
database support, from a few hours per week to full time, and I am able to sign
a contract with them.
Then I will give you 10% of the monthly
contract or deal price every month. When
the contract ends, and we re-sign the contract, I will again give you 10% of
the monthly contract price. This will go
on until the company no longer employs or contracts me or my agents to look
after their databases.
I can do this, because that 10% is my
marketing budget. When we re-sign the
contract, in the future, it may depend on you giving the thumbs up again, and
that is worth 10% of the monthly contract price, to be given to you as
commission.
Contact:
Franz
Security
Policies - Part 5
Password Management Policy
If the database security depends on passwords, then the
passwords must be kept secret at all times.
Passwords are vulnerable to theft, forgery, and misuse. The Oracle Database server uses a password
management policy. Security officers and
DBA’s control the password management policy through user profiles, which
enables a greater control over the database security.
You can use the CREATE PROFILE statement to create a user
profile.
SQL> CREATE PROFILE demo_profile LIMIT
2 SESSIONS_PER_USER UNLIMITED
3 CPU_PER_SESSION UNLIMITED
4 CPU_PER_CALL 3000
5 CONNECT_TIME 45
6 LOGICAL_READS_PER_SESSION DEFAULT
7 LOGICAL_READS_PER_CALL 1000
8 PRIVATE_SGA 15K
9 COMPOSITE_LIMIT 5000000;
Profile created.
SQL>
The
profile is assigned to the user with the CREATE USER or ALTER USER statement.
SQL>
ALTER USER fred PROFILE demo_profile;
User
altered.
SQL>
Account Locking
When a user exceeds a designated number of failed login
attempts, then the Oracle Server automatically locks that user account. In a similar way you can specify the amount
of time in days that accounts remain locked.
SQL> CREATE PROFILE demo_profilex LIMIT
2 FAILED_LOGIN_ATTEMPTS 4
3 PASSWORD_LOCK_TIME 30;
Profile created.
SQL>
SQL>
ALTER USER fred PROFILE demo_profilex;
User
altered.
SQL>
If the account does become locked, it will automatically
become unlocked again after 30 days, unless it is manually unlocked by the DBA
prior to the 30 days completing. If you
had not specified a time interval for unlocking the account, then
PASSWORD_LOCK_TIME would assume the value in the default profile. If you specify PASSWORD_LOCK_TIME UNLIMITED,
then you will need to explicitly unlock the account with the ALTER USER
statement.
SQL> ALTER USER fred ACCOUNT UNLOCK;
User altered.
SQL>
After a user successfully logs into an account, the
unsuccessful login attempt count for the user, is reset to 0.
The security officer can also explicitly lock the account. If the account is explicitly locked, then it
can only be unlocked again explicitly or manually.
SQL> ALTER USER fred ACCOUNT LOCK;
User altered.
SQL>
Password Aging and Expiration
You can use the CREATE PROFILE or ALTER PROFILE statement
to specify a maximum lifetime for passwords.
SQL> ALTER PROFILE demo_profile LIMIT PASSWORD_LIFE_TIME
90 PASSWORD_GRACE_TIME 7;
Profile altered.
SQL>
This will result in the password expiring after 90 days,
and the user or the DBA will have to change the password. You could have also specified the grace
period in days, during which you can still access the database, but in which
time you can change the password. After
the end of this grace period, you will need to ask the DBA to activate your
account.
SQL> ALTER USER fred IDENTIFIED BY fred;
User altered.
SQL> connect fred/fred
Connected.
SQL>
Chronology Process of Password
Lifetime and Grace Period
Setting the PASSWORD_LIFE_TIME Profile Parameter to a Low
Value
There may be some issues when you set the PASSWORD_LIFE_TIME
parameter of the CREATE PROFILE or ALTER PROFILE statement to a low value, for
example to 1 day.
If the user is logged in when you make this change, then
the users account status will change from OPEN to EXPIRED, with the ORA-28002
message. You can find a list of the
currently logged in user by querying the USERNAME column of the v$session view.
-> If the user is not logged on, when you set
PASSWORD_LIFE_TIME to a low value then the users account status does not
change, when the user logs in.
-> When you set the profile value to PASSWORD_LIFE_TIME
UNLIMITED, this will only affect accounts that have not yet entered their grace
period. The user must change their
password, if they are already in the grace period.
Password
History
There are
two parameters that control the user’s ability to reuse an old password.
Parameter Name
|
Description and Use
|
PASSWORD_REUSE_TIME
|
Requires either of the following:
-> A number specifying how many days, or fraction of a day,
between the first use, and next use of a password.
-> UNLIMITED
|
PASSWORD_REUSE_MAX
|
Requires either of the following:
-> A number (integer) to specify the number of
password changes required before a password can be reused.
-> UNLIMITED
|
If neither of the above parameters are specified, then the
user can reuse passwords at any time, which is a security issue.
If neither of the two parameters is specified as UNLIMITED,
then password reuse is allowed, but only after meeting both conditions.
If both parameters are set to UNLIMITED, then Oracle
ignores both, and the user can reuse any password at any time.
If you specify DEFAULT for either of the parameters, then
Oracle uses the value defined in the DEFAULT profile, which may set all
parameters to UNLIMITED. Oracle by
design will use UNLIMITED for any parameter specified as DEFAULT, unless you
change the setting for that parameter in the DEFAULT profile.
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment