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
