To Create User In Oracle Database

Before create new user we can check all username already created by using the following command :

SQL> SELECT USERNAME FROM ALL_USERS;

USERNAME
------------------------------
XDB
DODOL
EXPORTADMIN
TEDY
SCOTT
MGMT_VIEW
RMAN
MDDATA
SYSMAN
MDSYS
SI_INFORMTN_SCHEMA
ORDPLUGINS
ORDSYS
OLAPSYS
ANONYMOUS
CTXSYS
EXFSYS
WMSYS
DBSNMP
TSMSYS
DMSYS
DIP
OUTLN
SYSTEM
SYS

25 rows selected.

SQL>

Command for create user in Oracle database is like this :

SQL> CREATE USER testing IDENTIFIED BY password
  2  DEFAULT TABLESPACE USERS

User created.

SQL>

After create new user (in this example called ‘testing’), that new user can’t login into database without having granted access to create sesion. We will get error like this :

oracle@mytestbed:~$ sqlplus testing/password

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 5 23:10:37 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01045: user TESTING lacks CREATE SESSION privilege; logon denied

Enter user-name: ^C
oracle@mytestbed:~$

To give login access to user ‘testing’ we do it like this :

oracle@mytestbed:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 5 23:11:20 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> GRANT CREATE SESSION TO testing;

Grant succeeded.

SQL>

And here the result after user ‘testing’ has granted access to login :

oracle@mytestbed:~$ sqlplus testing/password

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 5 23:11:58 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

Leave a Reply