JDBC for Oracle - Herong's Tutorial Examples - v3.12, by Herong Yang
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