CST221: Database Systems

1. CST221: Database Systems Dr. Zhen Jiang Computer Science Department West Chester University West Chester, PA 19383

2. Outline • Overview • Non-relational DB system • NonSQL DB system • Injection • Inference • Role access control (UML) • Perturbation • Design • Models • Encryption

3. Database System Overview Database data Query request DBMS

4. Integration • Administration • Security & encryption • Privacy & inference • Transaction & injection • Sketching & hashing

5. Application Programming Interface (API) integration

6. 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

7. 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

8. 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)

9. Query request Ok? API DBMS

10. 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

11. You need a tool for the trace of transactions

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

13. 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

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

15. 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:

16. 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.

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

18. 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!

19. Existing solutions • Limit access • Role access control • Too many restriction could seriously hinder the functionality

23. 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

24. 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.

25. 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)

26. 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.

27. Models • Database role based • Application role based • Application function based • Application role and function based • Application table based

28. 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

30. 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

31. 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/

32. 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)

34. 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

35. 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

37. Model Based on Application Roles and Functions • Combination of models • Application authenticates users • Application is divided into functions • Highly flexible model

39. 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

40. 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

42. 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

43. 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