440 likes | 454 Views
This summary provides an overview of database techniques, including non-relational DB systems, injection vulnerabilities, inference risks, and role-based access control. It covers topics such as data querying, administration, security, encryption, privacy, and transaction injection. The summary also discusses the integration of traditional databases and non-relational databases, as well as the challenges and solutions related to authorization, perturbation, and preservation in database systems.
E N D
CSC321/545: Summary of Database Techniques 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
Database System Overview Database data Query request DBMS
Integration • Administration • Security & encryption • Privacy & inference • Transaction & injection • Sketching & hashing
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 Ok? API DBMS
Injection • Direct DB injection • http://www.youtube.com/watch?v=v6bphRHH4sM • Indirect DB injection • http://www.irongeek.com/i.php?page=videos/webgoat-sql-injection
interrupt each transaction as you debug and trace the record of each transaction
Internet is not safe https://www.youtube.com/watch?v=SJgYdTckMBY • Even worse ( 2 min)https://www.youtube.com/watch?v=hcSss9BHPFo • Information Assurance (in product lifecycle) • Definition (3 min) https://www.youtube.com/watch?v=Wde-_38_xNc • Our solution, e.g., (5 min)https://www.youtube.com/watch?v=97Tnjr72IoQ
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! • https://www.youtube.com/watch?v=3QnD2c4Xovk • 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 caurses 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/csc545_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.
Models • 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