Database Security

and
User Management
Objectives
To discuss database security principles (AAA)
To create and manage database user accounts:
 Authenticate users
 Assign default storage areas (tablespaces)

 To create and manage profiles:

 Implement standard password security features
 Control resource usage by users

To discuss authorisation implementation:

 How to grant and revoke privileges
 How to create and manage roles

3
Database Security Overview
Fundamental data security requirements:
 Confidentiality
 Integrity
 Availability

Major aspects of database security:

 Authentication
 Authorisation
 Auditing

5
Authentication
Authentication verifies the user’s identity
Authentication methods can be classified as:
 something you know (password)
 something you are (biometric)
 something you have (smart card)
Basic authentication
 Database user identified by a password
 Database user identified by the operating system
Strong authentication
 Confirming the identity of the user with something
other than a password: smart cards, biometrics,
certificates, etc.
6
Authorisation
Authorisation includes primarily two
processes:
 Giving users certain permissions to access, process,
or alter data
 Applying varying limitations on user access or
actions
The limitations placed on (or removed from)
users can apply to
 objects such as schemas, tables, or rows
 to resources such as time (CPU, connect, or idle
times)

7
 Auditing
Auditing
 is the monitoring and recording of selected user
database actions
 can be based on individual actions, such as the type of
SQL statement executed, or on combinations of data
that can include the user name, application, time, and
so on
 can audit both successful and failed activities
 can be done as a high level monitoring as well as Fine-
grained auditing
● But auditing of any type increases the amount of work
that the database must do !
8
Database User Accounts

Each database user account

has:
 A unique username
 An authentication method
 A default tablespace
 A temporary tablespace
 A tablespace quota
 A user profile

9
Creating User in SQL*Plus
 Use CREATE USER SQL statement to create a database
user account
 To create a user, you must have the CREATE USER
system privilege
 Because it is a powerful privilege, a database
administrator or security administrator is usually the only
user who has the CREATE USER system privilege

Example:
SQL> CREATE USER jsmith IDENTIFIED BY pass4You
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE clerk_profile;

10
Authenticating Users
Password
External
 This is authentication by a method outside the database
(operating system, biometrics, etc.)
Global
 Using LDAP services,
Oracle Internet Directory

11
Assigning a Default Tablespace to the User
Each user should have a default tablespace
 When user creates an object the database stores the
object in the default user's tablespace (unless DDL
command contains different tablespace)
 The default setting for the default tablespaces of all users
is set in the initialization parameter file
 At the creation of the database it might be set to SYSTEM
tablespace, but it is strongly recommended to change
default tablespace from SYSTEM to other tablespace, for
example USERS
 By separating the user data from the system data, you
reduce the likelihood of problems with the SYSTEM
tablespace, which can in some circumstances cause the
entire database to become nonfunctional

13
Assigning a Tablespace Quota to the User
By default, a user has no quota on any tablespace
in the database
Assigning a quota accomplishes the following:
 Users with privileges to create certain types of objects can
create those objects in the specified tablespace
 Oracle Database limits the amount of space that can be
allocated for storage of a user's objects within the specified
tablespace to the amount of the quota
 You can assign a user either individual quotas for a
specific amount of disk space in each tablespace or an
unlimited amount of disk space in all tablespaces
 Specific quotas prevent a user's objects from using too
much space in the database

14
Assigning a Temporary Tablespace to the User
DBA should assign each user a temporary
tablespace
When a user executes a SQL statement that requires
a temporary segment, the database stores the
segment in the temporary tablespace of the user
If DBA does not explicitly assign the user a
temporary tablespace, then the database assigns the
user the default temporary tablespace that was
specified:
 at database creation
 by an ALTER DATABASE statement at a later time

15
 Profiles
A profile is a named set of resource limits and
password parameters that restrict database usage
and instance resources for a user.
Each user can have only one profile, and creating a
new one supersedes an earlier version.
Profiles are used to manage the resource limits of
related users.
DBA needs to create and manage user profiles only if
resource limits are a requirement of your database
security policy.

16
 Creating profiles
 Syntax:
CREATE PROFILE profile_name
LIMIT { resource_parameters | password_parameters };

 resource_parameters:
 SESSIONS_PER_USER (the number of concurrent logins that can be made to the same user account)
 CONNECT_TIME (in minutes, the max duration of a session)
 IDLE_TIME (in minutes the maximum time a session can be idle)
 CPU_PER_CALL ( the CPU time in centiseconds that a session’s server process is allowed to
use to execute one SQL statement before the it forcibly terminated)
 etc.
 password_parameters:
 FAILED_LOGIN_ATTEMPTS
 PASSWORD_LOCK_TIME (the number of days an account will be locked after the specified
number of consecutive failed login attempts)
 PASSWORD_LIFE_TIME (the number of days before the password expires)
 PASSWORD_REUSE_TIME (the number of days before a password can be reused)
 PASSWORD_REUSE_MAX (the number of times a password can be reused)
 If either parameter is specified as UNLIMITED, then the user can never reuse a password
 If both parameters are set to UNLIMITED, then Oracle Database ignores both, and the user can
reuse any password at any time
17
 Creating Profiles Examples
SQL> CREATE PROFILE app_prof
LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CONNECT_TIME 45;

FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 45
PASSWORD_REUSE_MAX 4

 The user with this profile:

 can have any number of concurrent sessions ; in a single session, the
