How to Create a User and Grant Permissions in Oracle

In this post, Learn how to create a user in oracle and How to provide grant permissions to the user in oracle.

How to Create a User in Oracle

Before creating a user in oracle, you must have admin access or system privilege to create new user and to create session.

Connect to Oracle as sysdba using following command. We are running this tutorial in Oracle 18C version.

H:\>sqlplus / as sysdba

Output:

H:\>sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Apr 16 12:47:26 2019
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL>

Only a user authenticated AS SYSASM/SYSDBA to create the user.

Creating User in Oracle:

Once connected to System user then execute "CREATE USER" command syntax as below.

CREATE USER <USER_NAME> IDENTIFIED BY <PASSWORD>;

For above syntax, we must know the user name to be created and password. To create user, we must know atleast username and password.

SQL> CREATE USER javaw3schools IDENTIFIED BY java123;

User created.

Here username is javaw3schools and password is java123. Before running this SQL query you must know each keyword in the create statement.

CREATE USER:

CREATE USER tells to Oracle that creating a new user. This checks the provided user is already exists or not.

Username: 

Specify the name of the user to be created. This name can contain only characters from your database character set and must follow the rules described in the section "Schema Object Naming Rules".

IDENTIFIED Clause:

The IDENTIFIED clause lets you indicate how Oracle Database authenticates the user. This can be done in 3 ways.

1. BY password - The BY password clause lets you creates a local user and indicates that the user must specify password to log on to the database. Passwords are case sensitive.
2. EXTERNALLY Clause - to create an external user. Such a user must be authenticated by an external service, such as an operating system or a third-party service.
3. GLOBALLY Clause - The GLOBALLY clause lets you create a global user. Such a user must be authorized by the enterprise directory service (Oracle Internet Directory).

ORA-65096: invalid common user or role name:

Before executing the create user statement we must alter session to enable to execute the scripts.

SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

If we do not run the above statement, it will throw error "ORA-65096: invalid common user or role name" as below.

SQL> CREATE USER javaw3schools IDENTIFIED BY java123;
CREATE USER javaw3schools IDENTIFIED BY java123
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Grant Permissions in Oracle to the new user:

Once the new user is created then we must provide the privileges and roles to the new user "javaw3schools" to create tables etc. The fist rule is required to grant connection establishment to DB using "CONNECT" role as below SQL.

SQL> GRANT CONNECT TO javaw3schools;

Grant succeeded.

GRANT is a very powerful with many options such as allocating access to resources, DBA access to the user.

Below SQL is to add CONNECT, RESOURCE and DBA roles to the user. DBA access is very powerful and can be performed any operation including killing or droping the resources.

SQL> GRANT CONNECT, RESOURCE, DBA TO javaw3schools;

Grant succeeded.

Next, Need to provide the access to create a session using GRANT CREATE SESSION. We will also combine that with all privileges using GRANT ANY PRIVILEGES.

SQL> GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO javaw3schools;

Grant succeeded

Once created the new user, space allocation (disk space) for the user is important. This is called Namespace in Oracle. Because all the tables created and data will be using the namespace provided. This is done using "GRANT TABLESPACE" like below.

SQL> GRANT UNLIMITED TABLESPACE TO javaw3schools;

Grant succeeded.