SQL*Plus - Create New User and Login

This section describes how to use SQL*Plus to create a new user in Oracle server and login as the new user.

In order to login to Oracle Oracle 11.2 server, without using the Windows user group authentication, I need to add a new user and resolve the "ORA-12154: TNS: could not resolve the connect identifier specified" error.

1. Log in as admin with Windows user authentication and create user "Herong".

herong> sqlplus /nolog

SQL> -- Using Windows user group authentication for now
SQL> CONNECT / AS SYSDBA
Connected.

SQL> -- Create a new user Herong
SQL> CREATE USER Herong IDENTIFIED BY TopSecret ACCOUNT UNLOCK;
CREATE USER Herong IDENTIFIED BY TopSecret ACCOUNT UNLOCK
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> -- The above error is caused by the Multitenant Environment feature 
SQL> -- introduced in Oracle 12. It requires the user name in the CDB 
SQL> -- CDB (Container Database) being prefixed by "C##...".
SQL> -- But you can turn that requirement off by:
SQL> alter session set "_ORACLE_SCRIPT"=true;

SQL> -- Create a new user Herong
SQL> CREATE USER Herong IDENTIFIED BY TopSecret ACCOUNT UNLOCK;
User created.

SQL> -- Give login permission to Herong
SQL> GRANT CREATE SESSION TO Herong;
Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Express Edition ...

2. Log in as Oracle XE user "Herong".

herong> sqlplus /nolog

SQL> -- Try with a wrong password
SQL> CONNECT Herong/WrongPass
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> -- Connect with the correct password
SQL> CONNECT Herong/TopSecret
Connected.
SQL>

SQL> SELECT username, privilege FROM USER_SYS_PRIVS;

USERNAME                       PRIVILEGE
------------------------------ ----------------------
HERONG                         CREATE SESSION

Okay. I have a new user created in Oracle XE server. I can login as the new user with SQL*Plus.

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

Oracle Express Edition Installation on Windows

 Oracle Database Express Edition (XE) Installation

 Accessing Oracle Server through Web Interface

 Creating Oracle Database Users

 SQL*Plus - Oracle Command Line Interface

SQL*Plus - Create New User and Login

 Oracle JDBC Drivers

 Oracle - Reference Implementation of JdbcRowSet

 Oracle - PreparedStatement

 Oracle - JBDC CallableStatement

 Oracle CLOB (Character Large Object) - TEXT

 Oracle BLOB (Binary Large Object) - BLOB

 Archived Tutorials

 References

 Full Version in PDF/EPUB