5/9/10&1&Security&and&Authoriza6on&CISC437/637,&Lecture&#19&Ben&CartereAe&1&Copyright&©&Ben&CartereAe&Why&Security?&• Three&objec6ves:&– Secrecy&–&some&informa6on&should&only&be&seen&by&par6cular&users&– Integrity&–&some&informa6on&should&only&be&modified&by&par6cular&users&– Availability&–&authorized&users&should&not&be&denied&access&• A&security2policy&is&used&to&achieve&these&• A&security2mechanism&enforces&a&policy&22&Copyright&©&Ben&CartereAe&5/9/10&2&Outline&• Three&aspects&of&security:&– Access&control&and&security&in&the&DBMS&– Secure&access&by&applica6ons&(par6cularly&over&the&internet)&– Protec6on&from&inferences&Copyright&©&Ben&CartereAe& 3&Discre6onary&Access&Control&• Users&can&be&given&privileges&to&access&objects&at&the&discre6on&of&some&authority&• The&creator&of&a&table&or&view&automa6cally&has&all&privileges&(read,&write)&on&it&– That&user&or&administra6ve&users&can&grant&privileges&to&addi6onal&users&Copyright&©&Ben&CartereAe& 4&5/9/10&3&Discre6onary&Access&Control&in&SQL&GRANT&[privileges]&ON&[object]&TO&[users]&[WITH&GRANT&OPTION]&• Available&privileges&on&tables&are:&– SELECT(column)&–read&access&on&specified&columns,&or&all&columns&if&none&given&– INSERT(column)&–&insert&rows&with&noncnull&values&in&specified&columns,&or&all&columns&if&none&given&– UPDATE(column)&–&similar&to&INSERT&– DELETE&–&delete&rows&from&the&table&– REFERENCES(column)&–&define&foreign&keys&in&other&tables&that&refer&to&specific&columns&• WITH&GRANT&OPTION&gives&users&the&power&to&pass&the&granted&privileges&to&other&users&• CREATE,&ALTER,&DROP&can&be&granted&on&databases&Copyright&©&Ben&CartereAe& 5&GRANT/REVOKE&and&Views&• A&user&must&have&SELECT&privileges&on&all&underlying&tables&in&order&to&create&a&view&– Loss&of&SELECT&on&any&table&results&in&view&being&dropped&from&database&• If&a&user&gains&privileges&on&all&underlying&tables,&the&user&gains&the&same&privileges&on&the&view&• Views&can&be&used&to&give&necessary&informa6on&to&users&while&hiding&details&Copyright&©&Ben&CartereAe& 6&5/9/10&4&Applica6on&Security&• Users&of&database&applica6ons&may&not&be&known&by&database&and&therefore&have&no&privileges&within&database&– Generally&resolvable&with&password&authen6ca6on&• Harder&when&access&is&allowed&via&remote&connec6on&– Trust:&&how&do&we&know&user&is&who&they&say&they&are?&&How&does&user&know&that&they’re&sending&data&to&who&we&say&we&are?&Copyright&©&Ben&CartereAe& 7&Encryp6on&• Encode&data&for&secure&transmission&and&storage&– Encrypt(data,&encryp6on&key)&&encrypted&data&– Decrypt(encrypted&data,&decryp6on&key)&&data&– Ideally,&encypted&data&is&not&understandable&without&decryp6on&key&• Publicckey&encryp6on:&&each&user&has&two&keys&– Public&encryp6on&key&known&to&all&– Decryp6on&key&known&only&to&user&Copyright&©&Ben&CartereAe& 8&5/9/10&5&RSA&PubliccKey&Encryp6on&• RSA&=&Rivest,&Shamir,&and&Adleman&&– Developed&in&1977&at&MIT&– Won&the&Turing&Award&in&2002&– Clifford&Cocks&of&GCHQ&discovered&it&in&1973!&&But&was&kept&classified&• Idea:&– Represent&the&data&as&an&integer&I&– Choose&a&large&integer&L&=&p*q&• p,&q&very&large&dis6nct&prime&numbers&– To&encrypt,&choose&a&number&1&<&e&<&L&that&is&rela6vely&prime&to&(pc1)*(qc1)&• Encrypted&data&S&=&Ie&mod&L&– Decryp6on&key&d&chosen&so&that&• d*e&=&1&mod&((pc1)*(qc1))&• Show&that&I&=&Sd&mod&L&Copyright&©&Ben&CartereAe& 9&Secure&Socket&Layers&• SSL&is&a&way&to&ensure&trust&over&remote&connec6ons&• Suppose&Amazon&has&a&public&key&and&private&key&• An&Amazon&customer&can&get&the&public&key&and&use&it&to&encrypt&credit&card&and&other&secure&informa6on&– Only&Amazon&will&be&able&to&decrypt&it&• How&does&customer&know&public&key&is&real?&– Another&layer&of&security:&&Verisign&holds&a&cer6ficate&for&Amazon&– Customer&checks&with&Verisign&before&sending&anything&to&Amazon&• Once&SSL&connec6on&established,&weaker&encryp6on&Copyright&©&Ben&CartereAe& 10&5/9/10&6&RowcLevel&Control&• Some&DBMSs&implement&the&ability&to&restrict&rows&to&certain&security&clearance&levels&– A&user&without&the&proper&clearance&should&not&know&those&rows&exist&• Problem:&&a&lowclevel&user&could&figure&out&that&a&row&exists&i f&an& INSERT&that&“looks&right”&fails&– Same&primary&key&value&already&used&by&row&with&higher&clearance&level&– Solu6on:&&essen6ally&include&security&level&as&part&of&primary&key&Copyright&©&Ben&CartereAe& 11&Inference&• Even&with&security,&a&clever&user&might&be&able&to&figure&out&things&they&shouldn’t&know&• Example:&– A&database&contains&6mecstamped,&anonymous&GPS&data&(la6tude,&longitude)&covering&two&weeks&– A&database&user&gets&that&informa6on&and&applies&algorithmic&methods&to&group&data&by&individual&– The&user&then&applies&some&simple&heuris6cs&to&iden6fy&the&loca6on&of&an&individual’s&home&Copyright&©&Ben&CartereAe& 12&5/9/10&7&Another&Example:&&Search&Query&Logs&• Example&queries:&– Nov.&15:&&driving&direc6ons&to&nist&– Nov.&16:&&rental&car&newark&de&– Nov.&17:&&geor ge&watson&arts&and&sciences&– Nov.&22:&&repeatable&evalua6on&jensen&• If&you&know&that&the&same&person&put&in&all&four&of&these&queries,&it&wouldn’t&be&too&hard&to&figure&out&who&that&person&is&Copyright&©&Ben&CartereAe& 13&Sta6s6cal&Databases&• A&sta7s7cal2database&contains&specific&informa6on&but&allows&only&sta6s6cal&queries&– Average&GPA,&maximum&age,&…&– Total&number&of&GPS&coord in ates,&average&distance&between&points,&…&– Total&number&of&queries&submiAed,&number&of&results&clicked,&…&• Inference&can&s6ll&be&possible,&though&– Using&highly&selec6ve&clauses,&for&instance&– Even&restric6ng&queries&to&those&that&have&at&least&N&rows&doesn’t&solve&the&probl em&completely&Copyright&©&Ben&CartereAe& 14&5/9/10&8&Search&Engine&Query&Logs&• Google’s&database&sto res&IP&address,&cookie&ID,&username,&query&string,&6mestamp,&clicked&results&– Anyone&with&access&to&that&data&can&see&all&queries,&all&results&clicked&• Proposed&solu6ons&to&query&log&privacy/security:&– Hashing&query&string&to&an&“encrypted”&string&• But&queries&could&s6ll&be&inferred&using&other&data&–