BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








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:

[GLOBALLY AS 'external_name']
[QUOTA  n [K|M]|UNLIMITED ON tablespace]
[PROFILE  profile]
[DEFAULT ROLE role_list|ALL [EXCEPT role_list]|NONE]
For example:

QUOTA  1M ON  users
QUOTA unlimited ON temp
PROFILE  enduser
DEFAULT ROLE fin_app_user

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:

[QUOTA    n [K|M|]|[UNLIMITED] ON tablespace]
[PROFILE  profile]
[DEFAULT ROLE role_list|ALL[EXCEPT rolelist]|NONE]

For example:

QUOTA 2M ON user_data
PROFILE  appuser

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:


For example:


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.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter