Oracle for Beginner: Creating User Account

In Oracle, we can create an user account easily. Here are some examples that will show you how to create a new user account and grant some privileges to that user (as dba user):

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> create user xxx identified by password_xxx;
SQL> grant connect to xxx ;
SQL> quit

(now, you can try to login as user xxx)
$ sqlplus xxx/password_xxx
(done user xxx login in! but you can't do anything except querying some dictionary view that already grant to you, like v$version, user_tables, user_indexes, etc.)
SQL> select * from v$version;
SQL> select table_name from user_tables;
(you also can change your password)
SQL> alter user xxx identified by oracle;
(but you can't do DDL or DML operation)
SQL> create table test(a number);
(error!)
SQL> exit

We will do a simple practice to make our user xxx can do DDL or DML operation on tablespace "operations" with space limitation . To do this, you should login again as DBA:

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> create tablespace operations datafile size 5M;
SQL> alter user xxx default tablespace operations quota 2M on operations password expire;
(we want to change default tablespace from "users" to "operations" and make a space limitation for user xxx only for 2M. Option "password expire" is an option to force user xxx to change the password. Try to login as user xxx again)
SQL> grant create table, create any index, create sequence to xxx;
(as a dba, we grant some privileges for user xxx, so he/she can DDL and DML toward their own objects)
SQL> conn xxx/oracle
ERROR:
ORA-28001: the password has expired

Changing password for xxx
New password:
Retype new password:
Password changed
Connected.
SQL> create sequence seq_test;
SQL> create table test(id number(4) primary key, data varchar(40));
SQL> insert into test values(seq_test.next, 'test one');
SQL> commit;
SQL> select * from test;
SQL> drop table test purge;
SQL> exit

In unix/linux environment, if you want unix/linux user account can login to oracle which is the instance oracle is also running on the same machine, you can try with these a simple practice:

$ su -
password:
# adduser testing
# passwd testing
# exit

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> show parameter os_authent_prefix;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$

SQL> create user ops$testing identified by externally;
SQL> grant connect, resource to ops$testing;
SQL> quit
$ su - testing
password:
$ sqlplus /
SQL> show user;
ops$testing
(now you can login to your oracle with operating system user account)