1 / 150

Chapter 6 - Database Security

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.

dominique-derick
Télécharger la présentation

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 6 - Database Security Stallings Chp. 5 Prof. Ehud Gudes Security Ch6

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

  3. A Typical Architecture of a DBMS Prof. Ehud GudesSecurity Ch 6

  4. A typical DBMS Architecture

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

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

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

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

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

  10. The Access Matrix Model Compatibility Lists Access Lists Prof. Ehud GudesSecurity Ch 6

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

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

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

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

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

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

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

  18. Protection in Ingres, cont. • (Rule 1) range of E is employee permit E to Jones for retrieve (E.name, E.mgr) where E.dept = ‘D1’ • (Rule 2) range of E is employee permit E to Jones for retrieve (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

  19. Protection in Ingres, cont. • Query1 range of E is employee retrieve (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’) • Query2 range of E is employee retrieve (E.name) • Both Rules1,2 and 3 match (see Hovereth) Prof. Ehud GudesSecurity Ch 6

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

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

  22. Example Database (2) Project Prof. Ehud GudesSecurity Ch 6

  23. Horizontal / Vertical View AS query CREATE VIEW view-name (-- column_name --) , earning_little emp

  24. Mixed View (1) Prof. Ehud GudesSecurity Ch 6

  25. Mixed View (2) Prof. Ehud GudesSecurity Ch 6

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

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

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

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

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

  31. REVOKE Statement Prof. Ehud GudesSecurity Ch 6

  32. Access Privileges in different DBMSs Prof. Ehud GudesSecurity Ch 6

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

  34. Revoke options • Reject (SQL) • Non-recursive revocation (Fernandez) • Time based recursive revocation (System R) • System based recursive revocation (SQL) Prof. Ehud GudesSecurity Ch 6

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

  36. Protection in System R בדוגמה זו רואים כי X קיבל זכויות משני גורמים מ-B ומ-C ולכן שלילת הזכויות מ-B אינה גורמת בהכרח שלילת הזכויות מ-X. אם לדוגמה נבצע את הפקודה A:REVOKE READ ON EMP FROM B אזי במקרה של קיום שלילה רקורסיבית (SYSTEM R(נקבל את הגרף באיור -א' ובמקרה של אי קיום שלילה רקורסיבית (לפי FERNANDEZ ) נקבל את הגרף באיור - ב'.

  37. The privilege dependency graph B 4 2 D E G 8 5 A 3 7 6 F C Prof. Ehud GudesSecurity Ch 6

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

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

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

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

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

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

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

  45. Protection in SQL, cont. System (System, Joe, Select on Sailors, Yes) Joe Art Cal Bob Prof. Ehud GudesSecurity Ch 6

  46. Protection in SQL, cont. What happens if Joe revokes access from Cal Prof. Ehud GudesSecurity Ch 6

  47. The privilege dependency graph B 4 2 D E G 8 5 A 3 7 6 F C Prof. Ehud GudesSecurity Ch 6

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

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

  50. 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 Employees S ))) Prof. Ehud GudesSecurity Ch 6

More Related