USER MANAGEMENT-1

 1)Managing Users

2)Managing Privileges

3)Managing Roles

4)Managing Profiles  

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

sql>create user tom identified by tom;

i)Creating user with default tablespace:

create user tom1  identified by tom1 default tablespace users;

  

ii)Creating user with default and temp tablespaces:  

create user tom2 identified by tom2 default tablespace users temporary tablespace temp;   

ii)Allocating Space:

create user tom3 identified by tom3 default tablespace users quota 1m on users;

  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 tom4 identified by tom4;-->reset password

 i)Alter space with limit:  

alter user tom3 quota 2m on users;

here i 'm  change the user quota of tablepsce

ii)Alter space with  unlimited

alter user tom3 quota unlimited on users;

 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 users1;

-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