Database Systems

Lecture 4

Database Security

- Concept Manual : Chapter 20

- Database Security Manual : Chapters 5,10
- SQL Reference : Chapter 17,18

Lecturer : Assoc Professor Bela Stantic

Database Systems Slide 1

 Introduction to Database Security

 Database security entails allowing or disallowing user actions on the

database and the objects within it.

 Oracle uses schemas and security domains to control access to data and
to restrict the use of various database resources.

 Oracle provides comprehensive discretionary access control, which

regulates all user access to named objects through privileges.

 A Privilege is permission to access a named object in a prescribed

manner; for example, permission to query a table.

 Privileges are granted to users at the discretion of other users.

 Oracle provides for easy and controlled privilege management through

roles. Roles are named groups of related privileges that you grant to users
or other roles.

Database Systems Slide 2

 Database Users and Schemas

 Each database has a list of user names.

 To access a database, a user must use a database application and attempt a connection with a
valid user name of the database.

 Each user name has an associated password to prevent unauthorized use.

 Within each database a user name must be unique with respect to other user names and roles.
A user and role cannot have the same name.

 Furthermore, each user has an associated schema.

 Within a schema, each schema object must have a unique name

Database Systems Slide 3

 Security Domain

 Each user has a security domain — a set of properties that

determine such things as:

1. The actions (privileges and roles) available to the user >>

Controlled by the granting of privileges and roles.

2. Storage and tablespace quotas (available disk space) for the

user >> Assigned by the DBA at schema creation or by
subsequent modification.

3. The system resource limits (for example, CPU processing time)

for the user >> Controlled by assigning a Profile to the user.

Database Systems Slide 4

 Creating Users

 You create a database user with the CREATE USER statement.

 To create a user, you must have the CREATE USER system

privilege.

 Because it is a powerful privilege, a DBA or security administrator is

normally the only user who has the CREATE USER system
privilege.

 A newly created user cannot connect to the database until granted

the CREATE SESSION system privilege.

Database Systems Slide 5

 CREATE USER - Syntax

Database Systems Slide 6

 CREATE USER– An example

CREATE USER bela

IDENTIFIED BY bela99
DEFAULT TABLESPACE DBS_space
QUOTA 10M ON DBS_space
TEMPORARY TABLESPACE temp_space
PROFILE STAFF
PASSWORD EXPIRE
;

Database Systems Slide 7

 Helpful Data Dictionary Views

 USER_USERS
 ALL_USERS
 DBA_USERS
 USER_TS_QUOTAS
 DBA_TS_QUOTAS

 Information about the database user who is currently logged on, can
be seen by examining the USER_USERS data dictionary view.

Database Systems Slide 8

 Privileges and Roles

 A privilege is a right to run a particular type of SQL statement.

 Some examples of privileges include the right to:

– Connect to the database (create a session)
– Create a table in your schema
– Select rows from someone else’s table
– Run someone else’s stored procedure

 Roles are created by users (usually administrators) to group together

privileges or other roles.

 Roles are a means of facilitating the granting of multiple privileges or

roles to users.

Database Systems Slide 9

 Privileges and Roles

Database Systems Slide 10

 Privileges

 There are two distinct categories of privileges:

– System Privileges
– Schema Object Privileges

 System Privileges
– A system privilege is the right to perform a particular action, or to
perform an action on any schema objects of a particular type.
– For example, the privileges to create tablespaces and to delete
the rows of any table in a database are system privileges.
– There are over 100 distinct system privileges.

 Schema Object Privileges

– A schema object privilege is a privilege or right to perform a
particular action on a specific schema object.
– Different object privileges are available for different types of
schema objects. For example, the privilege to delete rows from
the departments table is an object privilege.

Database Systems Slide 11

 System Privileges

A list of all system privileges is included in a data dictionary view

