07 Sep 2016

User Management in Oracle

4:30 PM Oracle Database 0 Comment

let us discuss about user management  and their types

What is user management?

User management is to create and manage login credentials for each user. we can also limit their access functionality

Types of User Management:

IMG_20160902_172345_HDR

1)Managing Users

2)Managing Privileges

3)Managing Roles

4)Managing Profiles

1. Managing users

-Each Oracle db has listed of valid database users . To access a database, a user must run a database application and connect to the database instance using a valid user name defined in the database.

IMG_20160902_173301_HDR

 1 )Create User

– We can create new user  by using “Create User” statement

-Before executing this statement we must  have “Create user” system privilege

-It’s a powerful privilege ,a DBA or security administrator is normally have this privilege

in these above statement user – username of new user  and their password – after identified by

(Note this command will create user only )

   i)Creating user with default tablespace:

create user nbostech identified by nbostech default tablespace abc;

here i mentioned the user  nbostech has been created on “ABC” tablespace

ii)Creating user with default and temp tablespaces:

create user vinisoft identified by vinisoft default tablespace temporary tablespace temp_ts;

in above this command i ‘m allocated default tablespace and temp tablespace  for that user

   ii)Allocating Space: 

create user sam identified by sam default tablespace zen quota 20m on zen;

here i’m created a user sam and allocated space for “SAM” on zen  tablespace

(Note: if we not mentioned quota size on tablespace it automatically  allow up to 125mb but its not showing dba_ts_quotas so we  must allocate space and one more thing bytes column is ‘0’ that  user ve unlimited space on that particular tablepsace)

2) Alter User

-To change user password and account limitations

-Before executing this statement we must  have “Alter user” system privilege

alter user ram identified by reena;

   i)Alter space with limit:

alter user sam quota 25m on zen;

here i ‘m  change the user quota of tablepsce

ii)Alter space with  unlimited

alter user sam quota unlimited on zen;

To change default tablespace

alter database default tablespace zineeth;

-The above command replace the default tablespace

To view username and passwords

select username,password from dba_users;

 To view the user password

select spare4 from users$ where username='SAM';

 To view user password version 

select username,password,PASSWORD_VERSIONS from dba_users where username='USA';

USERNAME                       PASSWORD                       PASSWORD
—————————— ——————————           ——–
USA                                                                               10G 11G

Here Password_versions –  Database version in which the password was created or changed

To view account status

select username,account_status  from dba_users;

To unlock the user account

   i)Unlock

  ii)Unlock separation

alter user ram identified by ram;
alter user ram account unlock;

To lock the user account

i)Lock

alter user ram password expire account lock;

 ii)Lock separation

alter user ram password expire;
alter user ram account lock;

To Check the user account by connection

Here user was connected successfully

(Note: once you create the  user you must  the access to user connect  the database ,otherwise oracle won’t allow to connect  the user session)

2. Managing privileges:

Let us we discuss about user management another type managing privilege

what is privilege / User privilege?

– Privilege is nothing but to give users  access rights  for that particular objects  in oracle

– Privileges are  granted or revoked either by the instance administrator user

– A user privilege is a right to execute a particular type of SQL statement, or a right to access another user’s object

Types of Privileges

IMG_20160902_173209_HDR

*System privilege or System level privilege
*Object Privilege or object level privilege

Object Privilege ( with grant option )
-The listed of objects are assigned to roles or users
Privilege Description
SELECT Privilege to perform SELECT statements on the table.
INSERT Privilege to perform INSERT statements on the table.
UPDATE Privilege to perform UPDATE statements on the table.
DELETE Privilege to perform DELETE statements on the table.
REFERENCES Privilege to create a constraint that refers to the table.
ALTER Privilege to perform ALTER TABLE statements to change
the table definition.
INDEX Privilege to create an index on the table with the create index
statement.
ALL All privileges on table.

Views of object privs
1)USER_TAB_PRIVS
2)ALL_TAB_PRIVS
3)ROLE_TAB_PRIVS
4)DBA_TAB_PRIVS
With grant option and cascading revoke
s-1
connect our database as sys db user

[oracle@localhost ~]$ sqlplus / as sysdba

s-2
create sample schemas

SQL> create user u01 identified by u01;
User created.

Grant rights to them

SQL> grant create session, create table to u01;
Grant succeeded.

create schema ‘u02′

SQL> create user u01 identified by u02;
User created.

