Basics Of User Management in Oracle

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 :

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.

Related Article

DATABASE | ORACLE


 155 Total Views,  2 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the Knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

17 + 17 =

Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com

Share Learn Grow!


Click on the below technical group to join us to share expertise in PostgreSQL and MySQL on WhatsApp group. The purpose of this group is only help to the DBA community.

PostgreSQL Technical Group.

MySQL technical Group.

Technical Links Powered by DBsGuru.

Thanks.
Team DBsGuru.