Roles and profiles in oracle

Privileges,role and profiles

Privileges
A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object, execute a PL/SQL package, and so on. The types of privileges are defined by Oracle.
Roles
Roles are created by users (usually administrators) to group together privileges or other roles. They are a means of facilitating the granting of multiple privileges or roles to users.
Profiles
Profiles define resource limits imposed upon a user account. The "default" profile sets all resource limits to unlimited.

Roles:-
Role is a set or group of privileges.
A role contains all privileges granted to the role and grants the role to a user
You can add or drop privileges to a role, some example of predefined roles are dba, connect, resource, plustrace.

Create Role
Create role r1;

Create Role with password
Create role r1 identified by password;

Add privilege to a Role
Grant create session to r1;
Grant create view to test1_role;

Add role to a role
Grant test1_role to r1;

Assigning Role to User
Grant r1 to scott;

Revoke Role from User
Revoke r1 from scott;

Drop Role
Drop role r1;

 Privilege 
A privilege is a right to execute an SQL statement or to access another user's object. A Privilege can be assigned to a user or a role.

Types of privileges 
 
System privileges:- 

1.  Some of system privileges are create table, create view, and create procedure.
2.   List of system privileges shows in dba_sys_privs

Object privileges:-

1.   Some of object privileges are select, insert, delete, update, and alter.
2.   List of system privileges shows in dba_tab_privs.

Granting the Admin Option
If you have granted privilege to user and the user have able to grant those privileges to other users. We use with admin option to grant command.
We can revoke the granted privilege, Admin option never broke the chain cycle.
Grant create index to scott with admin option.

Granting the grant option
We can revoke the granted privilege, Grant option broke the chain cycle. So reminding users granted privilege automatically revoked.
Grant create index to scott with grant option.

Views

DBA_ROLES
DBA_ROLE_PRIVS
ROLE_ROLE_PRIVS
SESSION_ROLES


Profiles in Oracle

 

1. Profiles are a means to limit resources a user can use.
2. Before profiles can be assigned, they must be created with create profile. 
3. Then, they can be assigned to users with alter user ... profile.

Resource Parameters:-

• Maximum concurrent sessions for a user - (sessions_per_user)
• CPU time limit per session - (cpu_per_session)
• CPU time limit per call - (cpu_per_call) Call being parse, 
• execute and fetch Maximum connect time - (connect_time) The session will be dropped by oracle after specified time. 
• Maximum idle time - (idle_time) The session will be dropped by oracle after specified time of doing nothing. Long running processes are not idle!
• Maximum blocks read per session- (logical_reads_per_session)
• Maximum blocks read per call - (logical_reads_per_call)
• Maximum amount of SGA - (private_sga) 
• Specify the total resource cost for a session, expressed in service units-( COMPOSITE_LIMIT)
•      Composite_limit is sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. 

NOTE:-In order to enforce kernel limits, resource_limit must be set to true.

Password limits:-

• Maximum failed login attempts - (failed_login_attempts)
• Maximum time a password is valid - (password_life_time) default UNLIMITED 
• Minimum of different passwords before password can be reused - (password_reuse_max)
• Minimum of days before a password can be reused - (password_reuse_time)
• Number of days an account is locked after failing to login - (password_lock_time)
• After password expiry 3 day grace time to re-create - (password_grace_time)
• Verify function for passwords- (password_verify_function)

NOTE:-If a session exceeds one of these limits, Oracle will terminate the session.

      If there is a logoff trigger, it won't be executed.


Examples:-
       CREATE PROFILE pro1 LIMIT PASSWORD_REUSE_MAX 10 PASSWORD_REUSE_TIME 30;
 
   CREATE PROFILE app_user LIMIT
   SESSIONS_PER_USER          UNLIMITED
   CPU_PER_SESSION            UNLIMITED
   CPU_PER_CALL               3000
   CONNECT_TIME               45
   LOGICAL_READS_PER_SESSION  DEFAULT
   LOGICAL_READS_PER_CALL     1000
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000;
 
 
   CREATE PROFILE app_user2 LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 60
   PASSWORD_REUSE_TIME 60
   PASSWORD_REUSE_MAX 5
   PASSWORD_VERIFY_FUNCTION verify_function
   PASSWORD_LOCK_TIME 1/24
   PASSWORD_GRACE_TIME 10;


Alter

ALTER PROFILE accountant LIMIT  CPU_PER_CALL default  LOGICAL_READS_PER_SESSION 20000  SESSIONS_PER_USER 1;


  desc dba_profiles