Chapter 6 database security
This presentation is the property of its rightful owner.
Sponsored Links
1 / 150

Chapter 6 - Database Security PowerPoint PPT Presentation


  • 85 Views
  • Uploaded on
  • Presentation posted in: General

Chapter 6 - Database Security. Stallings Chp. 5. Levels of a Database System. END USER. EXTERNAL LEVEL. EXTERNAL VIEW. …. EXTERNAL VIEW. External / conceptual mapping. CONCEPTUALLEVEL. CONCEPTUAL SCHEMA. Conceptual / internal mapping. INTERNALLEVEL. INTERNAL SCHEMA. STORED DATABASE.

Download Presentation

Chapter 6 - Database Security

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Chapter 6 database security

Chapter 6 - Database Security

Stallings Chp. 5

Prof. Ehud Gudes Security Ch6


Levels of a database system

Levels of a Database System

END USER

EXTERNAL LEVEL

EXTERNAL VIEW

EXTERNAL VIEW

External / conceptual mapping

CONCEPTUALLEVEL

CONCEPTUAL SCHEMA

Conceptual / internal mapping

INTERNALLEVEL

INTERNAL SCHEMA

STORED DATABASE

Prof. Ehud GudesSecurity Ch 6


A typical architecture of a dbms

A Typical Architecture of a DBMS

Prof. Ehud GudesSecurity Ch 6


A typical dbms architecture

A typical DBMS Architecture


Introduction to db security

Introduction to DB Security

  • Secrecy: Users should not be able to see things they are not supposed to.

    • e.g., a student cannot see other student’s grades.

  • Integrity: Users should not be able to modify things they are not supposed to.

    • e.g., only instructors can assign grades.

    • Also, the DBMS should protect the database from non-malicious integrity errors, i.e Concurrency & Recovery issues

  • Availability: users should be able to see and modify things they are allowed to.

Prof. Ehud GudesSecurity Ch 6


Integrity problems in databases

Integrity Problems in Databases

  • Checks on values – single record checks, before and after update checks, etc.

  • Various integrity constraints – primary keys, unique checks, foreign keys, etc.

  • General integrity constraints – Constraints, Asserts, Triggers.

  • Consistency problems as a result of concurrent execution of transactions – CC protocols such as Two-Phase Locking

  • Integrity problems as results of system failure – Log and Recovery

Prof. Ehud GudesSecurity Ch 6


Security policies and mechanism reminder

Security - Policies and Mechanism - Reminder

  • Policies – general guidelines on authorization in the system, examples:

    • Students can see their grades

    • Only instructors can change grades

  • Mechanisms – techniques to enforce the policies

    • Access control

    • Encryption

Prof. Ehud GudesSecurity Ch 6


Categories of security policies reminder

Categories of Security Policies - Reminder

  • Mandatory vs. Discretionary (Need to Know).

  • Ownership vs. Administration

  • Centralized vs. Distributed

  • Close vs. Open

  • Name, Content or Context dependent

  • Individual, Group or Role based

  • Information Flow Control based

Prof. Ehud GudesSecurity Ch 6


Dac the access matrix model

DAC - The Access Matrix Model

  • Subjects

    - users, groups, applications, transactions

  • Objects

    - Files, programs, databases, relations, URLs

  • Access-types

    - Read, write, create, copy, delete, execute, kill

  • Authorization commands

    - enter, remove, transfer

  • Authorizers

    - Owners, users, administrators

Prof. Ehud GudesSecurity Ch 6


The access matrix model

The Access Matrix Model

Compatibility Lists

Access Lists

Prof. Ehud GudesSecurity Ch 6


Mandatory policy bell and lapadula model

Mandatory Policy - Bell and LaPadula Model

  • Objective of the model: trying to keep secrets and avoid illegal flow

  • Both subjects and objects are assigned security levels:

  • Public, Confidential, Secret, Top Secret

  • dominance relationship between security levels: ‚  ‘

  • • Simple Security Property:

  • Successful read access: Clearance (S)  Class (O)

  • • *-Property:

  • Successful write access: Class (O)  Clearance (S)

Prof. Ehud GudesSecurity Ch 6


Dbms security policies

DBMS Security Policies

  • Usually DAC, sometimes Mandatory (BLP)

  • Ownership, Usually (SQL) Distributed

  • Close

  • Name, Content dependent (using Views)

  • Individual, or Role based

  • Inference control policies

  • Note, assumes security at lower layers, i.e:

    OS, Hardware and User authentication

Prof. Ehud GudesSecurity Ch 6


Database access control

Database Access Control

  • DBMS provide access control for database

  • assume have authenticated user

  • DBMS provides specific access rights to portions of the database

    • e.g. create, insert, delete, update, read, write

    • to entire database, tables, selected rows or columns

    • possibly dependent on contents of a table entry

  • can support a range of policies:

    • centralized administration

    • ownership-based administration

    • decentralized administration


Dbms discretionary access control

DBMS Discretionary Access Control

  • Based on the concepts of access rights or privileges for objects (tables and view), and mechanisms for giving users privileges (and revoking privileges).

  • Creator of a table or a view automatically gets all privileges on it.

    • DBMS keeps track of who subsequently gains and loses privileges, and ensures that only requests from users who have the necessary privileges (at the time the request is issued) are allowed.

Prof. Ehud GudesSecurity Ch 6


History of relational systems

History of Relational Systems

  • Codd’s paper – early 70s

  • Two research systems: Berkeley Ingres and IBM System R – late 70s

  • SQL was developed based on System R

  • All relational systems today are SQL (92,99) compatible

  • Security:

    • Ingres – DAC, centralized, Rule-based

    • System R – DAC, Distributed, View based

  • SQL security follows System R security

Prof. Ehud GudesSecurity Ch 6


Security in ingres

Security in INGRES

  • Centralized, DBA enters authorization rules

  • The query predicate is combined with the relevant rule predicates to derive a modified query

  • The concept of query modification – partial results are possible

  • Difficult to manage a large group of users and rules

    .

Prof. Ehud GudesSecurity Ch 6


Protection in ingres

CONVERT TO INTERNAL FORM

CONVERT TO INTERNAL FORM

QUEL

query

VALIDATE AND MODIFY

Protection in Ingres

DBA

PROTECTION INTERACTIONS

User

SHARED RELATIONS

PROTECTION

Prof. Ehud GudesSecurity Ch 6


Protection in ingres cont

Protection in Ingres, cont.

  • (Rule 1)range of E is employeepermit E to Jones forretrieve (E.name, E.mgr)where E.dept = ‘D1’

  • (Rule 2)range of E is employeepermit E to Jones forretrieve (E.name, E.dept, E.mgr)where E.dept = ‘D1’

  • (Rule 3)permit E to jones for retrieve (E.name, E.sal)where E.mgr = ‘jones’

  • (Rule 4)permit E to jones for retrieve (E.sal)where E.sal < 100000

Prof. Ehud GudesSecurity Ch 6


Protection in ingres cont1

Protection in Ingres, cont.

  • Query1range of E is employeeretrieve (E.name, E.Sal)

  • Only Rule3 matches, query is modified to;

    range of E is employee retrieve (E.name, E.Sal)

    where (E.mgr = ‘Jones’)

  • Query2range of E is employeeretrieve (E.name)

  • Both Rules1,2 and 3 match (see Hovereth)

Prof. Ehud GudesSecurity Ch 6


The concept of views a window over the database

The concept of Views – A Window over the database

+· Performance

Views may be precompiled and optimized

- · Update restrictions

Many views are 'read-only'

+· Query simplicity

Multiple table-queries may be

expressed simply against a view

+ · Structural simplicity

Views can give a user a

'personalized' interpretation

of the database

+ · Security – a user sees only the portion relevant to him

Prof. Ehud GudesSecurity Ch 6


Example database 1

Example Database (1)

manages

1

N

date

function

ssn

title

name

N

M

Employee

Project

subject

dep

Assignment

client

salary

ssn

title

Prof. Ehud GudesSecurity Ch 6


Example database 2

Example Database (2)

Project

Prof. Ehud GudesSecurity Ch 6


Horizontal vertical view

Horizontal / Vertical View

AS query

CREATE VIEW view-name

(-- column_name --)

,

earning_little

emp


Mixed view 1

Mixed View (1)

Prof. Ehud GudesSecurity Ch 6


Mixed view 2

Mixed View (2)

Prof. Ehud GudesSecurity Ch 6


Views are ideal for security

Views are ideal for security...

Views are ideal for security…

value independent controls

(2)

(1), (3), (4)

value dependent controls

statistical controls

(5)

context dependent controls

(6)

Prof. Ehud GudesSecurity Ch 6


The view update problem

The View Update problem

How to translate update on a view to unambiguous update on the Base tables?

Examples:

  • Add an employee with salary >5K into view 1

  • Add an employee to View 2 (null values)

  • Update Salary in View 5.

  • Add a new row into View 4

    Generally the solution is:

    allow updates only on single level views which include the primary key and all non-null attributes

Prof. Ehud GudesSecurity Ch 6


Views and security

Views and Security

  • Enable convenient specification and enforcement of access to portions of the database, which include any horizontal, vertical or join on the Base tables using standard SQL

  • Once a view is defined, the access to it is binary, either yes or no

  • Access control is provided only if access is via the view

  • Distributed administration: users are owners of the views they define and can delegate access further

  • Views are problematic for update

Prof. Ehud GudesSecurity Ch 6


Authorization in sql based systems the grant command

Authorization in SQL based systems – The GRANT command

  • The following privileges can be specified:

    • SELECT: Can read all columns (including those added later via ALTER TABLE command).

    • INSERT(col-name): Can insert tuples with non-null or non-default values in this column.

      • INSERT means same right with respect to all columns.

    • DELETE: Can delete tuples.

    • REFERENCES (col-name): Can define foreign keys (in other tables) that refer to this column.

  • If a user has a privilege with the GRANT OPTION, can pass privilege on to other users (with or without passing on the GRANT OPTION).

  • Only owner can execute CREATE, ALTER, and DROP.

GRANT privileges ON object TO users [WITH GRANT OPTION]

Prof. Ehud GudesSecurity Ch 6


Grant statement

GRANT-Statement

GRANT

SELECT

INSERT

DELETE

UPDATE

(

)

column-name

,

,

ALL PRIVILEGES

·

user-name

ON

base relation

TO

PUBLIC

view relation

WITH

GRANT

OPTION

Prof. Ehud GudesSecurity Ch 6


Revoke statement

REVOKE Statement

Prof. Ehud GudesSecurity Ch 6


Access privileges in different dbmss

Access Privileges in different DBMSs

Prof. Ehud GudesSecurity Ch 6


Grant and revoke of privileges

GRANT and REVOKE of Privileges

  • GRANT INSERT, SELECT ON Employees TO Horatio

    • Horatio can query Employees or insert tuples into it.

  • GRANT DELETE ON Employees TO Yuppy WITH GRANT OPTION

    • Yuppy can delete tuples, and also authorize others to do so.

  • GRANT UPDATE Salary ON Employees TO Dustin

    • Dustin can update (only) the salary field of Employees tuples.

Prof. Ehud GudesSecurity Ch 6


Revoke options

Revoke options

  • Reject (SQL)

  • Non-recursive revocation (Fernandez)

  • Time based recursive revocation (System R)

  • System based recursive revocation (SQL)

Prof. Ehud GudesSecurity Ch 6


Protection in system r

Protection in System R

.

A:GRANT READ ON EMP TO B WITH GRANT OPTION

A:GRANT READ ON EMP TO C WITH GRANT OPTION

B:GRANT READ ON EMP TO X

C:GRANT READ ON EMP TO X

Prof. Ehud GudesSecurity Ch 6


Protection in system r1

Protection in System R

בדוגמה זו רואים כי X קיבל זכויות משני גורמים מ-B ומ-C ולכן שלילת הזכויות מ-B אינה גורמת בהכרח שלילת הזכויות מ-X. אם לדוגמה נבצע את הפקודה

A:REVOKE READ ON EMP FROM B

אזי במקרה של קיום שלילה רקורסיבית (SYSTEM R(נקבל את הגרף באיור -א'

ובמקרה של אי קיום שלילה רקורסיבית (לפי FERNANDEZ )

נקבל את הגרף באיור - ב'.


The privilege dependency graph

The privilege dependency graph

B

4

2

D

E

G

8

5

A

3

7

6

F

C

Prof. Ehud GudesSecurity Ch 6


Revoke in system r

REVOKE IN SYSTEM R

B

2

D

A

3

7

6

F

C

B

4

2

D

E

G

8

5

A

3

7

6

F

C

Revoking a4 (with CASCADE option) will succeed. Authorization a7

will not be revoked, because it is supported by a6, but a5 and a8

Will be revoked. The privilege dependency graph will change.

Prof. Ehud GudesSecurity Ch 6


Revoke in system r1

REVOKE in System R

REVOKE:procedure(grantee, privilege, table, grantor);

comment turn off the grantee’s authorization for privilege obtained from granter;setprivilege = 0 in the (grantee, table, grantor) tuple in SYSAUTH;comment find the minimum timestamp for the grantee’s remaining grantable privilege on table;

m  current timestamp;for eachgranter u such that (grantee, privilege, table, u, grantable) is in SYSAUTH do if privilege  0 andprivilege < mthen m  privilege;

commentrevoke grantee’s grants of privilege on table which were made before time m;

For each user u such that (u, privilege, table, grantee) is in SYSAUTH do if privilege < mthenREVOKE (u, privilege, table, grantee) ;

return

end REVOKE

Prof. Ehud GudesSecurity Ch 6


Protection in system r cont

Protection in System R, cont.

Suppose that at time t=35, B issues the command REVOKE ALL RIGHTS ON EMPLOYEE FROM X. Clearly the (X, EMPLOYEE, B) tuple must be deleted from SYSAUTH. In order to determine which of X’s grants of EMPLOYEE must be revoked, we form a list of X’s remaining incoming grants:

As well as a list of X’s grants to others:

The grant of the DELETE privilege by X to Y at time t=25 must be revoked because his earliest remaining DELETE privilege was received at time t=30. But X’s grants of READ and INSERT are allowed to remain because they are still “supported” by incoming grants which occurred earlier in time.

Prof. Ehud GudesSecurity Ch 6


Grant revoke on views

GRANT/REVOKE on Views

  • If the creator of a view loses the SELECT privilege on an underlying table, the view is dropped!

  • If the creator of a view loses a privilege held with the grant option on an underlying table, (s)he loses the privilege on the view as well; so do users who were granted that privilege on the view!

Prof. Ehud GudesSecurity Ch 6


Revoking access on views system r

Revoking Access on Views- System R

  • REVOKE :procedure(grantee, table, grantor) ;deletethe (grantee, table, grantor) tuple in SYSAUTH;for eachu such that (u, table, grantee) is in SYSAUTH doREVOKE (u, table, grantee) ;for eachview such that (table, view, grantee) is in SYSUSAGE do DROP (view) ;return ;endREVOKE ;

  • DROP :procedure(view) ;deletethe view definition from the system ;for eachu1 and u2 such that (u1, view, u2) is in SYSAUTH doREVOKE(u1, view, u2) ;for eachv and u such that (view, v, u) is in SYSUSAGE do DROP (v) ;return ;endDROP ;

Prof. Ehud GudesSecurity Ch 6


Revoke in sql

REVOKE in SQL

  • RESTRICT – accept only if there are no privileges resulted SOLELY from the revoked command, otherwise reject

  • CASCADE – remove privileges recursively as in System R, but do not consider time!, that is, if a privilege was granted to B by A, and A’s rights were revoked, but LATER A was given these rights independently, then don’t revoke B’s rights - this is equivalent to saying that there is a path from the “System” node

Prof. Ehud GudesSecurity Ch 6


Protection in sql

Protection in SQL

GRANT SELECT ON Sailors TO Art WITH GRANT OPTION(executed by Joe)

GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION(executed by Art)

GRANT SELECT ON Sailors TO Art WITH GRANT OPTION(executed by Bob)

GRANT SELECT ON Sailors TO Cal WITH GRANT OPTION(executed by Joe)

GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION(executed by Cal)

REVOKE SELECT ON Sailors FROM Art CASCADE(executed by Joe)

Prof. Ehud GudesSecurity Ch 6


Protection in sql cont

Protection in SQL, cont.

System

(System, Joe, Select on Sailors, Yes)

Joe

Art

Cal

Bob

Prof. Ehud GudesSecurity Ch 6


Protection in sql cont1

Protection in SQL, cont.

What happens if Joe revokes access from Cal

Prof. Ehud GudesSecurity Ch 6


The privilege dependency graph1

The privilege dependency graph

B

4

2

D

E

G

8

5

A

3

7

6

F

C

Prof. Ehud GudesSecurity Ch 6


Sql revoke with restrict option

SQL - REVOKE with RESTRICT option

B

4

2

D

E

G

8

5

A

3

7

F

C

Revoke of a4 with RESTRICT option will fail! If we have added a6,

then it would not have failed!

Prof. Ehud GudesSecurity Ch 6


What may be useful and is not supported in sql92

What may be useful and is not supported in SQL92?

B

4

2

D

E

G

8

5

A

3

7

6

F

C

  • Negative authorizations

  • Non cascading revoke

B

5

2

E

G

8

A

D

3

7

6

F

C

Prof. Ehud GudesSecurity Ch 6


Protection in sql cont2

Protection in SQL, cont.

Why needs SELECT right with Integrity constraints?

May infer values from non permitted table!

CREATE TABLE Sneaky (maxsalary INTEGER,

CHECK ( maxsalary >=

( SELECT MAX (S.salary )

FROM EmployeesS )))

Prof. Ehud GudesSecurity Ch 6


Protection in sql cont3

Protection in SQL, cont.

Why SELECT right is not sufficient?

Since owner rights may be restricted (cannot delete rows)!

Needs explicit REFERENCE right

CREATE TABLE Assignments (project CHAR (10) NOTNULL,

SSN INTEGER,

day DATE,

PRIMARY KEY (SSN, day),

FOREIGN KEY (SSN) REFERENCES Employees

ON DELETE NO ACTION

Prof. Ehud GudesSecurity Ch 6


Differences between system r and sql 92

Differences Between System R and SQL-92

  • New privileges – REFERENCE, USAGE

  • CASCADE or RESTRICT on Revoke

  • CASCADE different then System R – not time-stamp based

  • Precise definitions for Rights on viewsE.g. the impact of adding/removing a right to/from the base tables

  • Authorization-Ids (for programs), Groups and Roles

Prof. Ehud GudesSecurity Ch 6


Role based access control

Role-Based Access Control

  • role-based access control work well for DBMS

    • eases admin burden, improves security

  • categories of database users:

    • application owner

    • end user

    • administrator

  • DB RBAC must manage roles and their users

    • cf. RBAC on Microsoft’s SQL Server

  • Table 5.2 in [SB] lists a set of administrative roles for MS-SQL server


Role based authorization

Role-Based Authorization

  • In SQL-92, privileges are actually assigned to authorization ids, which can denote a single user or a group of users.

  • In SQL:1999 (and in many current systems), privileges are assigned to roles.

    • Roles can then be granted to users and to other roles.

    • Reflects how real organizations work.

    • Illustrates how standards often catch up with “de facto” standards embodied in popular systems.

Prof. Ehud GudesSecurity Ch 6


Roles and permissions

Roles and Permissions

Medical_Staff:collectively, responsible for all aspects of direct patient care.

Nurse:Direct involvement with patient care on a daily basis.

Physician:Handle the medical needs (diagnosis, treatment, etc.) for patients.

Pharmacists:Control the supply and distribution of all drugs throughout the hospital.

Technician:Provide a variety of medical testing support for Patients.

Therapist:Evaluate patients and develop treatment plans for therapy.

Staff_RN:Administer direct care to patients and implement the physician treatment plan.

Prof. Ehud GudesSecurity Ch 6


Roles and permissions cont

Roles and Permissions, cont.

Discharge_Plug:Link between patients and outside agencies for care after discharge.

Education:Educate both the nursing staff and patients regarding new treatment and self care.

Manager:Responsible for the day-to-day operation of a nursing unit

Director:(For Physician or Pharmacist) Responsible for the day-to-day operation of their respective department/medical service.

Private:the physician within his/her office/private–practice setting.

Attending:A physician that hes privileges to admit and treat patients at a hospital.

Prof. Ehud GudesSecurity Ch 6


The user role definition hierarchy

Users

Medical Staff

Support Staff

Other

Nurse

Physician

Pharmacist

Technician

Therapist

Support

Patient Spouce

Prepare room

Volunteer

Security

The User-Role Definition Hierarchy

User Types, User Classes and Selected User Roles

Prof. Ehud GudesSecurity Ch 6


Role based models

Role-Based Models

  • RBAC0 – Users, Roles, Permissions, Sessions

  • RBAC1 – RBAC0 + Role-hierarchies

  • RBAC2 – RBAC0 + Constraints

  • RBAC3 – RBAC0 + Role-hierarchies + Constraints

Prof. Ehud GudesSecurity Ch 6


Rbac0

RBAC0

  • המודל הבסיסי עליו מתבססים שאר המודלים.

Prof. Ehud GudesSecurity Ch 6


Rbac1

RBAC1

  • היררכיית Role-ים.

Prof. Ehud GudesSecurity Ch 6


Rbac11

RBAC1

  • היררכיה של Role-ים:

    • קשר אב ובן.

    • הרשאות אפקטיביות וישירות.

Prof. Ehud GudesSecurity Ch 6


Rbac12

RBAC1

  • הגבלת ירושה.

Prof. Ehud GudesSecurity Ch 6


Rbac2

RBAC2

  • מודל האילוצים

    • Role-ים מנוגדים.

Prof. Ehud GudesSecurity Ch 6


Rbac3

RBAC3

  • המודל המשולב:

    • אילוצים והיררכיית Roles.

Prof. Ehud GudesSecurity Ch 6


Constraints in rbac separation of duties

Constraints in RBAC – Separation of duties

  • Conflicts between Permissions – conflicting permissions cannot be in the same Role or in two roles with a common ancestor

  • Conflicts between Roles – the same user cannot be in two conflicting roles

  • Conflicting users

  • Static constraints – max. number of roles per user, permissions per role, etc

  • Dynamic constraints – session dependent

Prof. Ehud GudesSecurity Ch 6


Roles in sql99

Roles in SQL99

  • New in SQL99; benefits:Simplifies definition of complex sets of privileges

  • Roles are createdCREATE ROLE AuditorCREATE ROLE AuditorGeneral

  • Roles may be assigned to users & rolesGRANT Auditor TO AuditorGeneralWITH ADMIN OPTIONGRANTED BY CURRENT ROLEGRANT Auditor TO Smith

  • Controllable whether to grant as user or role

Prof. Ehud GudesSecurity Ch 6


Roles in sql99 cont

Roles in SQL99, cont.

  • Roles (like users) may own objects

  • As to users, privileges may be granted to roles Grant INSERT ON TABLE Budget TO AuditorThis privilege also among privileges of AuditorGeneral

  • A role R identifies a set of privileges:Those directly granted to RThose of the roles granted to R

Prof. Ehud GudesSecurity Ch 6


Roles in sql99 cont1

Roles in SQL99, cont.

  • At any time there is at least a valid current user or a valid current role.

  • Current user can be setSET SESSION AUTHORIZATION ‘JDOE’

  • Current role can be set or invalidatesSET ROLE Auditor

  • Operations (e.g. INSERT) determine the kind of required privilegesOften: union of user’s and role’s privileges

  • Session context maintains stack of user and role identifier pairsNew pair is pushed when externally invoked procedure is executedTemporarily makes client module identifier the current userEnables invoker’s rights in a limited fashion

Prof. Ehud GudesSecurity Ch 6


Advantages of rbac

Advantages of RBAC

  • Convenient representation and mapping of the organization structure

  • Convenient distribution of the administration of Roles

  • Easier definition and understanding of the security policy of the organization

  • Role-hierarchy and inheritance of permissions eases administrator job

  • Changes in user roles are simple and controllable

  • Sessions allow the same user different roles in different contexts

  • Grouping multiple users to roles allow easier control of their permissions

Prof. Ehud GudesSecurity Ch 6


Security in object oriented databases

Security in Object-Oriented Databases

  • Composition hierarchy [K]

  • Generalization hierarchy [K]

  • Explicit and implicit authorization [K]

  • Strong and weak authorization [K]

  • Inheritance – which authorizations are inherited? [G]

  • Algorithm for evaluation [G]

  • Negative authorization [G]

Prof. Ehud GudesSecurity Ch 6


Database granularity hierarchy

System [MCC]

database [Inventory]

database [CAD]

Database…

class [vehicle]

class [automobile]

class [4-wheel-vehicle]

class [Motor-Vehicle]

instance [1]

instance [2]

Instance…

instance [100]

Attribute-value [Weight]

Attribute-value…

Database Granularity Hierarchy

Database Granularity Hierarchy

Prof. Ehud GudesSecurity Ch 6


Class hierarchy

Vehicle

IS-A

IS-A

4-Wheel-Vehicle

Motor-Vehicle

IS-A

IS-A

Automobile

Class Hierarchy

Prof. Ehud GudesSecurity Ch 6


Implicit weak authorization with weak exceptions

Implicit Weak Authorization with Weak Exceptions

Prof. Ehud GudesSecurity Ch 6


Implicit weak authorization with strong exceptions

Implicit Weak Authorization with Strong Exceptions

Prof. Ehud Gudes Security Ch 7


Security in oo databases

Security in OO Databases

Prof. Ehud GudesSecurity Ch 6


Mandatory access control

Mandatory Access Control

  • Based on system-wide policies that cannot be changed by individual users.

    • Each DB object is assigned a security class.

    • Each subject(user or user program) is assigned a clearance for a security class.

    • Rules based on security classes and clearances govern who can read/write which objects.

  • Most commercial systems do not support mandatory access control. Versions of some DBMSs do support it; used for specialized (e.g., military) applications.

Prof. Ehud GudesSecurity Ch 6


Why mandatory control

Why Mandatory Control?

  • Discretionary control has some flaws, e.g., the Trojan horse problem:

    • Dick creates Horsie and gives INSERT privileges to Justin (who doesn’t know about this).

    • Dick modifes the code of an application program used by Justin to additionally write some secret data to table Horsie.

    • Now, Dick can see the secret info.

  • The modification of the code is beyond the DBMSs control, but it can try and prevent the use of the database as a channel for secret information.

Prof. Ehud GudesSecurity Ch 6


Bell lapadula model

Bell-LaPadula Model

  • Objects (e.g., tables, views, tuples)

  • Subjects (e.g., users, user programs)

  • Security classes:

    • Top secret (TS), secret (S), confidential (C), unclassified (U): TS > S> C > U

  • Each object and subject is assigned a class.

    • Subject S can read object O only if class(S) >= class(O) (Simple Security Property)

    • Subject S can write object O only if class(S) <= class(O) (*-Property6

Prof. Ehud Gudes Security Ch 7


Intuition

Intuition

  • Idea is to ensure that information can never flow from a higher to a lower security level.

  • E.g., If Dick has security class C, Justin has class S, and the secret table has class S:

    • Dick’s table, Horsie, has Dick’s clearance, C.

    • Justin’s application has his clearance, S.

    • So, the program cannot write into table Horsie.

  • The mandatory access control rules are applied in addition to any discretionary controls that are in effect.

Prof. Ehud GudesSecurity Ch 6


Multilevel relations

bid

bname

color

class

101

Salsa

Red

S

102

Pinto

Brown

C

Multilevel Relations

  • Users with S and TS clearance will see both rows; a user with C will only see the 2nd row; a user with U will see no rows.

  • If user with C tries to insert <101,Pasta,Blue,C>:

    • Allowing insertion violates key constraint

    • Disallowing insertion tells user that there is another object with key 101 that has a class > C!

    • Problem resolved by treating class field as part of key.

Prof. Ehud GudesSecurity Ch 6


Mls prototype systems

Sea View

(SRI International, Oracle, Gemsos)

MLS relational datamodel, supports polinstentiation on db-, relation- , tupel- and attribute levels.

LDV (LOCK Data Views)

(Honeywell SCTC, MITRE)

Extended relational data model, polyinstatiation on tuple level. Supports application dependent and non application dependent integrity rules.

MLS-Prototype systems

Prof. Ehud GudesSecurity Ch 6


Jajodia formal integrity rules

Jajodia - Formal Integrity Rules

  • A Database D;

  • A relation R within the database D;

  • The primary key for a tuple r within the relation R;

  • The attribute i, identifying the element ri within the tuple r.

    To get through to the element ri, the following must hold:

    lab(D)lab(R) lab(ri)

    Otherwise, you could be barred access to an element you are entitled to see. In convention, a user who has access to an element of r must have access to its primary key.Therefore: lab(rk) lab(ri)

Prof. Ehud GudesSecurity Ch 6


Jajodia formal integrity rules cont

Jajodia - Formal Integrity Rules, cont.

RuleMulti-level entity integrity: no component of a primary key of a base relation may be null. All components of a primary key of a base relation have the same access class. In a base relation, the access class of all other data values in a tuple dominates the access class of the primary key of that tuple.

Prof. Ehud GudesSecurity Ch 6


Formal integrity rules cont

Formal Integrity Rules, cont.

RuleMulti-level reference integrity: a tuple referenced by a foreign key has to exist. The access class of the foreign key dominates the access class of the corresponding primary key.

Rule The access class of a view dominates the access classes of all relations used in the definition of the view.

Rule The access class of a tuple dominates the access classes of all attributes in the tuple.

Prof. Ehud GudesSecurity Ch 6


Multi level relation

Multi-level Relation

Figure 15.1The Relation Bookings with Primary Key Flight

Prof. Ehud GudesSecurity Ch 6


Multi level relation view of c user

Multi-level Relation – view of C user

Figure 15.1The Relation Bookings with Primary Key Flight

Prof. Ehud GudesSecurity Ch 6


Multi level relation view of u user

Multi-level relation – view of U user

Figure 15.2List of Non-Confidential Data from fig. 15.1 Accessible to Unclassified User

Prof. Ehud GudesSecurity Ch 6


Polyinstatiation is necessary

Polyinstatiation is necessary

Prof. Ehud GudesSecurity Ch 6


Why poly instantiation

Why Poly-instantiation?

  • A low user tries to add information on flight CA909.

    • If refused – inference, if accepted - violation of primary key constraint

  • A low user tries to update information on flight GR555.

    • If refused – inference, if accepted - violation of functional dependency

  • A high user attempt to change destination of flight AX301

    • If refused – restricts access, if accepted - violation of functional dependency

  • Solution: accept and include tuple class as part of the primary key!.

Prof. Ehud GudesSecurity Ch 6


Polyinstatiated table after user u update

Polyinstatiated table after User U update

Figure 15.4Updated Version of Table Data Given in Figure 15.1.

Prof. Ehud GudesSecurity Ch 6


Polyinstatiation cont

Polyinstatiation Cont.

Figure 15.5Data Accessible to Confidential User

Prof. Ehud GudesSecurity Ch 6


Subsumption

Subsumption

If user view has a null in U attribute and User C update it with non-null value, the C tuple subsumes the U tuple and only one tuple is visible to C

Prof. Ehud GudesSecurity Ch 6


Polyinstantiation integrity

Polyinstantiation integrity

Polyinstantiation integrity If two tuples in a base relation have the same primary key and the respective entries for some attribute have the same access class, then also the data values for this attribute are the same. If two tuples of a base relation have the same primary key and if there are some attributes where the respective entries have different access classes, then the values for those attributes may differ and any combination of these values (and access classes) gives again a tuple in the relation.

Prof. Ehud GudesSecurity Ch 6


Polyinstantiation integrity1

Polyinstantiation Integrity

Assume query: Dest = NY and Seats = 11 by C user – need to add two rows over table from p. 105

Prof. Ehud GudesSecurity Ch 6


Implementation options

Implementation Options

1.Rely only on ML-OS There is a separate single-level DBMS process running at each access class. Multi-level relations are stored as a collection of single-level operating systems.

 The DBMS has to use a partial ordering of access classes supported by the operating system.

2. DBMS is a Trusted Subject

 Proceed with the update and polyinstantiate the data, or

 Deny the update and record this event in an audit log.

Prof. Ehud GudesSecurity Ch 6


Mls concurrecy control

MLS Concurrecy Control

Assume user S locked a record for write and now user U tries to read it. The existence of this lock is already secret information!

If lock is at level S, user U (or U scheduler) should be unaware of it and will try to read the record.

If lock is at level U then user S violated BLP!

SOLUTION? Read papers!

Prof. Ehud GudesSecurity Ch 6


Inference

Inference


Inference example

Inference Example


Statistical db security

Statistical DB Security

  • Statistical DB: Contains information about individuals, but allows only aggregate queries (e.g., average age, rather than Joe’s age).

  • New problem: It may be possible to infersome secret information!

    • E.g., If I know Joe is the oldest sailor, I can ask “How many sailors are older than X?” for different values of X until I get the answer 1; this allows me to infer Joe’s age.

  • Idea: Insist that each query must involve at least N rows, for some N. Will this work? (No!)

Prof. Ehud GudesSecurity Ch 6


Why minimum n is not enough

Why Minimum N is Not Enough

  • By asking “How many sailors older than X?” until the system rejects the query, can identify a set of N sailors, including Joe, that are older than X; let X=55 at this point.

  • Next, ask “What is the sum of ages of sailors older than X?” Let result be S1.

  • Next, ask “What is sum of ages of sailors other than Joe who are older than X, plus my age?” Let result be S2.

  • S1-S2 is Joe’s age!

Prof. Ehud GudesSecurity Ch 6


Types of sensitive data

Types of Sensitive Data

  • Exact data – e.g. salary of John Doe

  • Bounds

  • Negative results – e.g. 0 is not the total number of felonies

  • Existence – e.g. of AIDS virus

  • Probable values

Prof. Ehud GudesSecurity Ch 6


Example database

Example Database

Prof. Ehud GudesSecurity Ch 6


Stat db security size restriction

Stat. DB security – size restriction

הגנה אפשרית מפני שתי שאילתות אלו היא הגבלה כי גודל התשובה לכל שאילתא יהיה גדול מ-1!

אולם אז ניתן לשאול את השאילתות הבאות:

Q1=COUNT(LOC=‘SF’LOC=‘SF’)

Q2=COUNT(LOC  =‘SF’SEX=‘F’)

התשובה לשאילתא הראשונה תהיה 7 – מס' הרשומות בטבלה.

התשובה לשאילתא השנייה תהיה 6 וההפרש ביניהן אחד Q1-Q2=1

מכאן ניתן לבצע את שתי השאילתות הבאות:

Q3=SUM(LOC=‘SF’LOC=‘SF’;SAL)

Q4=SUM(LOC=‘SF’SEX=‘F’;SAL)

והמשכורת הרצויה היא כמובן Q4-Q3!


Stat db security size restriction1

Stat. DB security – size restriction

בטבלה 7.5 רואים טבלה ובה מידע על עובדים ומשכורותיהם (שים לב ששדה השם אינו מופיע בבסיס הנתונים). אחד הדברים שאיננו מעונינים לחשוף הוא משכורתו של עובד מסוים. אולם קל מאוד כן לחשוף זאת ע"י שאילתא חוקית לחלוטין (ע"י תהליך הסקה). שאילתא חוקית היא מהצורה:

function (Predicate; Attribute)

כאשר function היא פונקציה כמו COUNT או SUM, ו-Attribute הוא השדה שעליו פועלת הפונקציה.

לדוגמא, נניח שאנו יודעים כי הפרט שאנו מעונינים בו Smith גרה בעיר SF (סן פרנסיסקו)והיא אשה.

אזי ביצוע השאילתות הבאות

COUNT (LOC=‘SF’ . SEX=‘F’)

וכן SUM (LOC=‘SF’ . SEX=‘F’;SAL)

ייתן את משכורתה של Smith!

Prof. Ehud GudesSecurity Ch 6


The individual tracker

The individual Tracker

Assume C characterize the individual uniquely – then Q(C) or Q(C*S) is unanswerable, S is the searched field.

Assume C=AB where both Q(A) and Q(AB^) are answerable.

ThenQ(C)=Q(A)-Q(AB^) or

Q(C*S)=Q(A*S)-Q(AB^*S)

Prof. Ehud GudesSecurity Ch 6


The individual tracker example

The Individual Tracker - Example

C=F*CS*Prof, A=F, B=CS*Prof

Prof. Ehud GudesSecurity Ch 6


The general tracker

The General Tracker

Assume threshold is K.

Find T such that

2k <= count(T) <= n-2k

Then,k <= count(T+C) <= n-k and

k <= count(T^+C) <= n-k

So, both are answerable!

And Q(C) = Q(T+C) + Q(T^+C) –n !

Prof. Ehud GudesSecurity Ch 6


Some defenses

Some Defenses

  • Replacing precise values with range values

  • Use samples from the original database

  • Perturb data randomly

  • Perturb results randomly

  • Partition the database

  • Audit trail and query analysis

Prof. Ehud GudesSecurity Ch 6


Protecting against inference

Protecting Against Inference


Other query restrictions

Other Query Restrictions

  • query set overlap control

    • limit overlap between new & previous queries

    • has problems and overheads

  • partitioning

    • cluster records into exclusive groups

    • only allow queries on entire groups

  • query denial and information leakage

    • denials can leak information

    • to counter must track queries from user


Perturbation

Perturbation

  • add noise to statistics generated from data

    • will result in differences in statistics

  • data perturbation techniques

    • data swapping

    • generate statistics from probability distribution

  • output perturbation techniques

    • random-sample query

    • statistic adjustment

  • must minimize loss of accuracy in results


The inference problem cont

The Inference Problem, cont.

To show that a refusal to answer may reveal a secret, we now consider a system that only refuses if the answer would reveal a secret. Suppose the following integrity constraints apply to a database containing the fact that Mediocrates is an Athenian:

Every man is an Athenian, a Boeotian, a Corinthian, or a Dorian;

All Athenians and Corinthians are peaceable;

All Boeotians and Dorians are violent;

Prof. Ehud GudesSecurity Ch 6


The inference problem cont1

The Inference Problem, cont.

Mediocrates does not wish it to be known that he is peaceable. Rhinologus, a public nuisance, tries to find out about Mediocrates:

Rhinologus:Is Mediocrates an Athenian?

System:I will not tell you.

Rhinologus:Is he a Boeotian?

System:No.

Rhinologus:Is he a Corinthian?

System:No.

Rhinologus:Is he a Dorian, then?

System:I will not tell you.

Prof. Ehud GudesSecurity Ch 6


The inference problem answering queries without revealing secrets

The Inference Problem – Answering Queries Without Revealing Secrets

“is Mediocrates an Athenian?”

yes

no

mum

k

“is Mediocrates a Boeotian?”

yes

mum

no

k

“is Mediocrates a Corinthian?”

mum

yes

no

k

“is Mediocrates a Dorian?”

mum

no

yes

k

k

K  ‘M is peaceable’

Prof. Ehud GudesSecurity Ch 6


Database encryption

Database Encryption

Prof. Ehud GudesSecurity Ch 6


Dbms data the attackers

DBMS Data - The Attackers

  • Intruder

    • Tries to attack the confidentiality or integrity of the database.

    • Tries to take over the identity of a regular user (or DBA).

    • Most attacks are targeted at web applications.

  • The database service provider \ Administrator

    • Tries to obtain information beyond his own access rights.

    • Tries to change the content of the database for his own benefit (e.g., change account balance).

    • Most attacks are targeted at outsourced databases.

  • The data owner \ Client

    • Has sufficient privileges to tamper with the accessright definition – possibly unlimited privileges.


Database encryption1

Database Encryption

  • databases typical a valuable info resource

    • protected by multiple layers of security: firewalls, authentication, O/S access control systems, DB access control systems, and database encryption

  • can encrypt

    • entire database - very inflexible and inefficient

    • individual fields - simple but inflexible

    • records (rows) or columns (attributes) - best

      • also need attribute indexes to help data retrieval

  • varying trade-offs


Database encryption2

Database Encryption


Database encryption at the table level

Database Encryption at the Table Level

#$*#@#$%^

#$@!@#$%

%$@!@#&$

&^*%$%^&$

  • Encrypting the entire table after every change.

  • Substitution and statistical attacks on the table values are eliminated.

  • Weakness: In order to execute a query the whole table needs to be decrypted.

Encrypted

Database

DBMS

Decryption

Client

Secured

Communication

Encryption

Intruder

Insider

Admin

Prof. Ehud GudesSecurity Ch 6


Database encryption at the cell level

Database Encryption at the Cell Level

  • Encrypting each cell separately.

  • Only the data of interest needs to bedecrypted while performing a query.

  • Weakness: Substitution and statistical attacks are possible.

16

%$

Encrypted

Database

DBMS

Decryption

Client

Secured

Communication

Encryption

Intruder

Insider

Admin

Prof. Ehud GudesSecurity Ch 6


Database encryption at the row level

Database Encryption at the Row Level

  • The database encryption scheme described by Davida(81) is based on the Chinese-Reminder theorem.

  • Each row is encrypted using different sub-keys for different cells.

  • Enables: encryption at the level of rows and decryption at the level of cells.

  • Weakness: Requires re-encrypting the entire row when a cell value is modified.

  • Weakness: The basic element in the database is a row and not a cell, thus the structure of the database needs to be changed.

Prof. Ehud GudesSecurity Ch 6


Spde a new database encryption scheme

SPDE - A New Database Encryption Scheme

  • Each cell is encrypted with its uniquecell coordinates.

  • Only the data of interest needs to bedecrypted while performing a query.

  • Substitution and statistical attacksare eliminated.

16

&$

Encrypted

Database

DBMS

Decryption

Client

Secured

Communication

Encryption

Intruder

Insider

Admin

Prof. Ehud GudesSecurity Ch 6


Cell coordinates characteristics

Cell Coordinates Characteristics

  • Stable cell coordinates:

    • Insert, update and delete operations do not change the coordinates of existing cells.

    • For example, this is the case in the Oracle database.

  • If a database reorganization process changes cell coordinates, all affected cells are to be re-encrypted with their new coordinates.

Prof. Ehud Gudes Security Ch 7


Indexing encrypted data

IndexingEncrypted Data

Prof. Ehud Gudes Security Ch 7


Building an index on the encrypted data

Building an Index on the Encrypted Data

Decrypted Table

Encrypted Table

Index

  • Indexing the encrypted data using a hash index.

  • Equality queries are possible.

  • Weakness: Statistical attacks are possible by usingthe index.

  • Weakness: No range queries.

Prof. Ehud GudesSecurity Ch 6


Encrypting each index value separately

Encrypting Each Index Value Separately

Decrypted Table

Encrypted Table

Index

&$

@#

Maximum

&$

  • Building the index on the plaintext values and encrypting each index value separately.

  • Range queries are possible.

  • Weakness: Statistical attacks are possible by using the index.

  • Weakness: The order of the ciphertext values is exposed.

Prof. Ehud GudesSecurity Ch 6


Encrypting each index node separately

Encrypting Each Index Node Separately

Decrypted Table

  • The indexing scheme provided by Damiani(03) suggests encrypting each node of the B+-Tree as a whole.

  • References between the B+-Tree nodes are encrypted together with the index values.

  • The index does not reveal the statistics or order of the database values

  • Weakness: The index structure is concealed.

Index

@#

Encrypted Table

*!

&$

Prof. Ehud GudesSecurity Ch 6


A new database indexing scheme

A New Database Indexing Scheme

Decrypted Table

  • Each index value is the result of encrypting a plaintext value in the database with its row-id.

  • This ensures that the index does not reveal the statistics or order of the database values.

  • The new database indexing scheme preserves the index structure.

Index

Encrypted Table

@#

*!

&$

Prof. Ehud GudesSecurity Ch 6


Spde conclusions

SPDE - Conclusions

  • A new structure preserving scheme for database encryption has been presented.

  • In the new scheme, patterns matching and substitution attacks cannot succeed, thus guaranteeing information confidentiality and data integrity.

  • The new schemes do not impose any changes on the database structure.

    • A DBA is able to manage the encrypted database as any other non-encrypted database.

    • Implementing the new scheme in existing applications does not entail modifying the queries.

Prof. Ehud GudesSecurity Ch 6


Sql injection traditional db

SQL InjectionTraditional DB

  • Two-tier architecture

  • Users are fixed and known

  • Users’ number is limited

  • All transactions are passed via the same connection and users connect directly to the DB


Traditional db access control

Traditional DB Access Control

  • DB can identify and authenticate its users

  • DB can authorize users by traditional user/role-based access control

  • It is quite easy to follow up single user transactions to seek signs of intrusion

  • Views can be used to determine for a user the only part of the database that interests her


Web databases

Web Databases

Three-tier architecture

Users are casualand unknown

Users’ number is not limited

Users do not connect directly to the DB


Connection pooling

Connection Pooling

  • Different web users can run their SQL statements on the same DB connection and one user can run her SQL statements on different connections

    • This technique contributes to application efficiency since the time to open and close the connection is saved per each request

    • It has serious implications on the database’s access control mechanism


Web db access control

Web DB Access Control

  • Web DB does not identify the real application user who accesses it

  • The only user accessing the database is the user of the web application server – most often with full access privileges (administrator or “super-user”)

  • No user-based access control can be applied

  • Only limited RBAC can be applied

  • The principle of minimal privilege is violated

  • No more fine-grained access control to the web DB exists


Web db is vulnerable

Web DB is vulnerable

  • There is no way to limit the web user privileges at the database level of the web databases.

  • There are still many secured web applications, but their security can be achieved only by application means (writing lines of code that implement security policies) and not by database access control means (GRANT/REVOKE and VIEWS)

  • This situation is very problematic:

    • Implementing access control by writing code is a time consuming task

    • Enterprise may have one DB but many applications, then access control is distributed and in many cases not consistent

    • Programmers must be security specialists

  • As a result: "70% of websites are at immediate risk of being hacked!" (http://www.acunetix.com)


Attack example sql injection

Attack example: SQL Injection

  • In many Web application the SQL sentence is structured as a string and the user’s parameter is concatenated to this string:

strSQL= "SELECT Salary FROM Salary_Table WHERE Employee_No = 123

AND Salary_Date = '" + dateParam + "'"

  • But the intruder can type `01.2007' or '1' = '1` and retrieve the entire table:

SELECT Salary FROM Salary_Table

WHERE Employee_No = 123

AND Salary_Date = '01/2007' OR '1' = '1'


Sql injection

SQL Injection

  • SQL uses single and double quotes to switch between data and code.

  • Semi-colons separate SQL statements

  • Example query:

    • "UPDATE users SET prefcolor='red' WHERE uid='joe';"

  • This command could be sent from a web front-end to a database engine.

  • The database engine then interprets the command


Dynamic sql generation

Dynamic SQL Generation

  • Web applications dynamically generate the necessary database commands by manipulating strings

  • Example query generation:

    • $q = "UPDATE users SET prefcolor='$INPUT[color]' WHERE uid='$auth_user'";

  • Where the value of "$INPUT[color]" would be originating from the client web browser, through the web server.

  • And where the value for "$auth_user" would have been stored on the server and verified through some authentication scheme


Client web browser

Client Web Browser

  • Forms in client browsers return values to the web server through either the POST or GET methods

    • "GET" results in a url with a "?" before the values of the form variables are specified:

      • http://www.example.com/script?color=red

      • The value of "$INPUT[color]" is set to "red" in the script

  • "GET" urls are convenient to hack, but there isn't any significant difference in the security of either "GET" or "POST" methods because the data comes from the client web browser regardless and is under the control of the remote attacker


The sql table

The SQL Table

  • Tables are used to store information in fields (columns) in relation to a key (e.g., "uid")

  • What other fields could be of interest?

  • CREATE TABLE users ( prefcolor varchar(20), uid VARCHAR(20) NOT NULL, privilege ENUM('normal', 'administrator'), PRIMARY KEY (uid));


A malicious sql query

A Malicious SQL Query

  • What if we could make the web server generate a query like:

    • "UPDATE users SET prefcolor='red', privilege='administrator' WHERE uid='joe';"

  • Can we engineer the value of "color" given to the web server so it generates this query?

    • Note how code and data are mixed in the same channel

      • Better database interfaces provide separate channels


Malicious http request

Malicious HTTP Request

  • http://www.example.com/script?color=red',privilege='administrator

  • The "color" input is then substituted to generate SQL:

    • $q = "UPDATE users SET prefcolor='$INPUT[color]' WHERE uid='$auth_user'";

  • It gives the query we wanted!


Results

Results

  • Joe now has administrator privileges.


Adding another sql query

Adding Another SQL Query

  • Let's say Joe wants to run a completely different query:

    • "DELETE * FROM users"

      • This will delete all entries in the table!

  • How can the value of "color" be engineered?


Malicious http request1

Malicious HTTP Request

  • http://www.example.com/script?color=red'%3Bdelete+from+users%3B

    • %3B is the url encoding for ";"

  • What happens when the "color" input is used to generate SQL?

    • $q = "UPDATE users SET prefcolor='$INPUT[color]' WHERE uid='$auth_user'";


Result

Result

  • UPDATE users

  • SET prefcolor='red';

  • delete from users;

  • WHERE uid='$auth_user'";

  • The last line generates an error, but it's already too late; all entries have been deleted.

  • The middle query could have been anything


Chapter 6 database security

FAQs

  • Couldn't the database have a separate account for "Joe" with only the privileges he needs (e.g., no delete privilege)?

    • In theory yes, but in practice the management of such accounts and privileges, and connecting to the database with the correct IDs, adds significant complexity

      • Most often a database account is created for the entire web application, with appropriate limitations (e.g., without privileges to create and drop tables)

      • A good compromise is to create database accounts for each class of user or class of operation, so:

        • if Joe is a regular user he wouldn't have delete privileges for the user table

        • Changing user preferences, as an operation type, doesn't require delete privileges


Chapter 6 database security

FAQs

  • Doesn't SSL protect against this sort of attack?

    • No

  • But what if you authenticate users with a username/password over SSL? Then, if the user does SQL injection, the server admins will know who perpetrated the crime, right?

    • Not necessarily; only if you have sufficient auditlogging.


What to do

What to do?

  • Careful writing of Web applications including strong input validation

  • More reliance on Database security including the use of Views and Roles

  • Our method of Prevention and Detection using Parameterized Views


Summary

Summary

  • Three main security objectives: secrecy, integrity, availability.

  • DB admin is responsible for overall security.

    • Designs security policy, maintains an audit trail, or history of users’ accesses to DB.

  • Two main approaches to DBMS security: discretionary and mandatory access control.

    • Discretionary control based on notion of privileges.

    • Mandatory control based on notion of security classes.

  • Statistical DBs try to protect individual data by supporting only aggregate queries, but often, individual information can be inferred.

Prof. Ehud GudesSecurity Ch 6


  • Login