Pro Oracle Database 11g Administration (26 page)

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).