Phần I
Pag. 1
ReView
Database Security and Auditing
2
Introduction to SQL Encryption
• 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
Pag. 3
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. 4
Introduction to SQL Encryption
Pag. 5
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. 6
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. 7
Service Provider Architecture
Pag. 8
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. 9
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 resolve later at client-side, after
encrypted representation, decryption. Pag. 10
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. 11
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. 12
Building the Index
• Mapping function maps a value v in the domain of
attribute A to partition id
Pag. 13
Storing Encrypted Data
Pag. 14
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. 15
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. 16
Mapping Conditions
Pag. 17
Mapping Conditions
Pag. 18
Mapping Conditions
Pag. 19
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
set difference, union,
elimination,
projection
Pag. 20
Selection Operator
Pag. 21
Selection Operator
Pag. 22
Join Operator
Pag. 23
Join Operator
Pag. 24
Join Operator
Pag. 25
Grouping & Aggregation Operator
Pag. 26
Query Decomposition
Pag. 27
Query Decomposition
Pag. 28
Query Decomposition
Pag. 29
Query Decomposition
Pag. 30
Query Precision vs. Privacy
Pag. 31
Fine Encryption Granularity
Pag. 32
Can we do better with aggregation?
Pag. 33
Aggregation over encrypted data
Pag. 34
Aggregation over encrypted data
Pag. 35
In relational DBMS
Pag. 36
Complete example
Pag. 37
Complete example
Pag. 38
Complete example
Pag. 39
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. 40
/*
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. 41
/*
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. 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
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. 44
/*/
Example: Encryption
/* Check the content of the TestTable */
USE EncryptTest GO SELECT * FROM TestTable GO
Result of the SQL query
Pag. 45
/*/
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. 46
/*/
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. 47
/*/
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. 48
/*/
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. 49
/*/
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. 50
/*/
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. 51
/*/
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. 52
/*/
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. 53
/*//
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. 54
/*//
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. 55
/*//
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. 56
/*/
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. 57
Phần II
Pag. 58
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. 59
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. 60
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. 62
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. 63
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. 64
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. 65
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. 66
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. 67
Dissemination Policies
Request
Web Data Source
• PULL
View
Web Data Source
• PUSH
Pag. 68
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. 69
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. 70
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)
Pag. 71
An XML Document
...
...
Graph Representation
WordLawBulletin
&1
{(Date,”08/08/1999”)}
Law BluePageReport Law
&2
&7
{(Country,”USA”)} {(Country,”Italy”)} RelatedLaws LK75
Summary Section Section Topic Summary Topic
{(GeoArea,E.)} &8
&3
&4
&5
&6
&9
{(GeoArea,”NorthA.”)}
...
Law Law Import-Export Taxation
&13
&10
{(Country,”Germany”)} {(Country,”USA”)}
&11
&12
&14
&15
Summary Topic Summary Topic
Guns Transportation
Pag. 73
An XML DTD
Country CDATA #REQUIRED RelatedLaws IDREFS #IMPLIED> ]>
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. 75
The Author-X Project
Pag. 76
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. 77
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. 78
Enforcing access control
• Subject specification • Protection object specification • Privilege • Propagation option
Pag. 79
Subject Specification
• User Identifiers
OR • Subject credential: credential expression
Ex: X.age > 21
Programmer(X) and X.country=“Italy”
Pag. 80
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. 81
Privileges
read
• browsing
navigate
• authoring
write append delete
Pag. 82
Propagation option
NO PROPAGATION
Pag. 83
Propagation option
FIRST LEVEL
Pag. 84
Propagation option
CASCADE
Pag. 85
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. 86
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. 87
user
SA
access request
view
administrative operations
Author-X
X-Access
X-Admin
DOM/XQL
X-Bases Encrypted doc.base
Credential base
Policy base
XML Source
Pag. 88
Information Pull - Architecture
Internet Browser
CLIENT
• query
DTD
Internet
XML VIEW
Web Server Excelon Server Server Extension (X-Access)
XML Parser
XQL
X-Path
Excelon File System
SERVER
XML source
Pag. 90
Access request
Target Document
User
Password
query
Pag. 92
Query result
Query result
Pag. 93
Push Dissemination Mode
• Since:
– Different subjects -> different views – Wide range of protection granularities – High number of subjects
Number of views can be too large
Solution-> Encryption Techniques
Pag. 94
Push Dissemination Mode
• The approach is based on encrypting
different portions of the same document with different keys
• The same (encrypted) copy is then
broadcasted to all subjects
• Each subject only receives the key(s) for
the portions he/she is enabled to see
Pag. 95
Information Push - Main Issues
• How to encrypt the documents in a source • Which and how many keys should be
distributed to which subjects
• How to securely and efficiently distribute keys to subjects in such a way that keys are received only by the entitled subjects
Pag. 96
How to Encrypt Documents
• Document encryption is driven by the specified
access control policies: all the document portions to which the same access control policies apply are encrypted with the same key • Thus, to determine which keys should be sent to a particular subject it is only necessary to verify which are the access control policies that apply to that subject and then sending the keys associated with these policies
Pag. 97
Well-Formed Encryption
P2
&1
&5
P1,P3
&2
&8
P1,P3
&3
&4
&6
&7
&9
&13
P3
P1,P3
P1,P3
P1,P3
P1,P3
&14
&10
P3
&11
&12
&15
&16
Pag. 98
Well-Formed Encryption
P2
&1
Node encrypted with key K1
&5
P1,P3
&2
&8
P1,P3
&3
&4
&6
&7
&9
&13
P3
P1,P3
P1,P3
P1,P3
P1,P3
&14
&10
P3
&11
&12
&15
&16
Pag. 99
Well-Formed Encryption
P2
&1
&5
P1,P3
&2
&8
P1,P3
&3
&4
&6
&7
&9
&13
P3
P1,P3
P1,P3
P1,P3
P1,P3
&14
&10
P3
Nodes encrypted with key K2
&11
&12
&15
&16
Pag. 100
Well-Formed Encryption
P2
&1
&5
P1,P3
&2
&8
P1,P3
&3
&4
&6
&7
&9
&13
P3
P1,P3
P1,P3
P1,P3
P1,P3
&14
&10
P3
Nodes encrypted with key K3
&11
&12
&15
&16
Pag. 101
Well-Formed Encryption
P2
&1
&5
P1,P3
&2
&8
P1,P3
&3
&4
&6
&7
&9
&13
P3
P1,P3
P1,P3
P1,P3
P1,P3
&14
&10
P3
Nodes encrypted with key Kd
&11
&12
&15
&16
Pag. 102
Well-Formed Encryption
P2
&1
&5
P1,P3
&2
&8
P1,P3
&3
&4
&6
&7
&9
&13
P3
P1,P3
P1,P3
P1,P3
P1,P3
&14
&10
P3
P1
K2
P2
K1
&11
&12
&15
&16
P3
K2, K3
Pag. 103
Key Management
• Key assignment scheme such that:
– From the key associated with a policy P1 it is
possible to derive the keys associated with all the policy configurations containing P1
• Benefits:
– The system should manage in the worst case a
number of keys equal to the size of the Policy Base – Each subject receives a key for each policy he/she
satisfies
Pag. 104
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. 106
Why?
• It allows a uniform protection of XML documents and their security-related information
• It facilitates the export and exchange of
security information
Pag. 107
Goals
• Definition of an XML-based language for specifying security-related information for web documents: – Subject credentials – Access control policies for web documents satisfying the previously stated requirements
An example: X-Sec the XML-based language
developed in the framework of Author-X
Pag. 108
X-Sec Credentials
• Credentials with similar structure are
grouped into credential types
• A credential is a set of simple and
composite properties
• Credential types • Credentials
DTDs XML documents
Pag. 109
X-Sec credential type
email?, company)>
cIssuer CDATA #REQUIRED>
]>
X-Sec credential
24 Baker Street
X-Sec Policy Specification
• XML template for specifying credential-
based access control policies
• The template is as general as possible to be able to model access control policies for a variety of web documents (e.g., HTML, XML)
Pag. 114
X-Sec Policy Base Template
]>
Pag. 115
Instantiation for XML Sources
value="cascade"/>
value="cascade"/>
="/issues/issuesTuple/articles/ articlesTuple/abstract"/> value="no_prop"/> Pag. 116
• 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. 117 • Secure publishing of XML documents: – A new class of information-centered applications based on Data dissemination – Possible scenarios: • Information commerce: digital libraries, electronic news
• Intra-company information systems • Security requirements: – Confidentiality
– Integrity
– Authenticity
– Completeness Pag. 118 Traditional Architecture •The Owner is the producer of information
• It specifies access control policies
• It answers to subject queries Pag. 119 •Benefits: •Scalability
•No Bottleneck Pag. 120 • B. Dournee, XML Security, RSA Press, 2002. • E. Bertino, B. Carminati, E. Ferrari, and B. Thuraisingham, XML Security,
Addison-Wesley, in preparation. Pag. 121 • E. Bertino and E. Ferrari. Secure and Selective Dissemination of XML Documents, ACM Trans. on
Information System and Security, to appear • E. Bertino, S. Castano, e E. Ferrari. Author- X: a Comprehensive System for Securing XML Documents,
IEEE Internet Computing, May 2001 • E. Bertino, S. Castano, e E. Ferrari. Securing XML Documents: the Author-X Project Demonstration, Proc.
of the ACM SIGMOD Conference 2001 • E. Bertino, S. Castano, E. Ferrari, M. Mesiti. Specifying and Enforcing Access Control Policies for XML
Document Sources. World Wide Web Journal, 3(3),
2000 Pag. 122 • Web sites: – The XML Security Page: http://www.nue.et-inf.uni- siegen.de/~geuer-pollmann/ xml/security.html
– OASIS Consortium: http://www.oasis-open.org
– World Wide Web Consortium: http://www.w3.org Pag. 123Outline
Research Trends
Secure Publishing
Information
Owner
Subject
Third-Party Architecture
Docs
View
Publisher
•The Publisher is responsible
for managing (a portion of)
the Owner information and
for answering subject
queries
Owner
Query
Subscription
Subject
Main References
Main References
Main References
![Bài giảng Bảo mật thông tin [năm] mới nhất/chi tiết](https://cdn.tailieu.vn/images/document/thumbnail/2025/20250506/vinarutobi/135x160/7791746530096.jpg)