user can consume an unlimited amount of CPU time; a single session
cannot last for more than 45 minutes
 will be locked after 3 failed login attempts; it will be locked for 1 day; the
password will expire in 60 days; the user has to wait for 45 days before
re-using the same password; the same password can be reused 4 times
18
 Profile Enforcement
 Profiles can be assigned only to users and not to other
profiles
SQL> CREATE USER user1 IDENTIFIED BY passw1
PROFILE app_prof;

SQL> ALTER USER sh

PROFILE new_profile;
 Profile resource limits are enforced only when resource
limitation is enable for the database
 Limitation can be enabled
 using the RESOURCE_LIMIT initialization parameter (before
starting up the database )
 using the ALTER SYSTEM statement (while database is open )
SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
19
User Authorisation
Authorisation determines what types of objects,
privileges, and resources the user is permitted to
access or use.
The resources are limited by a profile attached to
the user.
Access to the objects and structures in the
database is controlled by setting an individual user
privileges, or collection of privileges – roles.

21
 Privileges
There two types of privileges:
 System: Enables users to perform particular actions in
the database
 Usually corresponds to the permission to run DDL commands
 Object: Enables users to access and manipulate a
content of a specific object
 Usually corresponds to the permission to run DML commands

HR_DBA

Object privilege: System privilege:

Update ON employees Create session
22
Granting System Privileges

SQL> GRANT create session TO user123;

SQL> GRANT create table TO user123;

SQL> GRANT alter any sequence TO user456;

SQL> GRANT create table, create sequence, create view

TO user123, user456;

23
Granting System Privileges WITH ADMIN OPTION
WITH ADMIN OPTION

SYS HR_DBA scott

CREATE TABLE
region (id … CREATE TABLE
GRANT stats (id …
create table,
TO HR_DBA
WITH ADMIN OPTION; GRANT
create table
TO scott;

24
 Revoking System Privileges
To revoke a system privilege, you must have been
granted the privilege with the ADMIN OPTION
You can revoke any privilege if you have the
GRANT ANY PRIVILEGE system privilege
 Syntax:
REVOKE { system_privilege | role | ALL PRIVILEGES }
FROM
{ user [ IDENTIFIED BY password ] | role | PUBLIC } ;

 Example:

SQL> REVOKE CREATE TABLE,DROP TABLE

FROM hr_user, oe_user;

25
Revoking System Privileges
SYS HR_DBA scott
GRANT GRANT
Users
WITH
ADMIN OPTION
GRANT GRANT Privileges

Objects

SYS HR_DBA scott

REVOKE Users

GRANT GRANT Privileges

Objects
26
Understanding Object Privileges
Object privileges vary from object to object
An owner has all the privileges on the object
An owner can grant specific privileges on the
owner’s object to another user or to PUBLIC
Object privileges cannot be granted along with
system privileges and roles in the
same GRANT statement
Some object privileges are only applicable to
certain types of objects
 For example, the DELETE privilege only makes sense
with table or views, but not sequences
27
Granting Object Privileges
To grant object privileges you must specify the name of the
object as a part of the GRANT statement

SQL> GRANT SELECT ON employees TO user123;

SQL> GRANT SELECT, UPDATE ON employees TO user123;

SQL> GRANT SELECT ON employees TO user123;

SQL> GRANT SELECT ON departments TO user123;

SQL> GRANT UPDATE (location_id) ON departments

TO user123, mgr1;

28
Revoking Object Privileges
Syntax:

REVOKE {privilege [, privilege...]|ALL}

ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS | FORCE];

Example:

SQL> REVOKE select, insert ON departments

FROM my_scott;

Revoke select succeeded.

29
Cascade Revoke
SYS HR_DBA scott
GRANT GRANT
Users
WITH
GRANT OPTION
Privileges GRANT GRANT

Objects

SYS HR_DBA scott

REVOKE
Users

GRANT
... GRANT
Privileges

Objects
30
Roles

Users

Manager Role

1 2 3 4 Privileges 1 2 3 4

Allocating privileges Allocating privileges

without a role with a role

32
About Roles
The role is a named group of related privileges
 The role simplifies the managing multiple system and
object privileges
You create roles, grant system and object
privileges to the roles, and then grant roles to
users
 You can also grant roles to other roles
 Unlike schema objects, roles are not contained in
any schema

33
 Managing Roles
Creating Roles SQL> CREATE ROLE clerk;

SQL> CREATE ROLE manager;

Assigning privileges to a role

SQL> GRANT CREATE TABLE,CREATE VIEW TO manager;
SQL> GRANT SELECT, UPDATE ON orders TO clerk;

Assigning roles to users

SQL> GRANT manager TO user123;
SQL> GRANT clerk TO user123, user555, user767, user999;

Assigning roles to roles SQL> GRANT clerk TO manager;

 Question: What privileges role manager will have in this case?

34
 Oracle Database Predefined Roles
Oracle Database has many predefined roles; the following
three are widely used:
 CONNECT (changed since 10g Release 2)
 Enables a user to connect to the database
 RESOURCE
 Enables a user to create, modify, and delete certain types of
schema objects in the schema associated with that user
 This role grants set of the system privileges (CREATE TABLE ,
CREATE PROCEDURE, etc)
 DBA
 Enables a user to perform most administrative functions,
including creating users and granting privileges; creating and
granting roles; creating, modifying, and deleting schema
objects in any schema; and more, but does not include the
privileges to start or shut down the database instance
35
QUESTIONS

36