Cst221 database systems
1 / 43

CST221: Database Systems - PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

CST221: Database Systems. Dr. Zhen Jiang Computer Science Department West Chester University West Chester, PA 19383. Outline. Overview Non-relational DB system NonSQL DB system Injection Inference Role access control (UML) Perturbation Design Models Encryption.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Download Presentation

CST221: Database Systems

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

CST221: Database Systems

Dr. Zhen Jiang

Computer Science Department

West Chester University

West Chester, PA 19383


  • Overview

    • Non-relational DB system

    • NonSQL DB system

  • Injection

  • Inference

    • Role access control (UML)

    • Perturbation

  • Design

    • Models

    • Encryption

Database System Overview



Query request


  • Integration

  • Administration

  • Security & encryption

  • Privacy & inference

  • Transaction & injection

  • Sketching & hashing

Application Programming Interface (API) integration

Traditional Database

  • The relation of key vs. non-key

  • The relation between key and foreign key

    • Intra-table relation

    • Inter-table relation

  • E-R diagram

    • http://www.cs.wcupa.edu/~zjiang/ER.pdf

    • Any regularity?

      • Arbitrary & Abrupt

    • Ambiguity

      • Sample of such ambiguity in normalization process caused by the lack of background

Non-Relational Database

  • Data does not relate in the true sense

    • e.g., Mongo, which handles document stores or other content and/or metadata stores

NonSQL Database

  • A more clear structure

    • e.g., Kobo, Playtika (mobile service)

    • Distributed database system

      • No need and not possible for a “join” operator

    • Fast third-party data aggregation

    • Fast caching for application objects

    • Globally distributed data repository

    • E-commerce and internet burstness

    • Game (data intensive applications)

    • Ad targeting (social networks)

Query request





  • Direct DB injection

    • http://www.youtube.com/watch?v=v6bphRHH4sM

  • Indirect DB injection

    • http://www.irongeek.com/i.php?page=videos/webgoat-sql-injection

You need a tool for the trace of transactions

interrupt each transaction as you debug and trace the record of each transaction

  • Authorization

    • Restrict access to data and restrict the actions that people may take (when they access data).

  • Encryption

    • Scramble data so that the data cannot be read.

  • Authentication

    • Password check

    • Key protection, not to protect everything!

  • Role based access control

Inference (aggregation)

  • Basically, inference occurs when users are able to piece together (aggregate) information to determine a fact that should be protected.

  • Role cheating

  • General Jones (who has a top security clearance) requests information and would see all three.

  • Civilian Smith (who has no security clearance) requests the data and would see the following data:

  • When Smith sees that nothing is scheduled for hold B on flight 1254, he might attempt to insert the record, and his insertion will fail due to the unique constraint on cargo space availability.

  • He has all the data he needs to infer that there is a secret shipment on flight.

  • He could then cross-reference the flight information table to find out the source and destination of the secret shipment and various other information.

  • Poly-instantiation: allows different records (hold B) to exist in the same table.

  • Overbooking!

  • Other causes such as:

    • Count of highly preferred customers

    • Average salary

  • Problem is difficult

    • Information?

      • Content: what is critical?

    • Path?

      • Hold A-C, Hold B? Total space? Probing!

  • Existing solutions

    • Limit access

      • Role access control

      • Too many restriction could seriously hinder the functionality

  • Perturbation

    • Alter the data so that individual details are accurate but overall generalization are inaccurate.

    • Include dummy data in the results returned by the query unauthorized.

    • Protect sensitive data, but also achieve preservation of the properties of the dataset.

      • Sketching with a probability of p.

      • With probability p to use the original data

      • With probability (1-p) to use a replacement

  • Preservation

    • Given each query f in the original table T with n rows, build a re-constructible query f’ in the revised table T’ (with n rows), so that the result difference can be controlled in a limited range with a probability of p.

    • In other words, the expected number of rows that get perturbation is n(1-p). For a domain ∆C, n(1-p)k rows will be expected to lie within the available value range (k ∆C), k[1, 0].

      • Among total nr rows observed from T’ in the value range (k ∆C), subtracting the n(1-p)k rows, we have the estimation for the number of unperturbed rows. Scaled up by 1/p, we get the total number of original rows (n0), as only a p fraction of rows were retained.

Security and Privacy

  • f’ = n0/n

  • [n-n0, n0]A = [n-nr, nr]

  • a=Pr(row T) vs. b=Pr(row in perturbed table T’)

  • Privacy breach, security threshold 

     > a / b

    b  b’ (sketch does not help to distinguish the cases)

  • Server Storage (with a) vs. Client retaining (with b)

OO Design for DB Systems

  • Injection, inference

    • RBAC (role based access control)

    • Use case

      • http://www.cs.wcupa.edu/~zjiang/intro_uc.ppt

    • Class design is needed for better maintaining the data ownership

      • http://www.cs.wcupa.edu/~zjiang/DB_OO_design.htm

  • Non-relational DB

    • Activity pattern – prediction of future relation, e.g., credit card security

  • NonSQL DB

    • Relations in structure for the use.


  • Database role based

  • Application role based

  • Application function based

  • Application role and function based

  • Application table based

Model Based on Database Roles

  • Application authenticates application users: maintain all users in a table

  • Each user is assigned a role; roles have privileges assigned to them

  • A proxy user is needed to activate assigned roles; all roles are assigned to the proxy user

  • Model and privileges are database dependent

  • Implementation in SQL Server:

    • Use application roles:

      • Special roles you that are activated at the time of authorization

      • Require a password and cannot contain members

    • Connect a user to the application role: overrules user’s privileges

  • Implementation in SQL Server (continued):

    • Connect to database as the proxy user

    • Validate the user name and password

    • Retrieve the application role name

    • Activate the application role

  • Great article on app roles:

    • SQL Server Security: Pros and Cons of Application Roles By Brian Kelley

    • http://www.sqlservercentral.com/articles/Security/sqlserversecurityprosandconsofapplicationroles/1116/

Model Based on Application Roles

  • Application roles are mapped to real business roles

  • Application authenticates users

  • Each user is assigned to an application role; application roles are provided with application privileges (read and write)

  • Implementation in SQL Server

    • Create a database user

    • Connect the application to the database using this user

    • Create stored procedures to perform all database operations

Model Based on Application Functions

  • Application authenticates users

  • Application is divided into functions

  • Considerations:

    • Isolates application security from database

    • Passwords must be securely encrypted

    • Must use a real database user

    • Granular privileges require more effort during implementation

Model Based on Application Roles and Functions

  • Combination of models

  • Application authenticates users

  • Application is divided into functions

  • Highly flexible model

Model Based on Application Tables

  • Depends on the application to authenticate users

  • Application provides privileges to the user based on tables; not on a role or a function

  • User is assigned access privilege to each table owned by the application owner

  • Privileges:

    • 0 -no access

    • 1 –read only

    • 2 – read and add

    • 3 –read, add, and modify

    • 4 – read, add, modify, and delete

    • 5 – read, add, modify, delete, and admin

  • Implementation in SQL Server:

    • Grant authorization on application functions to the end user

    • Alter authorization table from the security model based on database roles; incorporate the table and access columns required to support model

Data Encryption

  • Passwords should be kept confidential and preferably encrypted

  • Passwords should be compared encrypted:

    • Never decrypt the data

    • Hash the passwords and compare the hashes

  • Login