User and Security Administration Oracle Tips by Burleson Consulting

This chapter first addresses user administration. Database access begins with the creation of users, which are then assigned specific rights to perform actions either directly or through roles. The rights to perform actions are called system and object privileges. System privileges are rights to perform actions in the database; object privileges are access rights to an object (table, index, synonym, etc.) within the database.

This chapter then covers Oracle8, Oracle8i, and Oracle9i security-specific topics, such as the use of profiles to control resource usage; password security, contexts, and row-level security; and resource plans and their use in apportioning CPU and parallel server resources.

Administration of Users

In order to access your database, an account must be created in the Oracle database for the user. The exceptions to this are the SYS and SYSTEM users, which are created by Oracle when the database is created. Users can be created, altered, and dropped.

Creating Users

Before you can create a user, you must have the CREATE USER privilege. You can create users with the Oracle Enterprise Manager GUI, at the command line in SQL*Plus, or in Oracle Server Manager versions prior to 9i. The command syntax for creating a user is:

CREATE USER user IDENTIFIED BY password|EXTERNALLY
[GLOBALLY AS 'external_name']
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA  n [K|M]|UNLIMITED ON tablespace]
[PROFILE  profile]
[DEFAULT ROLE role_list|ALL [EXCEPT role_list]|NONE]
[PASSWORD EXPIRE]
[ACCOUNT LOCK|UNLOCK]
  
For example:

CREATE USER  james  IDENTIFIED BY   abc1
DEFAULT TABLESPACE  users
TEMPORARY TABLESPACE  temp
QUOTA  1M ON  users
QUOTA unlimited ON temp
PROFILE  enduser
DEFAULT ROLE fin_app_user
PASSWORD EXPIRE;

You need to assign each new user a password or indicate that operating system authentication will be used. Passwords are stored in the database in encrypted format and cannot be read by any user. The use of operating system authentication means that once your user has logged in at the operating system level, no user name or password will be required when logging in to the Oracle database. Users not assigned an Oracle password are designated as IDENTIFIED EXTERNALLY. Oracle depends upon the operating system for authentication of the user. In order to use external authentication, you must set the OS_AUTHENT_PREFIX in the database parameter file.

Password security features were added to Oracle in versions 8 and 8i. In the example, the user’s password has been set to expired. This will require the user to reenter a different password on first login. By using a PROFILE with password security features enabled, we can force users to change passwords on a frequent basis and limit password reuse and password complexity.

When you create a user, you can designate a specific tablespace as the DEFAULT tablespace for that user. The designation of a default tablespace means that all the objects created by that user will be placed in that tablespace unless the user specifically indicates that the database object be placed in another tablespace. If no default tablespace is indicated for a user, the SYSTEM tablespace will be the default for that user.

Note:  I can’t caution strongly enough not to allow users to have the SYSTEM tablespace as their temporary or default tablespace assignment. The only users that may have SYSTEM as their default tablespace are SYS and certain special users (such as DBSNMP) created by Oracle install scripts. All other users, including SYSTEM, should have default and temporary tablespaces other than SYSTEM. The SYS user should have a different temporary tablespace  from SYSTEM.

When you create a user, you can also designate a specific tablespace to be the TEMPORARY TABLESPACE. This designation specifies the tablespace that will be used for any database actions that require the use of a workspace for the storage of intermediate results for actions such as sorting.

If no temporary tablespace is indicated for a user, the system tablespace will be used. When you designate a default tablespace, temporary tablespace, or quota on a tablespace, this does not implicitly grant any system or object privileges. You can give a user permission to create objects in tablespaces with the QUOTA clause.

To allow a user to create objects in a tablespace, you need to specify a quota for that user on that tablespace. The tablespace quota may be limited to a specific amount of kilobytes or megabytes or may be designated as unlimited. A quota of unlimited indicates that the user can have any portion of a tablespace that is not already in use by another user. If the user is not assigned the UNLIMITED TABLESPACE system privilege, and the assigned limit is reached, the user will no longer be able to create additional objects or insert rows into any objects he or she owns in that tablespace.

