$show=/label

How to Create a User and Grant Permissions in Oracle

SHARE:

Learn how to create a user in oracle and How to provide grant permissions to the user in oracle. SQL> CREATE USER javaw3schools IDENTIFIED BY java123; SQL> GRANT CONNECT, RESOURCE, DBA TO javaw3schools; SQL> GRANT UNLIMITED TABLESPACE TO javaw3schools;

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.

How to Create a User and Grant Permissions in Oracle


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.


COMMENTS

BLOGGER

About Us

Author: Venkatesh - I love to learn and share the technical stuff.
Name

accumulo,1,ActiveMQ,2,Adsense,1,API,37,ArrayList,16,Arrays,7,Bean Creation,3,Bean Scopes,1,BiConsumer,1,Blogger Tips,1,Books,1,C Programming,1,Collection,5,Collections,22,Collector,1,Command Line,1,Compile Errors,1,Configurations,7,Constants,1,Control Statements,8,Conversions,6,Core Java,81,Corona India,1,Create,2,CSS,1,Date,3,Date Time API,4,Dictionary,1,Difference,1,Download,1,Eclipse,2,Efficiently,1,Error,1,Errors,1,Exception,1,Exceptions,3,Fast,1,Files,9,Float,1,Font,1,Form,1,Freshers,1,Function,3,Functional Interface,2,Garbage Collector,1,Generics,4,Git,4,Grant,1,Grep,1,HashMap,1,HomeBrew,2,HTML,2,HttpClient,2,Immutable,1,Installation,1,Interview Questions,5,Iterate,2,Jackson API,3,Java,29,Java 10,1,Java 11,5,Java 12,5,Java 13,2,Java 14,2,Java 8,66,Java 8 Difference,2,Java 8 Stream Conversions,2,java 8 Stream Examples,3,Java 9,1,Java Conversions,11,Java Design Patterns,1,Java Files,1,Java Program,2,Java Programs,65,java.lang,5,java.util. function,1,jQuery,1,Kotlin,10,Kotlin Conversions,3,Kotlin Programs,6,Lambda,1,lang,29,Leap Year,1,live updates,1,Logging,1,Mac OS,2,Math,1,Maven,1,Method References,1,Mockito,1,MongoDB,3,New Features,1,Operations,1,Optional,6,Oracle,5,Oracle 18C,1,Partition,1,Patterns,1,Programs,1,Property,1,Python,2,Quarkus,1,Read,1,Real Time,1,Recursion,2,Remove,2,Rest API,1,Schedules,1,Serialization,1,Servlet,1,Sort,1,Sorting Techniques,8,Spring,2,Spring Boot,23,Spring Email,1,Spring MVC,1,Streams,21,String,58,String Programs,9,String Revese,1,Swing,1,System,1,Tags,1,Threads,10,Tomcat,1,Tomcat 8,1,Troubleshoot,16,Unix,2,Updates,3,util,5,While Loop,1,
ltr
item
JavaProgramTo.com: How to Create a User and Grant Permissions in Oracle
How to Create a User and Grant Permissions in Oracle
Learn how to create a user in oracle and How to provide grant permissions to the user in oracle. SQL> CREATE USER javaw3schools IDENTIFIED BY java123; SQL> GRANT CONNECT, RESOURCE, DBA TO javaw3schools; SQL> GRANT UNLIMITED TABLESPACE TO javaw3schools;
https://2.bp.blogspot.com/-tEV_rcrYj_w/XLbF7-Wu5PI/AAAAAAAABYU/CzTMHhqUXhofgn1-zTAMfPRs5euQkZDBgCLcBGAs/s400/Create%2Ba%2BUser%2Band%2BGrant%2BPermissions%2Bin%2BOracle.PNG
https://2.bp.blogspot.com/-tEV_rcrYj_w/XLbF7-Wu5PI/AAAAAAAABYU/CzTMHhqUXhofgn1-zTAMfPRs5euQkZDBgCLcBGAs/s72-c/Create%2Ba%2BUser%2Band%2BGrant%2BPermissions%2Bin%2BOracle.PNG
JavaProgramTo.com
https://www.javaprogramto.com/2019/04/create-a-user-and-grant-permissions-in-oracle.html
https://www.javaprogramto.com/
https://www.javaprogramto.com/
https://www.javaprogramto.com/2019/04/create-a-user-and-grant-permissions-in-oracle.html
true
3124782013468838591
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content