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;
How to Create a User in OracleBefore 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
H:\>sqlplus / as sysdba SQL*Plus: Release 184.108.40.206.0 - Production on Tue Apr 16 12:47:26 2019 Version 220.127.116.11.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.104.22.168.0 - Production Version 22.214.171.124.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.
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.
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.
SQL> GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO javaw3schools; Grant succeeded
SQL> GRANT UNLIMITED TABLESPACE TO javaw3schools; Grant succeeded.