grant object rights to u02

SQL> grant create session to u02;
Grant succeeded.

create schema ‘u03′

SQL> create user u01 identified by u03;
User created.
SQL> grant create session to u03;
Grant succeeded.

s-3
connect u01

SQL>conn u01/u01
connected.

create table t0001 from u01

SQL> create table t0001 (col1 number, col2 varchar2(100));
Table created

to give with grant option to u02 user

SQL> grant select on t0001 to u02  with grant option;
Grant succeeded.

Check user_tab_privs

SQL> column grantee format a7
SQL> column owner format a5
SQL> column table_name format a10
SQL> column grantor format a7</pre>
SQL> column privilege format a9 
SQL> column grantable format a9 
SQL> select * from user_tab_privs
 

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE
——- —– ———- ——- ——— ——— —
U02 U01 T0001 U01 SELECT YES NO

s-4
connect u02

SQL> conn u02/u02
connected.

grant privilege select u01.t0001 to u02

SQL> grant select on u01.t0001 to u03;
Grant succeeded.

s-5
connect user u03

SQL> conn u03/u03
connected.

check the privilege given table

SQL> select * from u01.t0001;
no rows selected.

s-6
connect the user session u01 and check the user _tab_privs

SQL> select * from user_tab_privs;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIE
——- —– ———- ——- ——— ——— —
U03 U01 T0001 U02 SELECT NO NO
U02 U01 T0001 U01 SELECT YES NO

s-7
revoke ‘With grant option ‘ from user u01

SQL> revoke select toool from u02;
Revoke succeeded.

3. Managing Roles

Let us we can discuss about roles in user management
first we need know what is roles ? how it ll be different from profiles?
Roles:
– Role is a set of privileges that can be granted to users or to other roles
– We can add privileges to roles and then grant the role to a user.
Difference between roles and privileges
– Profiles are set of authorization objects , but roles are set of profiles
-Without giving profile we can’t create a role,but profile can exist/function in user master record without being role. which means that, when you assign a Role, without a profile generated for that Role, to a user, the user will not be able to perform any activity. but if however you assign a Profile to a user, he will be able to perform the activities/functions, attributed to that Profile.
-role can’t be effectively impact on session (connected) user
views of roles
1)USER_ROLE_PRIVS
2)ALL_ROLE_PRIVS
3)ROLE_ROLE_PRIVS
4)DBA_ROLE_PRIVS
Creating role
s-1:
logged on sysdba user and created a new user called ‘Karan’

SQL> create user karan identified by karan default tablespace users quota 5m on users;

s-2:
Now i ‘m open the new session try to log on Karan users

oracle@oracle11g~]export ORACLE_SID=orcl
oracle@oracle11g~]sqlplus Karan/karan
ORA-01045 user karan lacks CREATE SESSION privilege logon denied

here we have to grant the user creation session system privilege or in order to grant role
s-3
let us go to sysdba login and grant connect role to karan

SQL>grant connect to karan;

s-4;
logon karan user

SQL> sqlplus Karan/karan
connected instance
SQL>select * from Session_roles;
ROLE
 --------
 CONNECT

let us karan user try to create table

SQL> create table r (sal number);

sql error insufficient privileges
which means karan user doesn’t have create table system privilege
s-5
so let us creating role query without password

SQL> create role manager;

grant system privileges to manager

SQL> grant create table, create view to manager;

assign the role manager to karan

SQL> grant manager to Karan;

we can create role with password also

SQL> create role accounts identified by acco123;

grant object privilge

SQL> grant select on scott.emp to accounts;

s-6
after assign Manager role let us try create table from karan side

SQL> create table  r (sal number);

sql error ORA-01031 insufficient privileges
the same error has throughout because role will get impact the user not immediately while user session is active so disconnect the session again we can logged in as Karan user

SQL> create table  r (sal number);
created

check our roles

SQL> select * from Session_roles;

ROLE
——–
CONNECT
MANAGER

Assign one role into another
s-1
logon sys user grant the role accounts into manager

SQL> grant  accounts to manager;

in above we assigned role accounts into manager
s-2
let us check karan user disconnect karan user session and again logon

SQL> select * from scott.emp;

it’s working fine
so check roles which has been assigned to karan

SQL> select * from Session_roles;

ROLE
——–
CONNECT
MANAGER
ACCOUNTS
Drop roles
s-1;
logon on sys user and drop the roles

