Profile {Profile (password & session), Role (object & system), User}
Profile — is for resource limit, we can enforce password & session parameters to the user
Role — is for system & object level permission for user
Profile
It’s a collection of user security resource limit. We can apply password and session parameters.
You can enforce a limit on resource utilization using resource limit parameters. Also, you can maintain database security by using password management feature.
User accounts can have only a single profile.
By default a DEFAULT profile will be assigned for the user.
Types: 1. Password management 2. Session management
Password management parameters:
Default for all unlimited
FAILED_LOGIN_ATTEMPTS – Maximum times the user is allowed in failing login before locking the user account * 10
PASSWORD_LIFE_TIME – Number of days the password is valid before expiry * 108 days
PASSWORD_GRACE_TIME – Number of grace days for user to change password * 7
PASSWORD_LOCK_TIME – Number of days the user account remains locked after failed login * 1 day
PASSWORD_REUSE_TIME – Number of days after the user can use the already used password * UNLIMITED
PASSWORD_REUSE_MAX – Number of times the user can use the already used password * UNLIMITED
PASSWORD_VERIFY_FUNCTION – PL/SQL that can be used for password verification * NO DEFAULT SETTING
SEC_CASE_SENSITIVE_LOGON – To control the case sensitivity in passwords * TRUE
Session management parameters:
Default for all unlimited
SESSIONS_PER_USER – Specify the number of concurrent sessions to which you want to limit the user.
CPU_PER_SESSION – Specify the CPU time limit for a session, expressed in hundredth of seconds.
CPU_PER_CALL – Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
CONNECT_TIME – Specify the total elapsed time limit for a session, expressed in minutes.
IDLE_TIME – Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
LOGICAL_READS_PER_SESSION – Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
LOGICAL_READS_PER_CALL – Specify the permitted the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
PRIVATE_SGA – Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.
COMPOSITE_LIMIT – Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.
How to create a profile
To create a profile, we need to enable the followings.
Sql> Alter system set resource_limit = true / false
SQL> CREATE PROFILE <my_profile> LIMIT
FAILED_LOGIN_ATTEMPTS 3
SESSIONS_PER_USER 2
IDLE_TIME 5
CONNECT_TIME 10;
If you have not mentioned all, that will be taken from default i.e. Unlimited.
Alter profile to add or remove limits (or) change the values
Alter profile <pro name> limit password_grace_time 5;
Alter profile <pro name> limit password_grace_time 90;
Query to find the database profile and limits
Sql> select * from dba_profiles
Drop profile
Sql> Drop profile <pro name>
Sql> Drop profile <pro name> cascade
Cascade – We use this option to delete the currently used profiles.
USER management
Sys and system are DBAs account. Sys can do anything. System except shutdown & startup.
Create User
Sql> Create user <unname> identified by <password>
By default the tables created by user will go default table space.
Sql> CREATE USER <user> IDENTIFIED BY <pass>
DEFAULT TABLESPACE example
QUOTA 10M ON example
TEMPORARY TABLESPACE temp
QUOTA 5M ON users
PROFILE app_user
PASSWORD EXPIRE;
Mostly, The tables are created and point more than one tablespace. Which will be useful to move the tables in the future b/w tablespace. Without “ QUOTA 5M ON users ” we cannot move to users tablespace.
Sql> conn Muthu/Muthu
Remove the quote
Sql> Alter user <muthu> quoto om on users;
Remove the granted profile
Sql> Alter user <muthu> profile default/some other profile name;
Reset the password
Sql> Alter user <muthu> identified by <new password>;
Check the user status
Sql> select username,account_status from dba_users;
How to set the same password to the user
Sql> select spare2, spare4 from sys.user$ where name =’USER1’;
Sql> select spare2, spare4 from user$ where name =’USER1’;
Get the spare4 value and pass it.
Sql> Alter user <muthu> identified by values ‘spare4 value’;
Unlock the user
Sql> Alter user <muthu> account unlock;
View present user details
Sql> show user;
Sql> select * from user_users;
How to find tablespace given as quote for the users
Sql> select * from DBA_TS_QUOTAS;
select * from USER_TS_QUOTAS
How to find the sysdba role given user
Sql> select * from V$PWFILE_USERS;
Best practice: Create two sysdba user for emergency
Drop user
Sql> Drop user <user name>;
Sql> Drop user <user name> cascade;
Privileges
- Object privilege
- System privilege
- Role privilege
Object Privilege – (Select, insert, update, alter, execute etc)
Sql> Grant select on Muthu.emp to rajesh;
Sql> Grant insert,update on Muthu.emp to rajesh with grant option;
With grant only for – insert & update
Revoke the granted privilege
Sql> Revoke select on Muthu.emp from rajesh;
Sql> Revoke insert,update on Muthu.emp from rajesh;
Once, we revoked the user privilege, the grant options also removed automatically
System privilege – (More than 256 admin privileges are available)
Grant any object privilege
Create session privilege … etc
How to grant the system privilege to the user
Sql> Grant create session, create table to rajesh;
Each user must need Create session is must give to the user, to login to oracle DB.
Sql> Grant select any table to rajesh;
How to grant with grant option the system/admin privilege to the user
Sql> Grant select any table to rajesh with admin option;
Revoke the granted privilege
Sql> Revoke create session, create from rajesh;
Sql> Revoke select any table from rajesh;
The with granted system/admin privilege will not be removed automatically, we need to revoke if needed.
Role
Role used to create bundles of system and object privileges, so that we can have control of the user.
Default role:
Connect, Resource, dba, emp_full_database, imp_full_database, emp_full_database,
Datapump_exp_full_database, Datapump_ixp_full_database etc..
How to create a role
Sql> Create role <role name>;
Sql> Grant select on Muthu.emp to < role name >;
Sql> Grant create session, create table to < role name >;
Sql> Grant < role name > to Rajesh;
Revoke and remove role from user
Sql> revoke <role name> from muthu;
Sql> revoke create session, create table from muthu;
Sql> revoke select from muthu;
Sql> Drop role < role name >;
Query to find the object privilege
Object Privilege
Sql> Select * from dba_tab_privs; — Object privilege
Sql> Select * from user_tab_privs; — Current User
System Privilege
Sql> Select * from dba_sys_privs;
Sql> Select * from user_sys_privs;
Roles and roles privilege
Sql> Select * from dba_roles;
Sql> Select * from user_roles;
Sql> Select * from dba_role_privis;
Sql> Select * from user_role_privis;
