As a database administrator, you may need to give someone else access to the database without giving them full control. For instance, you hire developers to maintain your databases, but you don’t want to give them the ability to access, delete or modify sensitive information.
In this case, you must give them access to the database through a non-administrative account. This way, you will be able to keep track of what developers can and cannot do with your data.
In this guide, you’ll learn how to create a user in Oracle Database and grant him privileges to connect to the database. We’ll cover different user creation options and we’ll also provide some illustration examples..
Oracle CREATE USER Syntax
To create a new user in Oracle, you must be a DBA or be granted the CREATE USER system privilege.
Syntax
CREATE USER username
IDENTIFIED BY password
[DEFAULT TABLESPACE tablespace_name]
[TEMPORARY TABLESPACE temp_ts_name]
[QUOTA UNLIMITED|size ON tablespace_name]
[PROFILE user_profile]
[PASSWORD EXPIRE]
[ACCOUNT LOCK|UNLOCK];Note: Attributes in brackets are optional and are assigned default values when omitted.
Oracle CREATE USER attributes
| Attribute | Description | Default value |
IDENTIFIED BY | allows Oracle to authenticate the user with a password to grant him access to the database | N/A |
DEFAULT TABLESPACE | indicates the tablespace segments in which Oracle stores the user data | users |
TEMPORARY TABLESPACE | specifies a temporary tablespace segments for the user | temp |
QUOTA | allows the user to store his data in others tablespace with a well-defined quota, in addition to his own tablespace | UNLIMITED (on user’s own tablespace) |
PROFILE | database profile you want to assign to the user | DEFAULT |
PASSWORD EXPIRE | forces the user to change his password before Oracle grants him access to the database | If omitted, password doesn’t expire |
ACCOUNT LOCK|UNLOCK | tells Oracle if the user can access his account or not | UNLOCK |
Oracle CREATE USER Examples
Let’s illustrate with some examples.
Example 1:
The following example creates a database user account alekciss with password OraclE19$.
CREATE USER alekciss
IDENTIFIED BY OraclE19$;
Note: Any missing attribute takes a default value.
Example 2:
The following example creates a user account and specifies the user password, default tablespace and tablespace quotas. Moreover, we force the user to change his password at the first successful login attempt.
CREATE USER aciss IDENTIFIED BY devApp$14 DEFAULT TABLESPACE dev_ts QUOTA 10M ON dev_ts QUOTA 700K on users PASSWORD EXPIRE;
Now let’s try to connect to the database with user aciss.
The database login attempt returns the following error message:
ORA-01045: user username lacks CREATE SESSION PRIVILEGE; logon denied.
Let’s fix it quickly.
Granting privileges to a user
The scope of a user in Oracle can be very limited. In fact, a newly created user cannot perform any action on the database, even connect to his own account.
As a DBA, you must assign a number of privileges to the user to allow him to interact with part of the database. This efficient privilege system allows you to control user actions on the database.
To assign a privilege to a user in Oracle, you can use the GRANT statement, followed by the list of privileges to be granted.
Example:
After creating a user, you may want to grant him the user the CREATE SESSION privilege, to log in to a database session.
GRANT CREATE SESSION TO aciss ;
Let’s try again to connect to the database with the user aciss.
Altering a user in Oracle Database
You can easily alter an Oracle user account by modifying its attributes with the ALTER USER system privilege. Changing user domain affects the future user sessions, not current sessions.
The following example modify the user’s password, tablespace, quotas and lock the account.
ALTER USER aciss IDENTIFIED BY FormerDevApp$14 DEFAULT TABLESPACE users QUOTA 1M ON dev_ts ACCOUNT LOCK;
Dropping a user in Oracle Database
In some occasions, you may need to remove a user account from the database. To do so, you must have the DROP USER system privilege.
When you drop a user account, Oracle Database removes the user account and associated schema from the data dictionary. It also immediately drops all schema objects contained in the user schema, if any.
Example
DROP USER aciss CASCADE;
You might also like: Install Oracle Database Client Step by Step
