1Lecture 13:SecurityWednesday, October 26, 20062Midterm !Friday, 10:30-11:20, in class.• Problem 1: SQL• Problem 2: E/R diagrams• Problem 3: Conceptual design, BCNFOpen book examOpen book exam3OutlineSQL Security – 8.7Two famous attacksTwo new trends4Discretionary Access Control in SQLGRANT privileges ON objectTO users[WITH GRANT OPTIONS]GRANT privileges ON objectTO users[WITH GRANT OPTIONS]privileges = SELECT | INSERT(column-name) |UPDATE(column-name) |DELETE |REFERENCES(column-name)object = table | attribute5ExamplesGRANT INSERT, DELETE ON Customers TO Yuppy WITH GRANT OPTIONSGRANT INSERT, DELETE ON Customers TO Yuppy WITH GRANT OPTIONSQueries allowed to Yuppy:Queries denied to Yuppy:INSERT INTO Customers(cid, name, address)VALUES(32940, ‘Joe Blow’, ‘Seattle’)DELETE CustomersWHERE LastPurchaseDate < 1995INSERT INTO Customers(cid, name, address)VALUES(32940, ‘Joe Blow’, ‘Seattle’)DELETE CustomersWHERE LastPurchaseDate < 1995SELECT Customer.addressFROM CustomerWHERE name = ‘Joe Blow’SELECT Customer.addressFROM CustomerWHERE name = ‘Joe Blow’6ExamplesGRANT SELECT ON Customers TO MichaelGRANT SELECT ON Customers TO MichaelNow Michael can SELECT, but not INSERT or DELETE7ExamplesGRANT SELECT ON Customers TO Michael WITH GRANT OPTIONSGRANT SELECT ON Customers TO Michael WITH GRANT OPTIONSMichael can say this:GRANT SELECT ON Customers TO YuppiNow Yuppi can SELECT on Customers8ExamplesGRANT UPDATE (price) ON Product TO LeahGRANT UPDATE (price) ON Product TO LeahLeah can update, but only Product.price, but not Product.name9ExamplesGRANT REFERENCES (cid) ON Customer TO BillGRANT REFERENCES (cid) ON Customer TO BillCustomer(cid, name, address, balance)Orders(oid, cid, amount) cid= foreign keyCustomer(cid, name, address, balance)Orders(oid, cid, amount) cid= foreign keyNow Bill can INSERT tuples into OrdersBill has INSERT/UPDATE rights to Orders.BUT HE CAN’T INSERT ! (why ?)10Views and SecurityCREATE VIEW PublicCustomersSELECT Name, AddressFROM CustomersGRANT SELECT ON PublicCustomers TO FredCREATE VIEW PublicCustomersSELECT Name, AddressFROM CustomersGRANT SELECT ON PublicCustomers TO FredDavid says-520PortlandAnn333.25SeattleJoan-240SeattleSue450.99HustonMaryBalanceAddressNameDavid ownsCustomers:Fred is notallowed tosee this11Views and Security-520PortlandAnn333.25SeattleJoan-240SeattleSue450.99HustonMaryBalanceAddressNameCREATE VIEW BadCreditCustomersSELECT *FROM CustomersWHERE Balance < 0GRANT SELECT ON BadCreditCustomers TO JohnCREATE VIEW BadCreditCustomersSELECT *FROM CustomersWHERE Balance < 0GRANT SELECT ON BadCreditCustomers TO JohnDavid saysDavid ownsCustomers:John isallowed tosee only <0balances12Views and Security• Each customer should see only her/his recordCREATE VIEW CustomerMarySELECT * FROM CustomersWHERE name = ‘Mary’GRANT SELECTON CustomerMary TO MaryCREATE VIEW CustomerMarySELECT * FROM CustomersWHERE name = ‘Mary’GRANT SELECTON CustomerMary TO MaryDoesn’t scale.Need row-level access control !-520PortlandAnn333.25SeattleJoan-240SeattleSue450.99HustonMaryBalanceAddressNameDavid saysCREATE VIEW CustomerSueSELECT * FROM CustomersWHERE name = ‘Sue’GRANT SELECTON CustomerSue TO SueCREATE VIEW CustomerSueSELECT * FROM CustomersWHERE name = ‘Sue’GRANT SELECTON CustomerSue TO Sue. . .13RevocationREVOKE [GRANT OPTION FOR] privilegesON object FROM users { RESTRICT | CASCADE }REVOKE [GRANT OPTION FOR] privilegesON object FROM users { RESTRICT | CASCADE }Administrator says:REVOKE SELECT ON Customers FROM David CASCADEREVOKE SELECT ON Customers FROM David CASCADEJohn loses SELECT privileges on BadCreditCustomers14RevocationJoe: GRANT [….] TO Art …Art: GRANT [….] TO Bob …Bob: GRANT [….] TO Art …Joe: GRANT [….] TO Cal …Cal: GRANT [….] TO Bob …Joe: REVOKE [….] FROM Art CASCADEJoe: GRANT [….] TO Art …Art: GRANT [….] TO Bob …Bob: GRANT [….] TO Art …Joe: GRANT [….] TO Cal …Cal: GRANT [….] TO Bob …Joe: REVOKE [….] FROM Art CASCADESame privilege,same object,GRANT OPTIONWhat happens ??15RevocationAdminJoe ArtCal Bob012345RevokeAccording to SQL everyone keeps the privilege16Summary of SQL SecurityLimitations:• No row level access control• Table creator owns the data: that’s unfair !… or spectacular failure:• Only 30% assign privileges to users/roles– And then to protect entire tables, not columnsAccess control = great success story of the DB community...17Summary (cont)• Most policies in middleware: slow, error prone:– SAP has 10**4 tables– GTE over 10**5 attributes– A brokerage house has 80,000 applications– A US government entity thinks that it has 350K• Today the database is not at the center of the policy administration universe[Rosenthal&Winslett’2004]18Two Famous Attacks• SQL injection• Sweeney’s example19Search claims by: SQL InjectionYour health insurance company lets you see the claims online:Now search through the claims :Dr. Lee First login:User:Password: fred ********SELECT…FROM…WHERE doctor=‘Dr. Lee’ and patientID=‘fred’SELECT…FROM…WHERE doctor=‘Dr. Lee’ and patientID=‘fred’[Chris Anley, Advanced SQL Injection In SQL]20SQL InjectionNow try this:Search claims by: Dr. Lee’ OR patientID = ‘suciu’; --Better:Search claims by: Dr. Lee’ OR 1 = 1; --…..WHERE doctor=‘Dr. Lee’ OR patientID=‘suciu’; --’ and patientID=‘fred’…..WHERE doctor=‘Dr. Lee’ OR patientID=‘suciu’; --’ and patientID=‘fred’21SQL InjectionWhen you’re done, do this:Search claims by: Dr. Lee’; DROP TABLE Patients; --22SQL Injection• The DBMS works perfectly. So why is SQL injection possible so often ?• Quick answer:– Poor programming: use stored procedures !• Deeper answer:– Move policy implementation from apps to DB23Latanya Sweeney’s Finding• In Massachusetts, the Group Insurance Commission (GIC) is responsible for purchasing health insurance for state employees• GIC has to publish the data:GIC(zip, dob, sex, diagnosis, procedure, ...)GIC(zip, dob, sex, diagnosis, procedure, ...)24Latanya Sweeney’s Finding• Sweeney paid $20 and bought the voter registration list for Cambridge Massachusetts:GIC(zip, dob, sex, diagnosis, procedure, ...)VOTER(name, party, ..., zip, dob, sex)GIC(zip, dob, sex, diagnosis, procedure, ...)VOTER(name, party, ..., zip, dob, sex)25Latanya Sweeney’s Finding•
View Full Document
Unlocking...