| ☰ See All Chapters |
How to create a user in Oracle
In this tutorial, you will learn how to use the Oracle CREATE USER statement to create a new user in the Oracle database. Oracle user owns all your database objects. SYS, SYSTEM and PDBADMIN are the default users created with sysdba privilege when you install oracle. However, even if the user exists, it is good practice to create a new user for the target database and assign the sysdba privilege.
It is not good practice to use same schema for different applications. Using same schema can easily result in damage to your data and the objects themselves. Oracle Database automatically creates a schema when you create a user. This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions. Hence it is better to define application users and grant those users the necessary privileges on the schema objects.
The basic syntax of the CREATE USER statement is as follows:
CREATE USER username IDENTIFIED BY password [DEFAULT TABLESPACE tablespace] [QUOTA {size | UNLIMITED} ON tablespace] [PROFILE profile] [PASSWORD EXPIRE] [ACCOUNT {LOCK | UNLOCK}]; GRANT ROLE TO username; |
CREATE USER username | Name of the user to be created. |
IDENTIFIED BY password | Password for the user to use to log on to the database. |
DEFAULT TABLESPACE | Specify the tablespace of the objects such as tables and views that the user will create. If it is not specified then user’s objects will be stored in the database default tablespace if available, typically it is USERS tablespace; or the SYSTEM tablespace in case there is no database default tablespace. |
QUOTA | Specify the maximum of space in the tablespace that the user can use. You can have multiple QUOTA clauses, each for a tablespace. Use UNLIMITED if you don’t want to restrict the size in the tablespace that user can use. By default, a user has no quota on any tablespace in the database. |
PROFILE profile | A user profile limits the database resources or password that the user cannot exceed. You can assign a profile to a newly created user. If you skip this clause, Oracle will assign the DEFAULT profile to the user. |
PASSWORD EXPIRE | Use the PASSWORD EXPIRE if you want to force the user to change the password for the first time the user logs in to the database. |
ACCOUNT {LOCK | UNLOCK} | Use ACCOUNT LOCK if you want to lock user and disable access. On the other hand, specify ACCOUNT UNLOCK to unlock user and enable access. |
GRANT ROLE TO username | GRANT is used assign privileges to the user. GRANT is a very powerful statement with many possible options, but the core functionality is to manage the privileges of both users and roles throughout the database. |
Oracle CREATE USER example
sqlplus / AS SYSDBA ALTER SESSION SET CONTAINER=XEPDB1; DROP USER STUDY CASCADE; create user STUDY identified by STUDY default tablespace USERS temporary tablespace TEMP profile DEFAULT; GRANT DBA TO STUDY; Quit; |
All Chapters