SYSTEM_PRIVILEGE_MAP
SQL> select name from system_privilege_map;
CREATE EXTERNAL JOB ALTER ANY RULE SET ALTER ANY DIMENSION DROP ANY DIRECTORY CREATE ROLE UPDATE ANY TABLE
CHANGE NOTIFICATION CREATE ANY RULE SET CREATE ANY DIMENSION CREATE ANY DIRECTORY DROP PUBLIC DATABASE LINK INSERT ANY TABLE
READ ANY FILE GROUP CREATE RULE SET CREATE DIMENSION DROP ANY MATERIALIZED VIEW CREATE PUBLIC DATABASE LINK SELECT ANY TABLE
MANAGE ANY FILE GROUP EXECUTE ANY EVALUATION CONTEXT UNDER ANY TABLE ALTER ANY MATERIALIZED VIEW CREATE DATABASE LINK COMMENT ANY TABLE
MANAGE FILE GROUP DROP ANY EVALUATION CONTEXT EXECUTE ANY INDEXTYPE CREATE ANY MATERIALIZED VIEW SELECT ANY SEQUENCE LOCK ANY TABLE
EXEMPT IDENTITY POLICY ALTER ANY EVALUATION CONTEXT GLOBAL QUERY REWRITE CREATE MATERIALIZED VIEW DROP ANY SEQUENCE DROP ANY TABLE
CREATE ANY SQL PROFILE CREATE ANY EVALUATION CONTEXT QUERY REWRITE GRANT ANY PRIVILEGE ALTER ANY SEQUENCE BACKUP ANY TABLE
ADMINISTER ANY SQL TUNING SET CREATE EVALUATION CONTEXT UNDER ANY VIEW ANALYZE ANY CREATE ANY SEQUENCE ALTER ANY TABLE
ADMINISTER SQL TUNING SET GRANT ANY OBJECT PRIVILEGE DROP ANY INDEXTYPE ALTER RESOURCE COST CREATE SEQUENCE CREATE ANY TABLE
ALTER ANY SQL PROFILE FLASHBACK ANY TABLE ALTER ANY INDEXTYPE DROP PROFILE DROP ANY VIEW CREATE TABLE
DROP ANY SQL PROFILE DEBUG ANY PROCEDURE CREATE ANY INDEXTYPE ALTER PROFILE CREATE ANY VIEW DROP ROLLBACK SEGMENT
SELECT ANY TRANSACTION DEBUG CONNECT SESSION CREATE INDEXTYPE CREATE PROFILE CREATE VIEW ALTER ROLLBACK SEGMENT
MANAGE SCHEDULER SELECT ANY DICTIONARY EXECUTE ANY OPERATOR DROP ANY TRIGGER DROP PUBLIC SYNONYM CREATE ROLLBACK SEGMENT
EXECUTE ANY CLASS RESUMABLE DROP ANY OPERATOR ALTER ANY TRIGGER CREATE PUBLIC SYNONYM DROP USER
EXECUTE ANY PROGRAM EXEMPT ACCESS POLICY ALTER ANY OPERATOR CREATE ANY TRIGGER SYSOPER ALTER USER
CREATE ANY JOB ON COMMIT REFRESH CREATE ANY OPERATOR CREATE TRIGGER SYSDBA BECOME USER
CREATE JOB MERGE ANY VIEW CREATE OPERATOR EXECUTE ANY PROCEDURE DROP ANY SYNONYM CREATE USER
ADVISOR ADMINISTER DATABASE TRIGGER EXECUTE ANY LIBRARY DROP ANY PROCEDURE CREATE ANY SYNONYM UNLIMITED TABLESPACE
ANALYZE ANY DICTIONARY ADMINISTER RESOURCE MANAGER DROP ANY LIBRARY ALTER ANY PROCEDURE CREATE SYNONYM DROP TABLESPACE
EXECUTE ANY RULE DROP ANY OUTLINE ALTER ANY LIBRARY CREATE ANY PROCEDURE DROP ANY INDEX MANAGE TABLESPACE
DROP ANY RULE ALTER ANY OUTLINE CREATE ANY LIBRARY CREATE PROCEDURE ALTER ANY INDEX ALTER TABLESPACE
ALTER ANY RULE CREATE ANY OUTLINE CREATE LIBRARY FORCE ANY TRANSACTION CREATE ANY INDEX CREATE TABLESPACE
CREATE ANY RULE DROP ANY CONTEXT UNDER ANY TYPE FORCE TRANSACTION DROP ANY CLUSTER RESTRICTED SESSION
CREATE RULE CREATE ANY CONTEXT EXECUTE ANY TYPE ALTER DATABASE ALTER ANY CLUSTER ALTER SESSION
IMPORT FULL DATABASE DEQUEUE ANY QUEUE DROP ANY TYPE AUDIT ANY CREATE ANY CLUSTER CREATE SESSION
EXPORT FULL DATABASE ENQUEUE ANY QUEUE ALTER ANY TYPE ALTER ANY ROLE CREATE CLUSTER AUDIT SYSTEM
EXECUTE ANY RULE SET MANAGE ANY QUEUE CREATE ANY TYPE GRANT ANY ROLE DELETE ANY TABLE ALTER SYSTEM
DROP ANY RULE SET DROP ANY DIMENSION CREATE TYPE DROP ANY ROLE

