cst221 database systems
Skip this Video
Download Presentation
CST221: Database Systems

Loading in 2 Seconds...

play fullscreen
1 / 43

CST221: Database Systems - PowerPoint PPT Presentation

  • Uploaded on

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

PowerPoint Slideshow about ' CST221: Database Systems ' - christen-alvarado

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

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
Database System Overview



Query request


  • Administration
  • Security & encryption
  • Privacy & inference
  • Transaction & injection
  • Sketching & hashing
traditional database
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
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
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
    • 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
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
    • 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
    • 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
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
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
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
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
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
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
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
    • 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
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