Oracle GRANT Statement

Open SQL Shell

Open a new SSH session and login as oracle user
Open sqlplus shell
# sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 24 23:26:47 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL>

GRANT Statements

GRANT statement Basic Syntax
GRANT {system_privileges | object_privileges } TO user [WITH ADMIN OPTION]

Following are the various options for "system_privileges"

CREATE SESSION CREATE TABLE CREATE VIEW CREATE PROCEDURE

Following are the various options for "object_privileges"

INSERT UPDATE DELETE INDEX EXECUTE
GRANT "CREATE SESSION" privileges to user john

Provide CREATE SESSION system privilege to user john to enable the user to log in the Oracle database.

SQL> GRANT CREATE SESSION TO john; Grant succeeded.
GRANT "CREATE TABLE" privileges to user john

With Session Privilege granted now, user john should be able to access sqlplus shell.

SQL> GRANT CREATE TABLE TO john; Grant succeeded.
GRANT "CREATE TABLE" ADMIN privileges to user john

With ADMIN CREATE TABLE Privilege, user john can now GRANT CREATE TABLE Privilege to other users as well.

SQL> GRANT CREATE TABLE TO john WITH ADMIN OPTION; Grant succeeded.