Note: The role RESOURCE automatically grants UNLIMITED TABLESPACE, so only use it when absolutely required. A user’ s temporary tablespace assignment does not require a quota grant for the user to use it.

The Remote DBA_TS_QUOTAS view provides tablespace quota information for all users in the database. The USER_TS_QUOTAS view provides tablespace quota information for the current user. When you query Remote DBA_TS_QUOTAS or USER_TS_QUOTAS, a designation of 1 in the max_bytes and max_blocks columns indicates that the user has an unlimited quota on that tablespace.

Altering Users

To create a user, you must have the ALTER USER privilege. You can alter users with the Oracle Enterprise Manager GUI or at the command line in SQL*Plus or Server Manager (SVRMGRL). The command-line syntax for altering a user is:

ALTER user IDENTIFIED  BY password|EXTERNALLY|VALUE
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESAPCE tablespace]
[QUOTA    n [K|M|]|[UNLIMITED] ON tablespace]
[PROFILE  profile]
[DEFAULT ROLE role_list|ALL[EXCEPT rolelist]|NONE]
[PASSWORD EXPIRE]
[ACCOUNT LOCK|UNLOCK]

For example:

ALTER USER ault IDENTIFIED BY  xyz2
DEFAULT TABLESPACE  users
TEMPORARY TABLESPACE temp
QUOTA 2M ON user_data
QUOTA UNLIMITED ON temp
PROFILE  appuser
DEFAULT ROLE ALL
ACCOUNT LOCK;

Once a user has been created, the only thing that you cannot alter for that user is the user name. The password, default tablespace, temporary tablespace, and the quota on a tablespace, profile, default role, status, and password expiration can all be altered by someone with the ALTER USER system privilege.

Each user can alter the Oracle password you initially assigned to that user upon creation, provided that user is not identified externally (via the operating system). In addition to the end user, users with the ALTER USER system privilege can issue the ALTER USER command to change the user’s password. The use of operating system authentication can also be changed by a user with the ALTER USER system privilege. Any changes to the password will take effect the next time that user logs in to Oracle.

When you change the default tablespace for a user, all future objects created by that user will be created in the new default tablespace you designated (unless otherwise specified by the user at the time the object was created). Remember, the user must have a quota in the tablespace to create new objects in that tablespace. If a user reaches the maximum number of bytes assigned (quota), only a user with the ALTER USER system privileges will be able to increase the quota limit for the user.

The undocumented keyword VALUE allows you to specify the encrypted value of a user’s password. This can be handy if the Remote DBA needs to temporarily become a user. You simply capture the encrypted value of the user’s password from the SYS.USER$ table, alter the user to a password you know, do what you need to do, then reset the password using this command:

 ALTER USER username IDENTIFIED BY VALUE ‘encrypted_password’;

Dropping Users

In order to drop a user, you must have the DROP USER system privilege. You can drop users with Server Manager or at the command line in SQL*Plus. The command-line syntax for dropping a user is:

DROP USER user [CASCADE]

For example:

DROP USER scott CASCADE;     

If a user owns any database objects, you can only drop that user by including the CASCADE keyword in the DROP USER command. The DROP USER command with the CASCADE keyword will drop the user and all objects owned by that user. If you are using Oracle Enterprise Manager (OEM) to drop a user, you need to indicate that the associated schema objects be included in the command to drop the user. If a user owns objects and you fail to include CASCADE, you will receive an error message and the user will not be dropped. If a user is currently connected to the database, you cannot drop that user until he or she exits. Once a user has been dropped, all information on that user and all objects owned by that user are removed from the database.

Once you have issued the command to drop a user, you cannot perform a rollback to re-create the user and his or her objects. DROP USER is a DDL command and DDL commands cannot be rolled back.

If you need the objects created by that user, instead of dropping the user, you can revoke the CREATE SESSION system privilege to prevent the user from logging on. You can also copy the objects to another user by importing the objects from an export made before the user was dropped. In order to avoid the problem of dropping a user without losing your application tables, all application tables should be owned by a separate application schema instead of an actual database user schema.

See Code Depot for Full Scripts

---

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.