Oracle Create User

Onur ARDAHANLI

2 years ago

In today’s article, I will explain how to create a user. The person who will create a user in the database must have “CREATE USER” system privilege.

When creating a user:

What will be the username,

powers to be given,

What will be the default Tablespace,

We have to consider what the default Temprorary Tablespace will be.

The minimum authorization to be given to the user is the “CREATE SESSION” authorization. Otherwise, the user cannot connect to the database.

SQL SENTENCES

SQL> CREATE USER BURCU IDENTIFIED BY "Changeme1" DEFAULT TABLESPACE HE_USERS TEMPORARY TABLESPACE TEMP PROFILE AYM_USER_PROFILE ACCOUNT UNLOCK PASSWORD EXPIRE;

SQL> GRANT CREATE SESSION TO BURCU;

SQL> GRANT RESOURCE TO BURCU; --With Resource, the user is given the authority to create a table under the schema.

SQL> GRANT DBA TO BURCU; -- The user is given DBA authority.

SQL> CREATE USER BURCU IDENTIFIED BY "Changeme1" DEFAULT TABLESPACE HE_USERS TEMPORARY TABLESPACE TEMP PROFILE AYM_USER_PROFILE ACCOUNT UNLOCK PASSWORD EXPIRE;

SQL> GRANT CREATE SESSION TO BURCU;

SQL> GRANT RESOURCE TO BURCU; --With Resource, the user is given the authority to create a table under the schema.

SQL> GRANT DBA TO BURCU; -- The user is given DBA authority.

NOTES

If the SIGN user creates a new object and does not specify a special tablespace with the word “TABLESPACE”, USERS is created in the tablespace by default.