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>