User Managment

Managing Database users with privileges and Roles:

            Database user account is must to access the database instance.Every database instance contains the following default users.

            1. Sys 2.System etc..

Note: sys user act as a dba. Because he has all privileges and roles to access the database and managing other user accounts. Sys user can able to create other users.

Privileges:

A user privilege is a right to execute a particular type of SQL statement, or a right to access   another user's object. Oracle also provides shortcuts for grouping privileges that are commonly granted or revoked together.

There are 2 types of Privileges available in any database.

            1. System level privileges

            2. Object level privileges

Privilege is permission to access or deny the database.

1. System Level Privileges:

There are over 80 distinct system privileges. Each system privilege allows a user to perform a particular database operation or class of database operations.

Examples for  System Privileges:

ALTER DATABASE

CREATE DATABASE LINK

AUDIT SYSTEM

CREATE SESSION

ALTER SYSTEM

CREATE TABLE

CREATE TABLESPACE

ALTER TABLESPACE

DROP TABLESPACE

ALTER USER

DROP USER

System level privileges are assigned by the dba to the users.

Ex : Create table,create view,drop table,drop view,…….etc.

2. Object Level Privileges :

Each type of object has different privileges associated with it. privileges allow a system user to perform a specific type of operation on a specific schema object

Examples for  System Privileges:

ALTER

DELETE

EXECUTE

INSERT

SELECT

UPDATE

Object level privileges are assigned to other user to access his table by other user.

Either system levels (or) object level privileges are assigned by GRANT command.

How to Remove the Privillege:

Privilleges can be removed by using REVOKE  command

ROLES:

a role is created as a container for groups of privileges that are granted to system users who perform similar, typical tasks in a business.

Role Benefits:

Easier privilege management: Use roles to simplify privilege management. Rather than granting the same set of privileges to several users, you can grant the privileges to a role, and then grant that role to each user.

Dynamic privilege management: If the privileges associated with a role are modified, all the users who are granted the role acquire the modified privileges automatically and immediately.

Advantage of Roles:

Roles provides easy administration & less maintenance.Every database instance contains some of the default rules.

Ex: connect,resource,dba,full_export_database,full_import_database etc.

• Oralce recommends to assign connectand resource two rules to every user before login to the database.
• Roles can be assigned by using grant command.
• Roles can be removed by using revoke command.

Demo’s on User Administration:

#su – oracle

$sqlplus / as sysdba

SQL>startup

SQL>select username,password from dba_users;

SQL>select* from all_users;

Creation of users :

SQL>create user kishore identified by kishore;

User created

SQL>grant connect,resource to kishore;

Grant succeeded

$sqlplus kishore/kishore

kishore@orcl>select * from user_users;

USERNAME                          USER_ID ACCOUNT_STATUS

------------------------------ ---------- --------------------------------

LOCK_DATE EXPIRY_DA DEFAULT_TABLESPACE

--------- --------- ------------------------------

TEMPORARY_TABLESPACE           CREATED   INITIAL_RSRC_CONSUMER_GROUP

------------------------------ --------- ------------------------------

EXTERNAL_NAME

--------------------------------------------------------------------------------

kishore                                 32 OPEN

          08-AUG-14 USERDATA

TEMPX                          09-FEB-14 DEFAULT_CONSUMER_GROUP

How to identify user information as an user :

$sqlplus kishore/kishore

kishore@orcl>select * from user_users;

kishore@orcl>conn / as sysdba

SQL>select * from dba_users;

How to lock & unlock users :

SQL>alter user kishore account lock;

User altered.

SQL>alter user kishore account unlock;

User altered.

How to identify user session_privileges :

SQL>select * from session_privs;

How to identify user_roles:

kishore@orcl>select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_

------------------------------ ------------------------------ --- --- ---

kishore                         CONNECT                        NO  YES NO

kishore                        RESOURCE                       NO  YES NO

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

kishore                         UNLIMITED TABLESPACE                     NO

How to assign Disk quota to a user :

SQL>create user ravi identified by kishore  quota 30m on user_orcl;

User created

SQL>alter user ravi quota 20m on user_orcl;

Tablespace altered

SQL>desc dba_ts_quotas

SQL>select * from dba_ts_quotas where username = ‘RAVI’;

How to create a user and assigning privileges with a single statement :

SQL>grant connect, resource to Suresh identified by Krishna;

How to identify the system define roles:

SQL>select * from dba_roles;

SQL>select * from role_role_privs;

SQL>select * from dba_role_privs;

SQL>select * from user_role_privs;

How to create demo tables in the user:

kishore@PROD>exit

$cd $ORACLE_HOME/rdbms/admin/

$ls utlsampl.sql

$cp utlsampl.sql /u01/demobld.sql

$cd /u01/

$vi demobld.sql

Remove all unwanted lines upto above create table dept line.

 Goto end of the line and delete end of the line.

!wq

$sqlplus kishore/kishore

kishore@orcl>select * from tab;

kishore@orcl>@demobld.sql

kishore@orcl>select * from tab;

Now we see emp,dept,bonus etc 

.

How to drop a empty user :

kishore@orcl>conn / as sysdba

SQL>drop user kishore;

SQL>select * from all_users;

How to drop a user including objects:

SQL>drop user kishore cascade;

SQL>select * from all_users;

How to create roles :

SQL>create role demo_role;

How to assign roles to a role and user :

SQL>grant connet,resource to demo_role;

SQL>grant connect,resource to ali;

How to revoke roles from users :

SQL>revoke connect,resource from ali;

How to assign object level privileges to other users :

SQL>conn kishore/kishore

kishore@orcl>select * from tab;

kishore@orcl>grant select,insert,update,delete on emp to ravi;          (or)

kishore@orcl>grant all on emp to ravi;

kishore@orcl>connect ravi/kishore

ravi@orcl>select * from kishore.emp;

How to remove object level privileges:

Kishore@orcl>revoke delete on emp from ravi;

kishore@orcl>conn ravi/kishore

ravi@orcl>select * from kishore.emp;

Profiles:

Profile – is a database object – a named set of resource limits.Profiles are a means to limit resources a user can use.

Two types of resources:

·         Kernel Level Resources

·         Password Level Resources

Kernel Resources:

·         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)

Password Resources:

·         Maximum failed login attempts
(failed_login_attempts)

·         Maximum time a password is valid
(password_life_time)

·         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)

$vi initPROD.ora

resource_limit=true

:wq

Creating a Profile:

>CREATE PROFILE accountant LIMIT

    SESSIONS_PER_USER 4

    CPU_PER_SESSION unlimited

    CPU_PER_CALL 6000

    LOGICAL_READS_PER_SESSION unlimited

    LOGICAL_READS_PER_CALL 100

    IDLE_TIME 30

    CONNECT_TIME 480

    PASSWORD_REUSE_TIME 1

    PASSWORD_LOCK_TIME 7

    PASSWORD_REUSE_MAX 3;

>ALTER PROFILE Accountant LIMIT

    CPU_PER_CALL default

    LOGICAL_READS_PER_SESSION 20000

    SESSIONS_PER_USER 1;

>DROP PROFILE <profile_name>;

Assigning profile to user:

ALTERUSER<user_name> PROFILE <profile_name>;

Assign Profile During User Creation:

CREATEUSER<user_name> IDENTIFIED BY <password> PROFILE <profile_name>;

How to check profiles:

select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles;

select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT';