1 / 82

Introduction to Database

Introduction to Database. CHAPTER 6 Integrity and Security. Domain Constraints Referential Integrity Assertions Triggers Security Authorization Authorization in SQL Encryption and Authentication. Contents. Chapter 1: Introduction PART 1 DATA MODELS

greta
Download Presentation

Introduction to Database

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. Introduction to Database CHAPTER 6 Integrity and Security • Domain Constraints • Referential Integrity • Assertions • Triggers • Security • Authorization • Authorization in SQL • Encryption and Authentication

  2. Contents Chapter 1: Introduction PART 1 DATA MODELS Chapter 2: Entity-Relationship Model Chapter 3: Relational Model PART 2 RELATIONAL DATABASES Chapter 4: SQL Chapter 5: Other Relational Languages Chapter 6: Integrity and Security Chapter 7: Relational Database Design PART 4 DATA STORAGE AND QUERYING Chapter 11: Storage and File Structure Chapter 12: Indexing and Hashing

  3. Integrity vs. Security • Integrity: • Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency. • They test values inserted in the database, and test queries to ensure that the comparisons make sense. • Costly to test • Security: • Protect database from unauthorized access and malicious destruction or alteration 惡意的

  4. 6.1 Domain Constraints • Domain constraints • E.g. create domain Dollars numeric(12,2) • will test values inserted in the database • Domains with same format may with different meanings • E.g. create domainDollarsnumeric(12, 2)create domainPoundsnumeric(12,2) • We cannot assign or compare a value of type Dollars to a value of type Pounds. create table account(account-number char(10),branch-name char(15),balance dollars, amount pounds primary key (account-number),

  5. Domain Constraints (cont.) • The check clause: in SQL-92 permits domains to be restricted: • Use check clause to ensure that an hourly-wage domain allows only values greater than a specified value. create domain hourly-wage numeric(5,2) constraintvalue-test check(value > = 4.00) • that ensures that the hourly-wage is greater than 4.00 • The clause constraintvalue-test is optional; • Complex Conditions: in domain check • createdomainAccountTypechar(10)constraintaccount-type-testcheck (valuein (‘Checking’, ‘Saving’)) • createdomainBranchNamechar(15)constraintbranch-name-testcheck (branch-namein (selectbranch-namefrombranch))

  6. 3. account 1. branch 6.2 Referential Integrity create table account(account-number char(10),branch-name char(15),balance integer, primary key (account-number), foreign key (branch-name)references branch)

  7. 6.2.4 Referential Integrity in SQL • Foreign keys can be specified as part of the SQL create table statement: • By using the foreign key clause • By default, a foreign key references the primary key attributes of the referenced table create table account(account-number char(10),branch-name char(15),balance integer, primary key (account-number), foreign key (branch-name)references branch)

  8. Example: Banking Database • Banking Database: consists 6 relations: • branch (branch-name, branch-city, assets) • customer (customer-name, customer-street, customer-only) • account (account-number, branch-name, balance) • loan (loan-number, branch-name, amount) • depositor (customer-name, account-number) • borrower (customer-name, loan-number)

  9. 2. customer 1. branch 3. account Example: Banking Database (cont.)

  10. 5. loan 4. depositor 6. borrower Example: Banking Database (cont.)

  11. 2. customer 1. branch Referential Integrity in SQL: Example create table customer(customer-name char(20),customer-street char(30),customer-city char(30),primary key (customer-name)) create table branch(branch-name char(15),branch-city char(30),assets integer,primary key (branch-name))

  12. 3. account 4. depositor Referential Integrity in SQL: Example (cont.) create table account(account-number char(10),branch-name char(15),balance integer, primary key (account-number), foreign key (branch-name) references branch) create table depositor(customer-name char(20),account-number char(10), primary key (customer-name, account-number), foreign key(account-number) references account, foreign key (customer-name) references customer) references

  13. GQBE: Query Example • Example query: Find the customer-name, account-number and balance for all accounts at the Perryridge branch

  14. 1. branch Referential Integrity: Cascade • Due to the on delete cascade clauses, • if a delete of a tuple in branch • Eg. Brighton Broklyn 7100000 • the delete “cascades” to the account relation, A-201, A-217 • Cascading updates are similar. 3. account create tableaccount (account-number char(10),branch-name char(15),balance integer, primary key (account-number), foreign key (branch-name)references branch) on delete cascade on update cascade ) references See p.230

  15. 1. branch Referential Integrity: Set Null • Due to the on delete set null clauses, • if a delete of a tuple in branch • Eg. Brighton Broklyn 7100000 • the delete “set null” to the account relation, A-201, A-217 3. account create tableaccount (account-number char(10),branch-name char(15),balance integer, primary key (account-number), foreign key (branch-name)references branch) on delete set null ) null null references

  16. 主張, 宣告 6.3 Assertions/Integrity Rules • An assertionis a predicate expressing a condition that we wish the database always to satisfy. • An assertion in SQL takes the form createassertion <assertion-name> check <predicate> • E.g. create assertion balance-constraint check account.balance >= 1000 • When an assertion is made, the system tests it for validity, and tests it again on every update that may violate the assertion • The testing may introduce a significant amount of overhead; • Hence, assertions should be used with great care. • The rule of an assertion is stored in “dictionary”(system catalog)

  17. Assertion: Example 1 • The sum of all loan amounts for each branch must be less than the sum of all account balances at the branch. create assertion sum-constraint check (not exists (select * from branchwhere (select sum(amount) from loanwhere loan.branch-name = branch.branch-name) >= (select sum(amount) from accountwhere loan.branch-name = branch.branch-name)))

  18. Assertion: Example 2 • Every loan has at least one borrower who maintains an account with a minimum balance or $1000.00 create assertionbalance-constraint check (not exists ( select * from loanwhere not exists ( select * from borrower, depositor, accountwhere loan.loan-number = borrower.loan-numberand borrower.customer-name = depositor.customer-nameand depositor.account-number = account.account-numberand account.balance >= 1000)))

  19. 6.4 Triggers 啟動 • A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. • To design a trigger mechanism, we must: • Specify the conditions under which the trigger is to be executed (when). • Specify the actions to be taken when the trigger executes. • Triggers introduced to SQL standard in SQL:1999, but supported even earlier using non-standard syntax by most databases. create triggersetnull-trigger before update on r referencing new row as nrow for each row when nrow.phone-number = ‘‘ set nrow.phone-number = null

  20. 5. loan 3. account 6.4.1 Need for Trigger • Suppose that instead of allowing negative account balances, the bank deals with overdrafts by • setting the account balance to zero • creating a loan in the amount of the overdraft • giving this loan a loan number identical to the account number of the overdrawn account • The condition for executing the trigger is an update to the account relation that results in a negative balance value.

  21. 6. borrower 6.4.2 Trigger in SQL create triggeroverdraft-trigger after update on account referencing new row as nrow for each rowwhen nrow.balance < 0begin atomic insert into borrower (select customer-name, account-number from depositor where nrow.account-number = depositor.account-number); insert into loan values (n.row.account-number, nrow.branch-name, – nrow.balance); update account set balance = 0 where account.account-number = nrow.account-numberend

  22. Triggering event can be insert, delete or update Triggers on update can be restricted to specific attributes e.g. balance E.g. create triggeroverdraft-trigger after update ofbalance on account Values of attributes before and after an update can be referenced referencing old row as: for deletes and updates referencing new row as: for inserts and updates Triggers can be activated before an event E.g. Convert blanks to null. create triggersetnull-trigger before update onr referencing new row asnrow for each rowwhennrow.phone-number = ‘‘//blankset nrow.phone-number = null Triggering Events and Actions

  23. Introduction to Database CHAPTER 6 Integrity and Security • Domain Constraints • Referential Integrity • Assertions • Triggers • Security • Authorization • Authorization in SQL • Encryption and Authentication

  24. Integrity: review ?? Security Protection from malicious attempts to steal or modify data. Unauthorized access Absolute protection of the database is not possible But, the cost to the perpetrator can be made high enough to deter most Authorization 6.5 Security and Authorization 授權, 認可

  25. Forms of Malicious Access: Unauthorized reading of data Unauthorized modification of data Unauthorized destruction of data Security Levels: to protect the database Database system level Authorization mechanisms to allow specific users access only to required data We concentrate on authorization in the rest of this chapter Operating system level Operating system super-users can do anything they want to the database! Good operating system level security is required. 6.5.1 Security Violations 違例 蓄意的;惡意的

  26. Security Levels(cont.) • Network level: • must use encryption to prevent • Physical level • Physical access to computers allows destruction of data by intruders; traditional lock-and-key security is needed • Computers must also be protected from floods, fire, etc. • More in Chapter 17 (Recovery) • Human level • Users must be screened to ensure that an authorized users do not give access to intruders • Users should be trained on password selection and secrecy 加密 闖入者 保密能力

  27. Forms of authorization on parts of the database: Read authorization Insert authorization Update authorization Delete authorization 6.5.2 Authorization • Forms of authorization to modify the database schema: • Index authorization - allows creation and deletion of indices. • Resources authorization - allows creation of new relations. • Alteration authorization - allows addition or deletion of attributes in a relation. • Drop authorization - allows deletion of relations.

  28. Users can be given authorization on views, without being given any authorization on the relations used in the view definition Ability of views to hide data serves both to simplify usage of the system and to enhance security by allowing users access only to data they need for their job A combination or relational-level security and view-level security can be used to limit a user’s access to precisely the data that user needs. SQL View V1 View V2 Base Table B4 Base Table B3 Base Table B2 Base Table B1 Data Set D4 Data Set D3 Data Set D2 Data Set D1 6.5.3 Authorization and Views

  29. Suppose a bank clerk needs to know the names of the customers of each branch, but is not authorized to see specific loan information. Approach: Deny direct access to the loan relation, but grant access to the view cust-loan, which consists only of the names of customers and the branches The cust-loan view is defined in SQL as follows: View: Example create view cust-loan as select branchname, customer-name from borrower, loan where borrower.loan-number = loan.loan-number • The clerk is authorized to see the result of the query: select *from cust-loan

  30. Authorization Graph: The passage of authorization from one user to another may be represented by graph. The nodes of this graph are the users. The root of the graph is the database administrator. Consider graph for update authorization Revoke DBA revoke from U1, then … U1 U4 DBA U2 U5 U3 Fig. 6.6 Authorization-Grant Graph 6.5.4 Granting of Privileges

  31. Granting of Privileges (cont.) 打破 Fig. 6.7 Attempt to Defeat Authorization Revocation Fig. 6.8 Authorization Graph

  32. U1 U4 DBA U2 U5 U3 6.5.5 Notion of Roles • Role: Roles permit common privileges for a class of users can be specified just once by creating a corresponding “role” • Privileges can be granted to or revoked from roles, just like user • Roles can be assigned to users, and even to other roles • Example: Bank Database • Roles: teller, branch-manager, auditor, system administrator

  33. 6.5.6 Audit Trails 足跡 • Audit Trail: An audit trail is a log of all changes (inserts/deletes/updates) to the database along with information such as which user performed the change, and when the change was performed. • Used to track erroneous (mistake) or fraudulent updates. • Can be implemented using triggers, but many database systems provide direct support. 詐騙 Dishonest trick

  34. 6.6 Authorization in SQL • Privileges in SQL • Roles • Grant • with grant option • Revoke

  35. The grant statement is used to confer authorization grant <privilege list> on <relation name or view name> to <user list> <user list> is: a user-id public, which allows all valid users the privilege granted A role (more on this later) Example: grant users U1, U2, and U3 select authorization on the branch relation: grant selectonbranch toU1, U2, U3 6.6.1 Privileges in SQL

  36. Privileges can be granted to or revoked from roles, just like user Roles can be assigned to users, and even to other roles SQL:1999 supports roles create role tellercreate rolemanager grant select onbranch to tellergrant update (balance) onaccount totellergrant all privileges onaccounttomanagergranttellertomanagergrant teller to alice, bobgrantmanagerto avi 6.6.2 Roles

  37. with grant option: allows a user who is granted a privilege to pass the privilege on to other users. Example: grant select on branch to U1with grant option gives U1 the select privileges on branch and allows U1 to grant this privilege to others U1 U4 DBA U2 U5 U3 6.6.3 Privilege to Grant Privileges • Revoke revoke select onbranchfromU1, U2, U3cascade default revoke select on branch from U1, U2, U3restrict

  38. Data may beencrypted when database authorization provisions do not offer sufficient protection. Properties of good encryption technique: Relatively simple for authorized users to encrypt and decrypt data. Encryption scheme depends not on the secrecy of the algorithm but on the secrecy of the encryption key. Extremely difficult for an intruder to determine the encryption key. 確定 證明 6.7 Encryption and Authentication

  39. Data Encryption Standard (DES) substitutes characters and rearranges their order on the basis of an encryption key which is provided to authorized users via a secure mechanism. E.g. I LOVE YOU K NQXG AQW Scheme is no more secure than the key transmission mechanism since the key has to be shared. Advanced Encryption Standard (AES) is a new standard replacing DES, and is based on the Rijndael algorithm, but is also dependent on shared secret keys 6.7.1 Encryption Techniques + 2

  40. 最早有關密碼的書 (1920) (補) Source: http://www.nsa.gov/museum/

  41. World War II (補) Source: http://www.nsa.gov/museum/ Enigma (德國) Big machine (美國)

  42. Three-Rotor Machine (補) Source: http://www.nsa.gov/museum/

  43. 新時代 (補) Source: http://www.nsa.gov/museum/ Supercomputer: Cray-XMP 特殊IC

  44. 蠻兵 蠻兵 天水縣 蠻兵 蠻兵 士兵 龍鳳城四郎 諸葛四郎大鬥雙假面 (補) 四郎 資料來源: 葉宏甲編著 “諸葛四郎全集: 大鬥雙假面,” 故鄉出版社, 1992.

  45. Public Encryption: RSA 公開金鑰 (補) • e.g. • Painttext P = 13; public-key: e=11, r=15 • Ciphertext C = modulo r • = modulo 15 • = 1792160394037 modulo 15 • = 7 • Decryption P = modulo r • = modulo15 • = 343 modulo 15 • = 13 甲: e, r 乙: e, r, d C = mod r P = mod r

  46. Public Encryption: RSA 公開金鑰(cont.) (補) • The scheme of : [Rivest78] 1. Choose, randomly, two distinct large primes p and q, and compute the product • r = p * q e.g. p=3, q=5, r=15 • 2. Choose, randomly, a large integer e, such that • gcd (e, (p-1)*(q-1) ) = 1 • Note: any prime number greater than both p and q will do. • (p-1)*(q-1)= 2*4 = 8, e = 11 • 3. Take the decryption key, d, corresponding to e to be the unique "multiplicative inverse" of e, modulo (p-1)*(q-1); • i.e. d*e = 1, modulo(p-1)*(q- 1) • Note: The algorithm for computing d is straight forward. • d*11=1, mod 8, ==> d=3

  47. Public Encryption: RSA 公開金鑰(cont.) (補) • Exercise: Suppose we have r = 2773, e = 17, try to find d = ? Answer: 1. 50*50 = 2500 ~ 2773 2. 47*53 2773 3. 47*59 = 2773 so p = 47, q = 59 4. (p-1)(q-1) = 2668 5. d*17 = 1, modulo (p-1)*(q-1) => d*17 = 1, modulo 2668 d = 1: 17 + 2667 2668 x d = 2: 4 + 2667 2668 x d = 157: (157*17) / (2668) = 2 r: 50 digits => 4 hrs 75 digits => 100 days 100 digits => 74 years 500 digits => 4*10^25 years . . .

  48. Public-key Encryption: is based on each user having two keys: public key– publicly published key used to encrypt data, but cannot be used to decrypt data private key -- key known only to individual user, and used to decrypt data. Keys need not be transmitted to the site doing encryption. Encryption scheme is such that it is impossible or extremely hard to decrypt data given only the public key. The RSA public-key encryption scheme is based on the hardness of factoring a very large number (100's of digits) into its prime components. Encryption Techniques: RSA

  49. 6.7.2 Authentication 確定 證明 • Password-based Authentication • is widely used by OS and database • Drawback: easy to “sniff” the password on a network • Challenge-response systems: • No passwords travel across the network • DB sends a (randomly generated) challenge string to user • User encrypts string and returns result. • DB verifies identity by decrypting result

More Related