Oracle - Create User

Manually creating a user

You will need to know the system account password, the new user’s name and password, and tablespace.

Connect via sqlplus with system user

$ sqlplus system

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 6 15:36:28 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>

Create the user’s tablespace

Depending on your setup, you may be able to skip this step and use an existing or default tablespace.

In the following example I’ve created a tablespace named su2_tbs.

SQL> Create tablespace su2_tbs datafile 'su2_00_tbs.dbf' size 20M autoextend on;

Tablespace created.

Create user

The script below is to create user su2_owner. On the first line it drops the user su2_owner. This step will fail if the user does not already exist.

The password is set to su2_password.

Some privileges have been granted to su2_owner. Only grant the minimal required.

SQL> Drop user su2_owner cascade;
Drop user su2_owner cascade
      *
ERROR at line 1:
ORA-01918: user 'SU2_OWNER' does not exist


SQL> create user su2_owner identified by su2_password default tablespace su2_tbs quota unlimited on su2_tbs;

User created.

SQL> grant connect,resource to su2_owner;

Grant succeeded.

SQL> Grant create any synonym to su2_owner;

Grant succeeded.