166 rows selected.

Database Systems Slide 12

 Granting System Privileges
, ,
GRANT System_priv TO user

role role

PUBLIC

WITH ADMIN OPTION

 Where:
– System_priv Is a system privilege to be granted
– Role Is a role name to be granted
– TO Identifies the users or roles to which the system privileges and roles are
granted
– PUBLIC Grants system privileges or roles to all users
– WITH ADMIN OPTION Allows the grantee to grant the system privilege or role
to other users or roles. It you grant a role WITH ADMIN OPTION, the grantee
can also alter or drop the role.
– To grant a system privilege, you must have been granted the privilege
with the ADMIN OPTION.

Database Systems Slide 13

 Revoking System Privileges
 In order to revoke system privileges, it is necessary to use the
REVOKE command.

 Syntax:
, ,

REVOKE System_priv FROM user

role
role

PUBLIC

 The options for REVOKE have the same meaning as for the
GRANT command
 A system privilege can be revoked by a user other than the
grantor

Database Systems Slide 14

 System Privileges Do Not Cascade !

GRANT REVOKE RESULT

A B C A B C A B C

Database Systems Slide 15

 Querying System Privileges

 Some important Data Dictionary Views :

– ALL_SYS_PRIVS
– SESSION_PRIVS
– USER_SYS_PRIVS
– DBA_SYS_PRIVS
– SYSTEM_PRIVILEGE_MAP

 Example: The system privileges that have been granted can be

displayed by querying the DBA_SYS_PRIVS data dictionary view.

SQL> SELECT * FROM SYS_DBA_PRIVS;

GRANTEE_NAME PRIVILEGE ADM

-------------- ---------------- ------
SCOTT CREATE SESSION NO
SCOTT CREATE SYNONYM NO
SCOTT CREATE TABLE NO
SCOTT CREATE VIEW NO
SYS UNLIMITED TABLESPACE YES
SYSTEM UNLIMITED TABLESPACE YES
Database Systems Slide 16
 Object Privileges

Note 1: Oracle Database

treats a Java class, source,
or resource as if it were a
procedure for purposes of
granting object privileges.
Note 2: Job scheduler
objects are created using
the DBMS_SCHEDULER
package. Once these
objects are created, you
can grant the EXECUTE
object privilege on job
scheduler classes and
programs. You can grant
ALTER privilege on job
scheduler jobs, programs,
and schedules.
Note 3: The DELETE,
INSERT, and UPDATE
privileges can be granted
only to updatable
materialized views.

Database Systems Slide 17

 Granting Object Privileges
, ,

GRANT Object_priv ON Object TO user

Schema. role
PUBLIC

WITH GRANT OPTION

Where:
– Object_priv: Is an object privilege to be granted
– ON: Identifies the object on which the privileges are granted. if the
“schema.” prefix is not used then ORACLE assumes the current
user’s schema.
– TO: Identifies the users or roles to which the object privilege is
granted
– PUBLIC: Grants object privileges to all users
– WITH GRANT OPTION : Allows the grantee to grant the object
privileges to other users and roles. The grantee must be a user or
PUBLIC. GRANT OPTION cannot be granted to a role.
Database Systems Slide 18
 Object Privileges Cascade

 Grantors can revoke privileges from only those users to whom they
had granted the privileges in the first place.
 Revoking an object privilege may have a cascading effect that
should be investigated before a REVOKE statement is issued.

GRANT REVOKE RESULT

A B C A B C A B C

Database Systems Slide 19

 Displaying Object Privileges

 The object privileges that have been granted can be displayed by

querying the data dictionary.

 Available to DBAs

– DBA_TAB_PRIVS All privileges on all tables in the database

 Available to the User

– USER_TAB_PRIVS Privileges on tables for which the user Is the

owner, grantor, or grantee

– USER_TAB_PRIVS_MADE All privileges on tables owned by the

user

– USER_TAB_PRIVS_RECD All privileges on tables for which

the user is the grantee

Database Systems Slide 20

 Roles

 Managing and controlling privileges is made easier by using roles,

which are named groups of related privileges that you grant, as a
group, to users or other roles.

 Within a database, each role name must be unique, different from all
user names and all other role names.

 Unlike schema objects, roles are not contained in any schema.

Therefore, a user who creates a role can be dropped with no effect
on the role.

 Roles ease the administration of end-user system and schema

object privileges.

 However, roles are not meant to be used by application developers,

because the privileges to access schema objects within stored
programmatic constructs must be granted directly.

Database Systems Slide 21

 Creating Roles

 In order to create database roles it is necessary to use the

