Pro Oracle Database 11g Administration (25 page)

select

name

,to_char(ctime,'dd-mon-yy hh24:mi:ss')

,to_char(ptime,'dd-mon-yy hh24:mi:ss')

,length(password)

from user$

where password is not null

and password not in ('GLOBAL','EXTERNAL')

and ctime=ptime;

In this script, the CTIME column contains the timestamp of when the user was created. The PTIME

column contains the timestamp of when the password was changed. If the CTIME and PTIME are identical, then this indicates that the password has never changed.

You should also check your database to determine whether any accounts are using default passwords. If you’re using an Oracle Database 11
g
or higher, you can check the DBA_USERS_WITH_DEFPWD

view to determine whether any Oracle-created user accounts are still set to the default password: SQL> select * from dba_users_with_defpwd;

If you aren’t using Oracle Database 11
g
, then you have to manually check the passwords or use a script. Listed next is a simple shell script that attempts to connect to the database using default passwords:

#!/bin/bash

if [ $# -ne 1 ]; then

echo "Usage: $0 SID"

exit 1

fi

# source oracle OS variables via oraset script.

# See chapter 2 for more details on setting OS variables.

. /var/opt/oracle/oraset $1

#

userlist="system sys dbsnmp dip oracle_ocm outln"

113

CHAPTER 6 ■ USERS AND BASIC SECURITY

Creating Users

When you’re creating a user, you need to consider the following factors:

• User name

• Authentication method

• Basic privileges

• Default permanent tablespace

• Default temporary tablespace

These aspects of creating a user are discussed in the next few subsections.

Choosing a User Name and Authentication Method

Pick a user name that gives you an idea what application will be using the user. For example, if you have an inventory-management application, a good choice for a user name is INV_MGMT. Choosing a meaningful username helps identify the purpose of a user. This can be helpful especially if a system isn't documented appropriately.

Authentication is the method used to confirm that the user is authorized to use the account. Oracle supports three types of authentication:

• Password

• External service, such as the OS

• Global user via enterprise directory service (Oracle Internet Directory) The most common method of authenticating a user is with a password. The external authentication method allows you to authenticate using smart cards, Kerberos, or the OS. This section shows examples of password verification and external authentication. Refer to the
Oracle Database Security Guide
and the
Oracle Database Advanced Security Administrator’s Guide
(available at http://otn.oracle.com) for more information about external and global authentication methods.

When you’re creating users as a DBA, your account must have the CREATE USER system privilege. You use the CREATE USER SQL statement to create users. This example creates a user named HEERA with the password CHAYA and assigns the default permanent tablespace USERS and the default temporary tablespace TEMP:

create user heera identified by chaya

default tablespace users

temporary tablespace temp;

This creates a bare-bones schema that has no privileges to do anything in the database. To make the user useful, you must minimally grant it the CREATE SESSION system privilege: SQL> grant create session to heera;

If the new schema needs to be able to create tables, you need to grant it additional privileges like CREATE TABLE:

SQL> grant create table to heera;

The new schema also must have quota privileges granted for any tablespace in which it needs to create objects:

115

CHAPTER 6 ■ USERS AND BASIC SECURITY

SQL> alter user heera quota unlimited on users;

■
Note
A common technique is to grant the predefined roles of CONNECT and RESOURCE to newly created schemas.

These roles contain system privileges such as CREATE SESSION and CREATE TABLE (and several others, which vary by release). I recommend against doing this, because Oracle has stated that those roles may not be available in future releases.

You can also create a user that is authenticated by an external service, such as the OS. In this scenario, you assume that if a user has been authenticated by the OS logon, that level of security is also good enough to allow access to the database. External authentication has some interesting advantages:

•

Users with access to the server don’t have to maintain a password to the database.

•

Scripts that log on to the database don’t have to use hard-coded passwords if executed by OS-authenticated users.

•

Another database user can’t hack into the externally identified user by trying to guess the username and password connection string. The only way to log on to an externally authenticated user is from an external authentication source (such as the OS).

•

A DBA doesn’t have to maintain a password for the database user.

When using external OS authentication, Oracle prefixes the value contained in OS_AUTHENT_PREFIX to the OS user connecting to the database. The default value for this parameter is OPS$. Oracle strongly recommends that you set the OS_AUTHENT_PREFIX parameter to a null string. For example: SQL> alter system set os_authent_prefix='' scope=spfile;

You have to stop and start your database for this modification to take effect. After you’ve set the OS_AUTHENT_PREFIX variable, you can create an externally authenticated user. For example, say you have an OS user named jsmith and you want anybody with access to this OS user to be able to log in to the database without supplying a password. Use the CREATE EXTERNALLY statement to do this: SQL> create user jsmith identified externally;

Now, when jsmith logs on to the database server, this user can connect to SQL*Plus as follows: $ sqlplus /

No username or password is required because the user has already been authenticated by the OS.

Assigning Default Permanent and Temporary Tablespaces

When maintaining a database, you should verify the default and temporary tablespace settings to ensure that they meet your database standards. You can view user information by selecting from the DBA_USERS

view:

116

CHAPTER 6 ■ USERS AND BASIC SECURITY

select

username

,password

,default_tablespace

,temporary_tablespace

from dba_users;

Here is some sample output:

USERNAME PASSWORD DEFAULT_TABLESP TEMPORARY_TABLE

-------------------- -------------------- --------------- ---------------

JSMITH EXTERNAL USERS TEMP

DBSNMP SYSAUX TEMP

ORACLE_OCM USERS TEMP

APPQOSSYS SYSAUX TEMP

APPUSR USERS TEMP

None of your users, other than the SYS user, should have a default permanent tablespace of SYSTEM.

You don’t want any users other than SYS creating objects in the SYSTEM tablespace. The SYSTEM tablespace should be reserved for the SYS user’s objects. If other users’ objects existed in the SYSTEM tablespace, you’d run the risk of filling up that tablespace and compromising the availability of your database.

All of your users should be assigned a temporary tablespace that has been created as type temporary. Usually, this tablespace is named TEMP (see Chapter 4 for more details).

You never want any users with a temporary tablespace of SYSTEM. If a user has a temporary tablespace of SYSTEM, then any sort area for which they require temporary disk storage acquires extents in the SYSTEM tablespace. This can lead to the SYSTEM tablespace filling up. You don’t want the SYSTEM

tablespace ever filling up, because that can lead to a nonfunctioning database if the SYS schema can’t acquire more space as its objects grow. To check for users that have a temporary tablespace of SYSTEM, run this script:

SQL> select username from dba_users where temporary_tablespace='SYSTEM'; Typically, I use a script name creuser.sql when creating a user. The script uses variables that define the user names, passwords, default tablespace name, and so on. For each environment (development, test, QA, beta, production) in which the script is executed, you can change the ampersand variables as required for each environment. For example, you can use a different password for each separate environment.

Here’s an example creuser.sql script:

DEFINE cre_user=inv_mgmt

DEFINE cre_user_pwd=inv_mgmt_pwd

DEFINE def_tbsp=inv_data

DEFINE idx_tbsp=inv_index

DEFINE smk_ttbl=zzzzzzz

--

CREATE USER &&cre_user IDENTIFIED BY &&cre_user_pwd

DEFAULT TABLESPACE &&def_tbsp;

--

GRANT CREATE SESSION TO &&cre_user;

GRANT CREATE TABLE TO &&cre_user;

--

ALTER USER &&cre_user QUOTA UNLIMITED ON &&def_tbsp;

ALTER USER &&cre_user QUOTA UNLIMITED ON &&idx_tbsp;

--

-- Smoke test

117

CHAPTER 6 ■ USERS AND BASIC SECURITY

Enforcing Password Security

There are a couple schools of thought about enforcing password security:

• Use easily remembered passwords so you don’t have them written down or recorded in a file somewhere. Because the passwords aren’t sophisticated, they aren’t very secure.

• Enforce a level of sophistication for passwords. Such passwords aren’t easily remembered and thus must be recorded somewhere, which isn’t secure.

You may choose to enforce a degree of password sophistication because you think it’s the most secure option. Or you may be required to enforce password security by your corporate security team (and thus have no choice in the matter). This section isn’t about debating which of the prior methods is preferable. Should you choose to impose a degree of sophistication (strength) for a password, this section describes how to enforce the rules.

You can enforce a minimum standard of password complexity by assigning a password-verification function to a user’s profile. Oracle supplies a default password-verification function that you create by running the following script as the SYS schema:

SQL> @?/rdbms/admin/utlpwdmg

Function created.

Profile altered.

Function created.

For Oracle Database 11
g
, set the PASSWORD_VERIFY_FUNCTION of the DEFAULT profile to VERIFY_FUNCTION_11G:

SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION verify_function_11G; For Oracle Database 10
g
, set the PASSWORD_VERIFY_FUNCTION of the DEFAULT profile to VERIFY_FUNCTION:

SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION verify_function; If for any reason you need to back out of the new security modifications, run this statement to disable the password function:

SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION null;

When enabled, the password-verification function ensures that users are correctly creating or modifying their passwords. The utlpwdmg.sql script creates a function that checks a password to ensure that it meets basic security standards such as minimum password length, password not the same as username, and so on. You can verify that the new security function is in effect by attempting to change the password of a user that has been assigned the DEFAULT profile. This example tries to change the password to less than the minimum length:

SQL> password

Changing password for HEERA

Old password:

New password:

Retype new password:

ERROR:

ORA-28003: password verification for the specified password failed

ORA-20001: Password length less than 8

Password unchanged

119

CHAPTER 6 ■ USERS AND BASIC SECURITY

■
Note
For Oracle Database 11g, the minimum password length is eight characters. For Oracle Database 10g, the minimum length is four characters.