Basics Of User Management in Oracle

When a user logs on to the database, they connect to a user account by specifying an account name followed by an authentication method.

Every user account must have a default tablespace. This is the tablespace where any schema objects created by the user will be stored.

Permanent objects( such as tables) will be stored in permanent tablespaces, temporary objects are stored in the temporary tablespace. Operations that need temporary tablespace are sorting rows, joining tables, building indexes, and using temporary tables.

Below is the query for creating a user in an Oracle database

SQL> create user John identified by *****
default tablespace users
temporary tablespace temp
profile default
account unlock;  

User created.

SQL> col USERNAME for a10
SQL> col ACCOUNT_STATUS for a10
SQL> col PROFILE for a20
SQL> select username,account_status,profile from dba_users where username='JOHN';

USERNAME   ACCOUNT_ST PROFILE
---------- ---------- --------------------
JOHN       OPEN       DEFAULT

When prompted for the password for the JOHN user, John has provided the correct password but still he is unable to login.

This is because JOHN is not having the “CREATE SESSION” privilege. This privilege will allow the user to connect. Without this privilege, the user can not even log on to the database i.e CREATE SESSION is the minimum require privilege to logged in to the database.

SQL> conn JOHN
Enter password:
ERROR:
ORA-01045: user JOHN lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

SQL> grant create session to JOHN;

Grant succeeded.

A quota is the amount of space in a tablespace that the schema objects of a user are allowed to occupy.

If no quota is allocated on a tablespace, the user can not create any objects at all in that tablespace.

SQL> alter user JOHN quota 10M on users;

User altered.

To lock and unlock an account, use these commands :

SQL> alter user JOHN account lock;

User altered.

SQL> select username,account_status,profile from dba_users where username='JOHN';

USERNAME   ACCOUNT_ST PROFILE
---------- ---------- --------------------
JOHN       LOCKED     DEFAULT

SQL> alter user JOHN account unlock;

User altered.

SQL> select username,account_status,profile from dba_users where username='JOHN';

USERNAME   ACCOUNT_ST PROFILE
---------- ---------- --------------------
JOHN       OPEN       DEFAULT

To force a user to change the password, use the below command. This will immediately start the grace period forcing the user to change the password at the next login attempt.

SQL> alter user JOHN password expire;

User altered.

SQL> select username,account_status,profile,expiry_date from dba_users where username='JOHN';

USERNAME   ACCOUNT_ST PROFILE              EXPIRY_DA
---------- ---------- -------------------- ---------
JOHN       EXPIRED    DEFAULT              09-AUG-21


---Password reset :

SQL> alter user JOHN identified by *****;

User altered.

Click here for Related articles :

How to Grant READ ONLY Access on Schema Using ROLE in Oracle
How to Find User’s Privileges in Oracle
How to check object count of a schema in Oracle

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

DATABASE, ORACLE

Basics Of User Management in Oracle

Related Article