SQL> drop role manager;

s-2
check the karan session after drop manager role

SQL>select * from Session_roles;

ROLE
——–
CONNECT
ACCOUNTS
Manager role has been gone which means once the role is dropped its impact simultaneously to assigned users

4. Managing Profiles

what is profile ?
– Profile is a set of limits on database resources
– Once we assign the user with in the profile then that user cannot exceeds the limits
– Before creating profile we must enable resource_limit parameter
Resource Limit
– Resource limits are enforced in database profiles
– Profiles only take effect when resource limits are “turned on” for the database as a whole
Check resource_limit

SQL> show parameter resource_limit

NAME TYPE VALUE
———————————— ———– ———
resource_limit boolean FALSE

enable these parameter

SQL> Alter system set resource_limit=TRUE;
System altered.

check the parameter again

SQL&gt; show parameter resource_limit;

NAME TYPE VALUE
———————————— ———– ———
resource_limit boolean TRUE

Resource Parameters
*SESSION_PER_USER – specify the no of concurrent session allow 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 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.

Creating Profile Resource Parameters

SQL> Create profile MY_TEST LIMIT
SESSIONS_PER_USER 2
       IDLE_TIME 5
       CONNECT_TIME 10;

in above i’m created one profile along with limits then i created a user for profile

SQL> create user SAM identified by oracle;

Assign the user to profile

SQL&gt;alter user SAM profile MY_TEST;
User altered.

right now i ll login as SAM user and over limitation as we assigned means already opened two sessions and i tired connect third session it ll throw an error

sqlplus SAM
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Nov 26 15:57:23 2007
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Enter password:
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Lets go to 2nd step IDLE_TIME.Here we go again

SQL> select * from tab;
select * from tab
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again

here my session idle time is more than 5 mins that why oracle server kill mine session.
To view profile limitations

SQL> select * from dba_profiles where profile='MY_TEST';

PROFILE RESOURCE_NAME RESOURCE LIMIT
—————————— ——————————– ——– ——
———————————
MY_TEST COMPOSITE_LIMIT KERNEL DEFAUL
MY_TEST SESSIONS_PER_USER KERNEL 2
MY_TEST IDLE_TIME KERNEL 5
MY_TEST CONNECT_TIME KERNEL 10

PASSWORD MANAGEMENT
*FAILED_LOGIN_ATTEMPTS – Maximum times the user is allowed in fail login before locking the user account * 10
*PASSWORD_LIFE_TIME :Number of days the password is valid before expiry * 108 days
*PASSWORD_REUSE_TIME :Number of day 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_LOCK_TIME :Number of days the user account remains locked after failed login * 1 day
*PASSWORD_GRACE_TIME :Number of grace days for user to change password * 7 days
*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

check the profile
only DBA role person to view this

SQL> describe DBA_PROFILES 

Name Null? Type
————- ——– ————-
PROFILE NOT NULL VARCHAR2(30)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE VARCHAR2(8)
LIMIT VARCHAR2(40)

SQL> select * from dba_profiles ;

RESOURCE_NAME RESOURCE_TYPE LIMIT
————————— ————- ————–
COMPOSITE_LIMIT KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED

FAILED_LOGIN_ATTEMPTS PASSWORD 10
PASSWORD_GRACE_TIME PASSWORD 7
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_LOCK_TIME PASSWORD 1
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL

Alter Profile
-We can alter the profile once we created

SQL> alter profile MY_TEST LIMIT SESSIONS_PER_USER 1;
system altered.

View the profile

SQL> select * from dba_profiles where profile='MY_TEST';

PROFILE RESOURCE_NAME RESOURCE LIMIT
—————————— ——————————– ——– ——
———————————
MY_TEST COMPOSITE_LIMIT KERNEL DEFAUL
MY_TEST SESSIONS_PER_USER KERNEL 1
MY_TEST IDLE_TIME KERNEL 5
MY_TEST CONNECT_TIME KERNEL 10

Drop Profile
– Drop profile using “Drop Profile” command
– We can drop “Default Profile”
– If the user has been assigned to profile we can’t drop the profile so we use the CASCADE it drop the profile and it ll change user into default profile

SQL> DROP PROFILE MY_TEST;

ERROR at line 1:
ORA-02382: profile ACCOUNTANT has users assigned, cannot drop without CASCADE
SQL> DROP PROFILE MY_TEST CASCADE;

 

 

Leave a Reply