Creating a User

SQL>create user venu identifies by venu;

SQL>select * from all_users;

SQL>select username,default_tablespace,temporary_tablespace,profile from dba_users where username='VENu';

SQL>conn venu/venu

SQL>show user;

SQL>conn / as sysdba

SQL>show user

SQL>grant create session to venu;

SQL>conn venu/venu

venu>create table emp(eid number(3));

                ORA-01031 : insufficient privileges

venu>conn / as sysdba

SQL>grant create table to venu;

SQL>conn venu/venu

venu>create table emp(eid number(3));

venu>insert into emp(111);

                ORA-01950 : no privileges on tablespace 'USERS'

venu>conn / as sysdba

SQL>alter user venu quota 10m on users;

SQL>conn venu/venu

venu>insert into emp(111);

venu>commit;

venu>select * from emp;

venu>select * from session_privs;

                Lists the privileges of the current user

venu>conn / as sysdba

SQL>desc dba_sys_privs;

SQL>select * from dba_sys_privs where grantee='VENU';

                By using this view, we can see the privileges of any user.

SQL>desc dba_ts_quotas;

SQL>select username,tablespace_name,max_bytes from dba_ts_quotas where username='VENU';

                To see the quota of the given users.

SQL>grant connect,resource to venu1 identified by venu1;

                Another way of user creating an user by granting privileges at the time of user creation.

SQL>conn venu1/venu1

venu1>select * from session_privs;

                To see the privileges of a particular user in which we exist.

venu1>conn / as sysdba

SQL>revoke create session from venu;

SQL>revoke create session from venu1;