Oracle Database User Creation

Hey guys, in this blog I am going to explain to you about Oracle Database User Creation.

USER-

User is basically used to connect to database. All Database objects like table, index, view etc. can be created under that user. In Oracle users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.)

Database User Accounts-

Each database user account has:

• A unique username
• An authentication method
• A default tablespace
• A temporary tablespace
• A user profile
• An account status

Predefined Accounts SYS account-

• Is granted the DBA role.
• Have all privileges with ADMIN OPTION.
• Is required for startup, shutdown, and some maintenance commands.
• Owns the data dictionary.

SYSTEM account is granted the DBA role-

These accounts are not used for routine operations.

User Authentication- 

Oracle can utilize the data kept in a database to verify the identity of anyone trying to connect to it. You must create each user with a password that is required when the user tries to establish a connection in order to configure Oracle to use database authentication.

Step to create user -

Create user user_name identified by password;

Step to create session-

Minimum privilege required to connect to a database is create session.

Grant create session to user_name ;

Step to change password of user-

Alter user tarif1 identified by tarif01;

Step to user lock and unlock-

Alter user tarif1 account lock;

Alter user tarif1 account unlock;

Step to give to tablespaces privileges-

grant unlimited tablespace to tarif1;

Alter user user_name quota 100mb 0r 200mb on 'USERS';

Step to describe user-

desc dba_users;

Select username, account_status, last_login from dba_users where username =’TARIF’;

User Authorization-

A fundamental step in securing a database system is validating the identity of the user who is accessing the database (authentication) and controlling what operations they can perform (authorization). A strong authentication and authorization strategy helps protect the users and their data from attackers.

Role-

A role is a collection of privileges. It allows easier management of privileges.

Step to create role-

Create role tarif_role;

Step to Grant role-

Grant select, update, delete on tarif.employee to tarif_role;

Step to describe table privilege:-

desc dba_tab_privs;

Select GRANTEE, OWNER, TABLE_NAME, PRIVLIEGE from dba_tab_privs where table_name =’EMPLOYEE’;