CREATE ROLE command.
 Syntax of the CREATE ROLE Command
CREATE ROLE role
NOT IDENTIFIED

IDENTIFIED BY password
EXTERNALLY
 Where:
– Role Name of the role to be created
– NOT IDENTIFIED Users granted the role do not need to be verified by
ORACLE to enable it
– IDENTIFIED Indicates that the users granted the role must be verified
by ORACLE to enable the role
– BY password Specifies the password that authorizes enabling the role
– EXTERNALLY Specifies that ORACLE will verify user access to the role
using an operating system utility

Note : If the IDENTIFIED option is chosen, users can enable/disable the role
by using the SET ROLE command.

Database Systems Slide 22

 Benefits of Using Roles
 Reduced privilege administration
– Rather than granting the same set of privileges explicitly to several users,
you can grant the privileges for a group of related users to a role, and
then only the role needs to be granted to each member of the group.
 Dynamic privilege management
– If the privileges of a group must change, then only the privileges of the
role need to be modified. The security domains of all users granted the
group’s role automatically reflect the changes made to the role.
 Selective availability of privileges
– You can selectively enable or disable the roles granted to a user. This
allows specific control of a user’s privileges in any given situation.
 Application awareness
– The data dictionary records which roles exist, so you can design
applications to query the dictionary and automatically enable (or disable)
selective roles when a user attempts to run the application by way of a
given user name.

Database Systems Slide 23

 Displaying Information About Roles

The following data dictionary views contain information about

privileges granted to roles, roles granted to users etc.

 ROLE_SYS_PRIVS System privileges granted to roles

 ROLE_TAB_PRIVS Table privileges granted to roles
 ROLE_ROLE_PRIVS Roles granted to other roles
 SESSION_ROLES Roles that the user currently has enabled
 USER_ROLE_PRIVS Roles granted to the user
 DBA_ROLES All roles existing in the database
 DBA_SYS_PRIVS System privileges granted to users and to
roles

Database Systems Slide 24

 Storage Settings and Quotas

You can direct and limit the use of disk space allocated to the database for
each user, including default and temporary tablespaces and tablespace
quotas.

 Default Tablespace
– Each user is associated with a default tablespace. When a user creates a
table, index, or cluster and no tablespace is specified, the object is created in
user’s default tablespace.
 Temporary Tablespace
– Each user has a temporary tablespace. When a user runs a SQL statement
that requires the creation of temporary segments, the user’s temporary
tablespace is used.
 Tablespace Quotas
– It is possible to limit the collective amount of disk space available to the
objects in a schema. Quotas (space limits) can be set for each tablespace
available to a user. This permits selective control over the amount of disk
space that can be consumed by the objects of specific schemas.

Database Systems Slide 25

 Altering Users

Some Examples

Alter user bela

default tablespace
DBS_SPACE;

Alter user bela

quota 10M on
DBS_space;

Alter user bela

quota unlimited on
DBS_SPACE;

Alter user bela

identified by pass22;

Database Systems Slide 26

 Profiles and Resource Limits
 Each user is assigned a profile that specifies limitations on several system
resources available to the user, including the following:

– Number of concurrent sessions the user can establish,

– CPU processing time available for the user’s session and a single call to
Oracle made by a SQL statement,

– Amount of logical I/O available for the user’s session and a single call to
Oracle made by a SQL statement,

– Amount of idle time available for the user’s session,

– Amount of connect time available for the user’s session

– Password restrictions:
 Account locking after multiple unsuccessful login attempts
 Password expiration and grace period
 Password reuse and complexity restrictions

Database Systems Slide 27

 Towards Better Security

 In order to build good security, you must confront many tasks.

 Good security requires physical access control, reliable personnel,

trustworthy installation and configuration procedures, secure
communications, and control of database operations such as
selection, viewing, updating, or deleting database records.

 Since some of these requirements involve applications or stored

procedures as well as human action, security procedures must also
account for how these programs are developed and dealt with.

 Practical concerns must also be met: minimizing the costs of

equipment, personnel, and training; minimizing delays and errors;
and maximizing rapid and thorough accountability. Scalability, too, is
an important and independent practical criterion that should be
assessed for each proposed solution.

Database Systems Slide 28

 Towards Better Security

1. Install only what is required.

2. Lock and expire default user accounts.
3. Change default user passwords.
4. Enable data dictionary protection.
5. Practice the principle of least privilege.
6. Enforce access controls effectively.
7. Restrict operating system access.
8. Secure your network.
9. Apply all security patches and workarounds.
10. Have an strong password management policy.

Database Systems Slide 29