Bài giảng Bảo mật Cơ sở dữ liệu - Chương 9: Introduction to SQL Encryption - Phần 1

Pag. 1 Phần I Database Security and Auditing2 ReView Pag. 3 • Encryption hierarchy is marked by three-level security. • These three levels provide different mechanisms for securing data across networks and local servers. • Different levels of hierarchies allow multiple instances of services (e.g., SQL Server Services) to run on one physical server. – Windows Level – Highest Level – Uses Windows DP API for encryption – SQL Server Level – Moderate Level – Uses Services Master Key for encryption – Database Level – Lower Level – Uses Database Master Key for encryption Introduction to SQL Encryption Pag. 4 Introduction to SQL Encryption There are two kinds of keys used in encryption: • Symmetric Key – In Symmetric cryptography system, the sender and the receiver of a message share a single, common key that is used to encrypt and decrypt the message. This is relatively easy to implement, and both the sender and the receiver can encrypt or decrypt the messages. • Asymmetric Key – Asymmetric cryptography, also known as Public-key cryptography, is a system in which the sender and the receiver of a message have a pair of cryptographic keys – a public key and a private key – to encrypt and decrypt the message. This is a relatively complex system where the sender can use his key to encrypt the message but he cannot decrypt it. The receiver, on the other hand, can use his key to decrypt the message but he cannot encrypt it. Pag. 5 Introduction to SQL Encryption Pag. 6 Introduction to SQL Encryption There are two different kinds of encryptions available in SQL Server: • Database Level – This level secures all the data in a database. However, every time data is written or read from database, the whole database needs to be decrypted. This is a very resource-intensive process and not a practical solution. • Column (or Row) Level – This level of encryption is the most preferred method. Here, only columns containing important data should be encrypted; this will result in lower CPU load compared with the whole database level encryption. If a column is used as a primary key or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to perform operations involving those columns. Pag. 7 Can we offer better performance? • We DO NOT fully trust the service provider with sensitive information – Encrypt client’s data and store at server – Client: • runs queries over encrypted remote data • verifies integrity/authenticity of results • Most of the processing work to be done by the server • Consider passive adversary – A malicious individual who has access to data but only tries to learn sensitive information about the data without actively modifying it or disrupting any kind of services Pag. 8 Service Provider Architecture Pag. 9 Query Processing 101 • At its core, query processing consists of: – Logical comparisons (> , =) – Pattern based queries (e.g., *Arnold*egger*) – Simple arithmetic (+, *, /, ^, log) • Higher level operators implemented using the above – Joins – Selections – Unions – Set difference – • To support any of the above over encrypted data,need to have mechanisms to support basic operations over encrypted data Pag. 10 Searching over Encrypted Data • Want to be able to perform operations over encrypted data (for efficiency) SELECT AVG(E.salary) FROM EMP WHERE age > 55 • Fundamental observations – Basic operations do not need to be fully implemented over encrypted data – To test (AGE > 55), it might suffice to devise a strategy that allows the test to succeed in most cases (might not work in all cases) – If test does not result in a clear positive or negative over encrypted representation, resolve later at client-side, after decryption. Pag. 11 Searching over Encrypted Data • Store an encrypted string – etuple – for each tuple in the original table – This is called “row level encryption” – Any kind of encryption technique (e.g., AES, DES) can be used • Create an index for each (or selected) attribute(s) in the original table Pag. 12 Building the Index • Partition function divides domain values into partitions (buckets) • Partition (R.A) = { [0,200], (200,400], (400,600], (600,800], (800,1000] } – partition function has impact on performance as well as privacy – very much domain/attribute dependent – equi-width vs. equi-depth partitioning • Identification function assigns a partition id to each partition of attribute Pag. 13 Building the Index • Mapping function maps a value v in the domain of attribute A to partition id Pag. 14 Storing Encrypted Data Pag. 15 Referring back to our example SELECT AVG(E.salary) FROM EMP WHERE age > 55 • Suppose the partitions on age are as follows: P1 - [20,30); P2 -[30,40); P3 - [40,50); P4 - [50,60); P5 - [60,100] • To test (AGE > 55), it suffices to retrieve all data that falls into partitions that contain at least one employee with age > 55 – P4 and P5 – These partitions (e g P4) may contain records with age <=55; they can examined at the client-side after records are decrypted. • Records belonging to partitions that contain only employees with age <= 55 (e.g., P1, P2 and P3) will not need to be returned Pag. 16 Mapping Conditions • Q: SELECT name, pname FROM employee, project WHERE employee.pin=project.pin AND salary>100k • Server stores attribute indices determined by mapping functions • Client stores metadata and uses it to translate the query Pag. 17 Mapping Conditions Pag. 18 Mapping Conditions Pag. 19 Mapping Conditions Pag. 20 Relational Operators over Encrypted Relations • Partition the computation of the operators across client and server • Compute (possibly) superset of answers at the server • Filter the answers at the client • Objective : minimize the work at the client and process the answers as soon as they arrive requiring minimal storage at the client • Operators: – Selection – Join – Grouping and Aggregation – Others: Sort, duplicate elimination, set difference, union, projection Pag. 21 Selection Operator Pag. 22 Selection Operator Pag. 23 Join Operator Pag. 24 Join Operator Pag. 25 Join Operator Pag. 26 Grouping & Aggregation Operator Pag. 27 Query Decomposition Pag. 28 Query Decomposition Pag. 29 Query Decomposition Pag. 30 Query Decomposition Pag. 31 Query Precision vs. Privacy Pag. 32 Fine Encryption Granularity Pag. 33 Can we do better with aggregation? Pag. 34 Aggregation over encrypted data Pag. 35 Aggregation over encrypted data Pag. 36 In relational DBMS Pag. 37 Complete example Pag. 38 Complete example Pag. 39 Complete example Pag. 40 Summary • Store encrypted data at server • Process as much at server as possible, and postprocess at client • Storage cost is higher (hash values can be as large as the original values) • Leak some information – number of distinct values, which records have the same values in certain attribute, which records are join-able, – violate access control • Effectiveness depends on the partitioning/index granularity Pag. 41 Example: Encryption Let’s go over a simple instance that demonstrates the encryption and the decryption process executed with Symmetric Key and Triple DES encryption algorithm. /*Create Database */ USE master GO CREATE DATABASE EncryptTest ON PRIMARY ( NAME = N'EncryptTest', FILENAME = N'C:\EncryptTest.mdf') LOG ON ( NAME = N'EncryptTest_log', FILENAME =N'C:\EncryptTest_log.ldf') GO /* Pag. 42 Example: Encryption First, let’s create a sample table and then populate it with sample data. We will now encrypt one of the two columns of the table. /* Create table and insert data in the t able */ USE EncryptTest GO CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50)) /* Pag. 43 Example: Encryption First, let’s create a sample table and then populate it with sample data. We will now encrypt one of the two columns of the table. /* Create table and insert data in the t able */ USE EncryptTest GO CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50)) /* Pag. 44 Example: Encryption INSERT INTO TestTable (FirstCol, SecondCol) SELECT 1,'First' UNION ALL SELECT 2,'Second' UNION ALL SELECT 3,'Third' UNION ALL SELECT 4,'Fourth' UNION ALL SELECT 5,'Fifth' GO /* Pag. 45 Example: Encryption /* Check the content of the TestTable */ USE EncryptTest GO SELECT * FROM TestTable GO /*/ Result of the SQL query Pag. 46 Example: Encryption Every database can have one master key. Database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys present in the database. It uses Triple DES algorithm together with user-provided password to encrypt the keys. /* Create Database Master Key */ USE EncryptTest GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLAuthority' /*/ Pag. 47 Example: Encryption Certificates are used to safeguard encryption keys, which are used to encrypt data in the database. SQL Server 2005 has the capability to generate self- signed X.509 certificates. /* Create Encryption Certificate */ USE EncryptTest GO CREATE CERTIFICATE EncryptTestCert WITH SUBJECT = 'SQLAuthority' GO ' /*/ Pag. 48 Example: Encryption The symmetric key can be encrypted by using various options such as certificate, password, symmetric key, and asymmetric key. A number of different algorithms can be employed for encrypting key. The supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256. /* Create Symmetric Key */ USE EncryptTest GO CREATE SYMMETRIC KEY TestTableKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE EncryptTestCert GO /*/ Pag. 49 Example: Encryption Now add a column of type varbinary to the original table, which will store the encrypted value for the SecondCol. /* Encrypt Data using Key and Certificate Add Columns which will hold the encrypted d ata in binary */ USE EncryptTest GO ALTER TABLE TestTable ADD EncryptSecondCol VARBINARY(256) GO /*/ Pag. 50 Example: Encryption Before the key is used, it needs to be decrypted using the same method that was used for encrypting it. /* Update binary column with encrypted data created by certificate and key */ USE EncryptTest GO OPEN SYMMETRIC KEY TestTableKey DECRY PTION BY CERTIFICATE EncryptTestCert UPDATE TestTable SET EncryptSecondCol =ENCRYPTBYKEY(KEY_ GUID('TestTableKey'),SecondCol) GO /*/ Pag. 51 Example: Encryption We can drop the original SecondCol column, which we have now encrypted in the EncryptSecondCol column. If you do not want to drop the column, you can keep it for future comparison of the data when we decrypt the column. /* DROP original column which was encrypted for protect the data */ USE EncryptTest GO ALTER TABLE TestTable DROP COLUMN SecondCol GO /*/ Pag. 52 Example: Encryption • We can run a SELECT query on our database and verify if our data in the table is well protected and hackers will not be able to make use of it even if they somehow manage to reach the data. /* Check the content of the TestTable */ USE EncryptTest GO SELECT * FROM TestTable GO /*/ Pag. 53 Example: Encryption • Authorized user can use the decryptbykey function to retrieve the original data from the encrypted column. If Symmetric key is not open for decryption, it has to be decrypted using the same certificate that was used to encrypt it. An important point to bear in mind here is that the original column and the decrypted column should have the same data types. If their data types differ, incorrect values could be reproduced. In our case, we have used a VARCHAR data type for SecondCol and EncryptSecondCol. /*/ Pag. 54 Example: Encryption * Decrypt the data of the SecondCol */ USE EncryptTest GO OPEN SYMMETRIC KEY TestTableKey DECRY PTION BY CERTIFICATE EncryptTestCert SELECT CONVERT(VARCHAR(50),DECRYPTBYK EY(EncryptSecondCol)) ASDecryptSecondCol FROM TestTable GO /*/ Pag. 55 Example: Encryption If you drop the database after the entire processing is complete, you do not have to worry about cleaning up the database. However, in real world on production servers, the database is not dropped. It is a good practice for developers to close the key after using it. If keys and certificates are used only once or their use is over, they can be dropped as well. Dropping a database will drop everything it contains – table, keys, certificates, all the data, to name a few. /*/ Pag. 56 Example: Encryption /* Clean up database */ USE EncryptTest GO CLOSE SYMMETRIC KEY TestTableKey GO DROP SYMMETRIC KEY TestTableKey GO DROP CERTIFICATE EncryptTestCert GO DROP MASTER KEY GO USE [master] GO DROP DATABASE [EncryptTest] GO /*/ Pag. 57 Summary • Encryption is a very important security feature of SQL Server. Long keys and asymmetric keys create unassailable, stronger encryption and stronger encryption uses lots of CPU to encrypt data. Stronger encryption is slower to process. When there is a huge amount of data to encrypt, it is suggested to encrypt it using a symmetric key. The same symmetric key can be encrypted further with an asymmetric key for additional protection, thereby adding the advantage of a stronger encryption. It is also recommended to compress data before encryption, as encrypted data cannot be compressed. /*/ Pag. 58 Phần II Pag. 59 Outline • Security requirements for web data. • Basic concepts of XML • Security policies for XML data protection and release • Access control mechanisms for XML data • XML-based specification of security informaiton • XML security: future trends Pag. 60 Web Data: Protection Requirements • The web is becoming the main informaiton dissemination means for many organizations • Strong need for models and mechanisms enabling the specification and enforcement of security policies for web data protection and release Pag. 62 Web Docs: Protection Requirements • Web documents may have a nested or hierarchical, inter-linked structure • Different portions of the same document may have different protection requirements We need a wide spectrum of protection granularity levels Pag. 63 Web Docs: Protection Requirements • Web documents may have an associated description of their structure: – DTDs and XML Schemas for XML documents – Data models for describing the logical organization of data into web pages Policies specified both at the schema and at the instance level Pag. 64 Web Docs: Protection Requirements • Documents with the same type and structure may have contents of different sensitivity degree: Policies that take the document content into account (content-based policies) Pag. 65 Web Docs: Protection Requirements • Supporting fine-grained policies could lead to the specification of a, possibly high, number of access control policies: Need of mechanisms for exception management and authorization propagation Pag. 66 Web Docs: Protection Requirements • Heterogeneity of subjects: – Subjects accessing a web source may be characterized by different skills and needs and may dynamically change – Conventional identity-based access control schemes are not enough Credentials based on subject characteristics and qualifications Pag. 67 Web Docs: Protection Requirements • In a web environment the traditional on user-demand mode of performing access control is not enough: Security policies enforcing both the pull and push dissemination modes Pag. 68 Web Data Source • PULL • PUSH Request Web Data Source View Dissemination Policies Pag. 69 Outline • Security requirements for web data • Basic concepts of XML • Security policies for XML data protection and release • Access control mechanisms for XML data • XML-based specification of security information • XML security: future trends Pag. 70 Why XML? • Because XML is becoming a standard for data representation over the web • XML compatibility is thus an important requirement for security policies, models and mechanisms for Web data sources Pag. 71 XML • Building blocks of XML are tagged elements that can be nested at any depth in the document structure • Each tagged element has zero or more subelements and zero or more attributes • Elements can be linked by means of IDREF(S) attributes • Optional presence of a DTD/XMLSchema for describing the structure of documents (well- formed vs valid documents) Taxation ... Import-Export ... Guns ... ... Transportation ... ... An XML Document Pag. 73 Graph Representation &1 &9&8&6&5&4&3 &2 &7LK75 &10 &12&11 &13 &14 &15 WordLawBulletin Law Law Summary SectionTopic BluePageReport Law Law Topic Topic Topic Summary SummarySummary Section {(Country,”USA”)} ... {(Country,”Germany”)} {(Country,”USA”)} {(GeoArea,”NorthA.”)} {(GeoArea,E.)} {(Country,”Italy”)} Taxation Guns Transportation Import-Export {(Date,”08/08/1999”)} RelatedLaws An XML DTD <!DOCTYPE WorldLawBulletin[ <!ATTLIST Law Id ID #REQUIRED Country CDATA #REQUIRED RelatedLaws IDREFS #IMPLIED> ]> Pag. 75 XML & Security Two main issues: 1. Development of access control models, techniques, mechanisms, and systems for protecting XML documents 2. Use of XML to specify security relevant information, (organizational policies, subject credentials, authentication information, encrypted contents) Pag. 76 The Author-X Project Pag. 77 Author-X • Java-based system for XML data sources protection • Security policy design and administration • Credential-based access control to XML document sources • Secure document dissemination and update Pag. 78 Author-X ACPs • Set-oriented and document-oriented policies • Positive and negative policies at different granularity levels, to enforce differentiated protection of XML documents and DTDs • Controlled propagation of access rights • ACPs reflect user profiles through credential- based qualifications Pag. 79 Enforcing access control • Subject specification • Protection object specification • Privilege • Propagation option Pag. 80 Subject Specification • User Identifiers OR • Subject credential: credential expression Ex: X.age > 21 Programmer(X) and X.country=“Italy” Pag. 81 Protection Object Specification • Identify the portions of a document(s) to which the authorization applies. We want to allow users to specify authorizations ranging from – sets of documents – to single elements/attributes within documents specification on DTD or documents [{doc|*}|{DTD|#}].[pathOfElem|ElemIds].[Attrs|links] Pag. 82 Privileges read • browsing navigate write • authoring append delete Pag. 83 Propagation option NO PROPAGATION Pag. 84 Propagation option FIRST LEVEL Pag. 85 Propagation option CASCADE Pag. 86 Examples of authorization rules P1 = ((LLoC Employee or European Division Employee), WorldLawBulletin.Law, browse_all, *) this authorization rule authorizes the LLoC and European Division Employees to view all laws (not contained in the BluePageReport element) in all instances of WorldLawBulletin relations among laws, that is, RelatedLaws attributes, are also displayed Pag. 87 Examples of authorization rules P4 = (European Division Employee, (WorldLawBulletin.BluePageReport.Section, GeoArea = Europe), browse_all, *) this authorization rule authorizes the European Division Employees to view the section pertaining to Europe of the BluePageReport in all instances of WorldLawBulletin Pag. 88 access request view administrative operations user SA Author-X DOM/XQL X