User Administration

Creating User : 

SQL> show user;
USER is "SYS"

SQL> create user sairam identified by saipassword;
User created.

SQL> select default_tablespace from dba_users where username='SAIRAM';
DEFAULT_TABLESPACE
------------------------------

SYSTEM

SQL> alter user sairam default tablespace xxtrainee;
User altered.

SQL> select default_tablespace from dba_users where username='SAIRAM';
DEFAULT_TABLESPACE
------------------------------

XXTRAINEE

SQL> select max_bytes from dba_ts_quotas where username='SAIRAM';

no rows selected

SQL> alter user sairam quota 100m on xxtrainee;

User altered.

SQL> select max_bytes from dba_ts_quotas where username='SAIRAM';

 MAX_BYTES

----------

 104857600

SQL> create user sairam identified by saipassword default tablespace xxtrainee quota 150m on xxtrainee;

User created.

Once we create a user account we need to specify the previleges. If no privilege is given to the user. He can't connect to the DB.

[oravis@test ~]$ sqlplus sairam/saipassword;

ERROR:

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

Granting Privileges :

SQL> grant create session, create table to sairam;

Grant succeeded.

SQL> select privilege from dba_sys_privs where grantee='SAIRAM';

PRIVILEGE

----------------------------------------

CREATE TABLE

CREATE SESSION

CREATE SESSION:  

SQL> connect sairam/saipassword

Connected.

SQL> show user

USER is "SAIRAM"

[oravis@test ~]$ sqlplus sairam/saipassword

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 31 12:26:53 2018

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

Last Successful login time: Tue Jul 31 2018 12:26:34 +03:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user

USER is "SAIRAM"

CREATE TABLE:

SQL> create table business (customer_id number(10),customer_name varchar2(20),corporate_name varchar2(20),limit_iqd number(15),password varchar2(10),card_type varchar2(10));

Table created.

SQL> desc business;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 CUSTOMER_ID                                        NUMBER(10)

 CUSTOMER_NAME                                      VARCHAR2(20)

 CORPORATE_NAME                                     VARCHAR2(20)

 LIMIT_IQD                                          NUMBER(15)

 PASSWORD                                           VARCHAR2(10)

 CARD_TYPE                                          VARCHAR2(10)

SQL> insert into business (customer_id,customer_name,corporate_name,limit_iqd,password,card_type) values (999,'zashwanth','sanielsoft',50621,'pass542','corp');

1 row created.

SQL> select *from business;

CUSTOMER_ID CUSTOMER_NAME        CORPORATE_NAME        LIMIT_IQD PASSWORD

----------- -------------------- -------------------- ---------- ----------

CARD_TYPE

----------

999     zashwanth      sanielsoft    50621     pass542     corp

SQL> commit;

Commit complete.

SQL> select tablespace_name from user_segments where segment_name='BUSINESS';

TABLESPACE_NAME

------------------------------

XXTRAINEE

SQL> show user

USER is "SAIRAM"

SQL> select tablespace_name,extents from user_segments where segment_name='BUSINESS';

TABLESPACE_NAME                   EXTENTS

------------------------------ ----------

XXTRAINEE                               1

SQL> select extent_id,bytes from user_extents where segment_name='BUSINESS';

EXTENT_ID      BYTES

---------- ----------

         0      65536

(Default extent size is - 8blocks=64kb)

Changing User Password:

SQL> alter user sairam identified by sai123;
User altered.

To make user to change password

SQL> alter user sairam password expire;

User altered.

when user logs in next session will be prompted for new password

[oravis@test ~]$ sqlplus sairam/sai123

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 31 13:55:01 2018

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

ERROR:

ORA-28001: the password has expired

Changing password for sairam

New password:

Retype new password:

Password changed

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter user sairam quota 5m on test;

User altered.

SQL> select default_tablespace from dba_users where username='SAIRAM';

DEFAULT_TABLESPACE

------------------------------

XXTRAINEE

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

TABLESPACE_NAME                     BYTES  MAX_BYTES

------------------------------ ---------- ----------

TEST                                    0    5242880

XXTRAINEE                               0  157286400

SQL> create table t1 (n number) tablespace test;

Table created.

SQL> desc t1;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 N                                                  NUMBER

SQL> insert into t1 values(340);

1 row created.

SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME

--------------------------------------------------------------------------------

TABLESPACE_NAME

------------------------------

T1

TEST

SQL> select tablespace_name,bytes,max_bytes from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES

------------------------------ ---------- ----------

TEST                                65536    5242880

XXTRAINEE                               0  157286400

sys SQL> select tablespace_name,bytes,max_bytes from dba_ts_quotas where username='SAIRAM';

TABLESPACE_NAME                     BYTES  MAX_BYTES

------------------------------ ---------- ----------

TEST                                65536    5242880

XXTRAINEE                               0  157286400

SQL> alter user sairam quota 0m on test;

User altered.

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

TABLESPACE_NAME                     BYTES  MAX_BYTES

------------------------------ ---------- ----------

XXTRAINEE                               0  157286400

SQL> create table t2(n number) tablespace test;

Table created.

SQL> insert into t2 values (100);

insert into t2 values (100)

*ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'TEST'

Lock/Unlock User :

sys SQL> alter user sairam account lock;

User altered.

SQL> conn sairam/sai123

ERROR:

ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

sys SQL> select account_status from dba_users where username='SAIRAM';

ACCOUNT_STATUS
--------------------------------
LOCKED

SQL> alter user sairam account unlock;
User altered.

SQL> conn sairam/sai123;

Connected.

SQL> select default_tablespace,temporary_tablespace,created,account_status from dba_users where username='SAIRAM';

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED

------------------------------ ------------------------------ ------------------

ACCOUNT_STATUS

--------------------------------

XXTRAINEE                      TEMP                           31-JUL-16

OPEN

Drop User :

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"

SQL> drop user sairam ;
User dropped.

ORA-01922: CASCADE must be specified to drop 'SAIRAM'

SQL> drop user sairam cascade;
User dropped.