Creating Users   «Prev  Next»

Lesson 2Managing Users
Objective List Commands used to manage Users

Managing Users in Oracle

There are three primary commands used to manage users[1] in an Oracle database. In the context of this module, the term "manage" is used to denote the processes of creating, modifying, and deleting users. The three commands are:
CREATE USER The CREATE USER command is used when you want to add a new user to your database. It allows you to specify a username, a password, default and temporary tablespaces, and other information about a user.
ALTER USER The ALTER USER command is very similar to CREATE USER and allows you to make any necessary changes to a user definition after it has been created. One frequent use of ALTER USER is to change and reset passwords.
DROP USER The DROP USER command is used when you want to completely remove a user from your database.


Purpose: Use the CREATE USER statement to create and configure a database user, which is an account through which you can log in to the database, and to establish the means by which Oracle Database permits access by the user. You can enable a user to connect to the database through a proxy application or application server. For syntax and discussion, refer to ALTER USER.
Prerequisites: You must have the CREATE USER system privilege. When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle Database, a user must have the CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION system privilege. This name can contain only characters from your database character set and must follow the rules described in the section Schema Object Naming Rules. Oracle recommends that the user name contain at least one single-byte character regardless of whether the database character set also contains multibyte characters.


Purpose: Use the ALTER USER statement:
  1. To change the authentication or database resource characteristics of a database user
  2. To permit a proxy server to connect as a client without authentication

Prerequisites: You must have the ALTER USER system privilege. However, you can change your own password without this privilege.
Semantics: The keywords, parameters, and clauses described in this section are unique to ALTER USER or have different semantics than they have in CREATE USER. Keywords, parameters, and clauses that do not appear here have the same meaning as in the CREATE USER statement.

Oracle Drop User Cascade

In order to drop a user, you must have the Oracle DROP USER system privilege. The command line syntax for dropping a user can be seen below:
If a user owns any database objects, that user can only be dropped with the Oracle DROP USER CASCADE command. The Oracle DROP USER CASCADE command drops a user and all owned objects. The user will not be dropped and an error message will be returned if you a user owns objects and you fail to use the Oracle DROP USER CASCADE command.
As the DROP USER CASCADE command is a DDL command, after the Oracle DROP USER CASCADE command has been executed, a rollback to re-create the user and his objects cannot be completed.

[1]user: Specify the name of the user to be created.