let us discuss about user management  and their types

What is user management?

User management is to create and manage login credentials for each user. we can also limit their access functionality

Types of User Management:

usert

1)Managing Users

2)Managing Privileges

3)Managing Roles

4)Managing Profiles  

Managing Users:

-Each Oracle db has listed of valid database users . To access a database, a user must run a database application and connect to the database instance using a valid user name defined in the database

Manageuser  

1 )Create User - We can create new user  by using "Create User" statement -Before executing this statement we must  have "Create user" system privilege -It's a powerful privilege ,a DBA or security administrator is normally have this privilege

create user ram identified by ram;

in these above statement user - username of new user  and their password - after identified by (Note this command will create user only )   

i)Creating user with default tablespace:

create user villa  identified by villa default tablespace zen;

  here i mentioned the user  villa has been created on "ZEN" tablespace

ii)Creating user with default and temp tablespaces:  

create user raj identified by raj default tablespace zen temporary tablespace temp_ts;

in above this command i 'm allocated default tablespace and temp tablespace  for that user   

ii)Allocating Space:

create user sam identified by sam default tablespace zen quota 20m on zen;

  here i'm created a user sam and allocated space for "SAM" on zen  tablespace (Note: if we not mentioned quota size on tablespace it automatically  allow up to 125mb but its not showing dba_ts_quotas so we  must allocate space and one more thing bytes column is '0' that  user ve unlimited space on that particular tablepsace)

2) Alter User -To change user password and account limitations -Before executing this statement we must  have "Alter user" system privilege

alter  user ram identified by reena;

 i)Alter space with limit:  

alter user sam quota 25m on zen;

here i 'm  change the user quota of tablepsce

ii)Alter space with  unlimited

alter user sam quota unlimited on zen;

 To View Quota Allocation     

i)DBA Level

select * from  dba_ts_quotas;

 ii)User Level

select * from user_ts_quotas;

 To view default tablespace

SQL> select property_name,property_value from database_properties;

SQL> col property_name for a25
SQL> col property_value for a28
SQL> /

PROPERTY_NAME PROPERTY_VALUE
------------------------- ----------------------------
DICT.BASE 2
DEFAULT_TEMP_TABLESPACE     TEMP
DEFAULT_PERMANENT_TABLESP   USERS
ACE

DEFAULT_EDITION ORA$BASE
Flashback Timestamp TimeZ GMT
one

TDE_MASTER_KEY_ID
DST_UPGRADE_STATE NONE
DST_PRIMARY_TT_VERSION 11
DST_SECONDARY_TT_VERSION 0
DEFAULT_TBS_TYPE SMALLFILE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.1.0
GLOBAL_DB_NAME TEST
EXPORT_VIEWS_VERSION 8
WORKLOAD_CAPTURE_MODE
WORKLOAD_REPLAY_MODE
NO_USERID_VERIFIER_SALT 820EA1118701F6539A50393BB68B
 7AD0

DBTIMEZONE 00:00

36 rows selected.

-By using this command we can find which  tablespace is set on default

To change default tablespace

alter database default tablespace zineeth;

-The above command replace the default tablespace

To view username and passwords

select username,password from dba_users;

 To view the user password

select spare4 from  users$ where username='SAM';

  To view user password version 

SQL> select username,password,PASSWORD_VERSIONS from dba_users where username='USA';

USERNAME                       PASSWORD                       PASSWORD
------------------------------ ------------------------------ --------
USA                                                           10G 11G

Here Password_versions -  Database version in which the password was created or changed To view account status

select username,account_status  from dba_users ;

To unlock the user account   

i)Unlock

alter user ram identified by ram account unlock;

 ii)Unlock separation

alter user ram identified by ram;
alter user ram account unlock;

To lock the user account

i)Lock

alter user ram password expire account lock;

ii)Lock separation

alter user ram password expire;

 

alter user ram account lock;

To Check the user account by connection

SQL> grant connect ,resource to SAM;

Grant succeeded.

SQL> conn SAM/SAM
Connected.
SQL> select * from tab;

no rows selected

Here user was connected successfully (Note: once you create the  user you must  the access to user connect  the database ,otherwise oracle won't allow to connect  the user session)