USER MANAGEMENT IN ORACLE DATABASE

 

User Management in oracle database

 

1)Managing users

2)Managing Privileges

3)Managing Roles

4)Managing Profiles

 

1)Managing users

User->Schema->owner

Table,Sequence,Constraints,indexes,views etc->Objects->Scott/HR->Tablespace->Database

HR -Schema

Tablespace Name ->HRTBS -Data

Tablespace Name ->HRINX -INDEX

Create and alter user

Create new user assigning tablespace

2)Managing Privileges

Types of Privileges

Two types of Oracle user privileges:

• System: Enables users to perform particular actions in the database

• Object: Enables users to access and manipulate a specific object

Describe system privileges

Create user and assign privileges

Create user and assign privilege to that user

Connect that user and check the privilege

Assign Multiple privileges

Revoke privileges

with the ADMIN OPTION

Connect sys user to create 2 other  users , grant create session to one user with admin option

V user grant privilege with admin option

 

Connect v user the admin access can be YES the V user give grant privilege to T user

Sys user not give create session option V user give this permission

Revoke privilege from V user

V user not able to connect

T user is in connection the V user with admin option they give permission to other user that can be access able

Object Privileges

SQL> grant insert,update,delete on a.s to b;

Grant succeeded.

SQL> grant select,insert,update,delete on a.s to b;

Grant succeeded.

SQL> insert into a.s values(1);

1 row created.

SQL> commit;

Commit complete.

Revoke object privileges

SQL> revoke insert on a.s from b;

Revoke succeeded.

SQL> grant select,insert,update,delete on a.s to a with grant option;

Grant succeeded.

3)Managing Roles

Collection of privileges - > Roles

 

Custom roles

Roles with admin option

4)Managing Profiles

Profile 2 types

PASSWORD

KERNEL  

Create new profile and assign to user:

 

FAILED_LOGIN_ATTEMPTS

PASSWORD_LOCK_TIME

Password Expiration and Aging

KERNEL

SESSIONS PER USER

IDLE_TIME

 

Connect time