As mentioned earlier, starting with Oracle Database 11
g
, password case-sensitivity is enforced. You can disable this feature by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE: SQL> alter system set sec_case_sensitive_logon = FALSE;
However, doing so isn’t recommended. For most security requirements, you should have passwords that are case-sensitive.
Also keep in mind that it’s possible to modify the code used to create the password-verification function. For example, you can open and modify the script used to create this function: $ vi $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
If you feel that the Oracle-supplied verification function is too strong or overly restrictive, you can create your own function and assign it to the appropriate database profiles.
Logging On as a Different User
This section details how to log on to a different user without having the clear-text form of the user’s password. You may wish to do this in a couple of situations:
• You’re copying a user from one environment (such as production) to a different environment (such as test), and you want to retain the original password.
• You’re working in a production environment, and you need to be able to connect as the user who owns objects to execute CREATE TABLE statements, issue grants, and so on. In a production environment, you may not know the user’s password because of poor maintenance procedures.
You need access to a DBA-privileged account to be able to log on as a different user without knowing the password. Here are the steps to do this:
1. As a DBA, temporarily store a user’s encrypted password.
2. Change the user’s password.
3. Connect to the user with the new password, and run Data Definition Language (DDL) statements.
4. Connect as a DBA, and change the password back to the original.
Be very careful before changing a user’s password as describe in the previous steps. First, the application can’t connect to the database while the password has been changed to a temporary setting.
If the application repeatedly fails to successfully connect, this may lock the account when you exceed the FAILED_LOGIN_ATTEMPTS limit of a user’s profile (the default is 10 failed attempts before the user is locked).
Furthermore, if you’ve modified the values of PASSWORD_REUSE_MAX (the number of days before a password can be reused) and PASSWORD_REUSE_TIME (the number of times a password must change before a password can be reused), then you can’t reset the password back to its original value.
120
CHAPTER 6 ■ USERS AND BASIC SECURITY
Listed next is an example that shows how to temporarily change a user’s password and then set the password back to its original value. First, select the statement required to restore a user’s password to what it’s currently set to. In this example, the username is APPUSR:
select 'alter user appusr identified by values ' ||
'''' || password || '''' || ';'
from user$ where name='APPUSR';
Here is the output for this example:
SQL> alter user appusr identified by values 'A0493EBF86198724';
Now, modify the user’s password to a known value (in this example, foo): SQL> alter user appusr identified by foo;
Connect to the APPUSR user:
SQL> conn appusr/foo
After you’re finished using the APPUSR user, change its password back to the original value: SQL> alter user appusr identified by values 'A0493EBF86198724';
Again, be very cautious when performing this procedure, because you don’t want to put yourself in a situation where a password-profile setting won’t allow you to reset the password: ORA-28007: the password cannot be reused
If you get this error, one option is to set the password to a brand-new value. However, doing so may have an undesirable impact on the application. If developers have hard-coded the password into response files, the application can’t log on without changing the hard-coded password to the new password.
Your other option is to temporarily change the user’s profile to allow the password to be reused.
First, check to see what the current profile is for the user:
SQL> select username, profile from dba_users where username = UPPER('&&username'); Here is some sample output:
USERNAME PROFILE
------------------------------ ------------------------------
APPUSR SECURE
Now, create a profile that specifically allows a password to be reused without any restrictions: CREATE PROFILE temp_prof LIMIT
PASSWORD_REUSE_MAX unlimited
PASSWORD_REUSE_TIME unlimited;
Next, assign the user the profile that doesn’t limit the reuse of passwords: SQL> alter user appusr profile temp_prof;
You should be able to modify the password as shown previously:
SQL> alter user appusr identified by values 'A0493EBF86198724';
If successful, you see this message:
User altered.
121
CHAPTER 6 ■ USERS AND BASIC SECURITY
Make sure you set the profile back to the original value for the user: SQL> alter user appusr profile secure;
Finally, drop the temporary profile so it isn’t accidentally used in the future: SQL> drop profile temp_prof;
Modifying Users
Sometimes you need to modify existing users for the following types of reasons:
• To change a user’s password
• To lock or unlock a user
• To change the default permanent and/or temporary tablespace
• To change a profile or role
• To change system or object privileges
• To modify quotas on tablespaces
Use the ALTER USER statement to modify users. Listed next are several SQL statements that modify a user. This example changes a user’s password using the IDENTIFIED BY clause: SQL> alter user inv_mgmt identified by i2jy22a;
If you don’t set a default permanent tablespace and temporary tablespace when you initially create the user, you can modify then after creation as shown here:
SQL> alter user inv_mgmt default tablespace users temporary tablespace temp; This example locks a user account:
SQL> alter user inv_mgmt account lock;
And this example alters the user’s quota on the USERS tablespace:
SQL> alter user inv_mgmt quota 500m on users;
Dropping Users
Before you drop a user, I recommend that you first lock the user. Locking the user prevents anybody from connecting to a locked database account. This allows you to determine better whether someone is using the account before it’s dropped. Here’s an example of locking a user: SQL> alter user heera account lock;
Any user or application attempting to connect to this user now receives the following error: ORA-28000: the account is locked
To view the users and lock dates in your database, issue this query:
SQL> select username, lock_date from dba_users;
122
CHAPTER 6 ■ USERS AND BASIC SECURITY
To unlock an account, issue this command:
SQL> alter user heera account unlock;
Locking users is a very handy technique for securing your database and discovering which users are active.
Be aware that by locking a user, you aren’t locking access to a user’s objects. For example, if a USER_A has select, insert, update, and delete privileges on tables owned by USER_B, if you lock the USER_B account, USER_A can still issue Data Manipulation Language (DML) statements against the objects owned by USER_B.
To determine whether the objects are being used, see the auditing section of Chapter 22.
■
Tip
If a user’s objects don’t consume inordinate amounts of disk space, then before you drop the user, it’s prudent to make a quick backup. See Chapter 13 for details on using Data Pump to back up a single user.
After you’re sure that a user and its objects aren’t needed, use the DROP USER statement to remove a database account. This example drops the user HEERA:
SQL> drop user heera;
The prior command won’t work if the user owns any database objects. Use the CASCADE clause to remove a user and have its objects dropped:
SQL> drop user heera cascade;
■
Note
The DROP USER statement may take an inordinate amount of time to execute if the user being dropped owns a vast number of database objects. In these situations, you may want to consider dropping the user’s objects before dropping the user.
When you drop a user, any tables that it owns are also dropped. Additionally, all indexes, triggers, and referential-integrity constraints are removed. If referential integrity constraints exist in other schemas that depend on any dropped primary-key and unique-key constraints, the referential constraints in other schemas are also dropped. Oracle invalidates but doesn’t drop any views, synonyms, procedures, functions, and packages that are dependent on the dropped user’s objects.
Enforcing Password Security and Resource Limits
When you’re creating users, sometimes requirements call for passwords to adhere to a set of security rules. For example, the password must be of a certain length and contain numeric characters. Also, when you set up database users, you may want to ensure that a certain user isn’t capable of consuming inordinate amounts of CPU resources.
You can use a database profile to meet these types of requirements. An Oracle
profile
is a database object that serves two purposes:
123
CHAPTER 6 ■ USERS AND BASIC SECURITY
• Enforcing password security settings
• Limiting system resources that a user consumes
These topics are discussed in the next two subsections.
Implementing Password Security
When you create a user, if no profile is specified, the DEFAULT profile is assigned to the newly created user. To view the current settings for a profile, issue the following SQL: SQL> select profile, resource_name, resource_type, limit from dba_profiles; Here is a partial listing of the output:
PROFILE RESOURCE_NAME RESOURCE_T LIMIT
---------- ------------------------- ---------- --------------------
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 10
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
A profile’s password restrictions are in effect as soon as the profile is assigned to a user. For example, from the previous output, if you’ve assigned the DEFAULT profile to a user, that user is allowed only 10 consecutive failed login attempts before the user account is automatically locked by Oracle. See Table 6–1 for a description of the password profile security settings.
■
Tip
See My Oracle Support note 454635.1 for details on Oracle Database 11g DEFAULT profile changes.
You can alter the DEFAULT profile to customize it for your environment. For example, say you want to enforce a cap on the maximum number of days a password can be used. The next line of code sets the PASSWORD_LIFE_TIME of the DEFAULT profile to 300 days:
SQL> alter profile default limit password_life_time 300;
124
CHAPTER 6 ■ USERS AND BASIC SECURITY
Table 6–1.
Password Security Settings
Password Setting
Description
11g default
10g default
FAILED_LOGIN_ATTEMPTS
Number of failed login
10 attempts
10 attempts
attempts before the schema is
locked
PASSWORD_GRACE_TIME
Number of days after a
7 days
Unlimited
password expires that the
owner can log in with an old
password
PASSWORD_LIFE_TIME
Number of days a password is
180 days
Unlimited
valid
PASSWORD_LOCK_TIME
Number of days an account is
1 day
Unlimited
locked after
FAILED_LOGIN_ATTEMPTS has
been reached
PASSWORD_REUSE_MAX
Number of days before a
Unlimited Unlimited
password can be reused
PASSWORD_REUSE_TIME
Number of times a password
Unlimited Unlimited
must change before a
password can be reused
PASSWORD_VERIFY_FUNCTION
Database function used to
Null Null
verify the password
The PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX settings must be used in conjunction. If you specify an integer for one parameter (doesn’t matter which one) and then UNLIMITED for the other parameter, the current password can never be reused.
If you want to specify that the DEFAULT profile password must be changed 10 times within 100 days before it can be reused, use a line of code similar to this:
SQL> alter profile default limit password_reuse_time 100 password_reuse_max 10; Although using the DEFAULT profile is sufficient for many environments, you may need tighter security management. I recommend that you create custom security profiles and assign them to users as required. For example, create a profile specifically for application users: CREATE PROFILE SECURE_APP LIMIT
PASSWORD_LIFE_TIME 200
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1
PASSWORD_REUSE_MAX 1
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;
125
CHAPTER 6 ■ USERS AND BASIC SECURITY
After you create the profile, you can assign it to users as appropriate. The following SQL generates a SQL script named alt_prof_dyn.sql that you can use to assign users to the newly created profile: set head off;
spo alt_prof_dyn.sql
select 'alter user ' || username || ' profile secure_app;'
from dba_users where username like '%APP%';
spo off;
Be careful when assigning profiles to application accounts that use the database. If you want to enforce that a password must change at a regular frequency, be sure you understand the impact on production systems. Passwords tend to get hard-coded into response files and code. Enforcing password changes in these environments can wreak havoc as you try to chase down all the places where the password is referenced. If you don’t want to enforce the periodic changing of the password, you can set PASSWORD_LIFE_TIME to a high value such as 10000 (days).