Chapter 5:
Database Security
EECS710: Information Security
Professor Hossein Saiedian
Fall 2014
Outline
• Introduced databases and DBMS
• Relational databases
• Database access control issues
▪ SQL, role-based
• Inference
• Statistical database security issues
• Database encryption
Database systems
• Structured collection of data stored for use by one or more
applications
• Contains the relationships between data items and groups of data
items
• Can sometimes contain sensitive data that needs to be secured
• Query language: Provides a uniform interface to the database
Database Security
Relational Databases
• Constructed from tables of data
▪ each column holds a particular type of data
▪ each row contains a specific value these
▪ ideally has one column where all values are unique, forming an identifier/key
for that row
• Have multiple tables linked by identifiers
• Use a query language to access data items meeting specified criteria
Relational databases
• Table of data consisting of rows and columns
▪ Each column holds a particular type of data
▪ Each row contains a specific value for each column
▪ Ideally has one column where all values are unique, forming an identifier/key
for that row
• Enables the creation of multiple tables linked together by a unique
identifier that is present in all tables
• Use a relational query language to access the database
▪ Allows the user to request data that fit a given set of criteria
A relational database example
Relational database terms
• Relation/table/file
• Tuple/row/record
• Attribute/column/field
• Primary key: uniquely identifies a row
• Foreign key: links one table to attributes in another
• View/virtual table: Result of a query that returns selected rows and
columns from one or more tables
Abstract view of a relation
Relational Database Elements
Structured Query Language
• Structure Query Language (SQL)
▪ originally developed by IBM in the mid-1970s
▪ standardized language to define, manipulate, and query
data in a relational database
▪ several similar versions of ANSI/ISO standard
CREATE TABLE department ( CREATE VIEW newtable (Dname, Ename, Eid, Ephone)
Did INTEGER PRIMARY KEY, AS SELECT D.Dname E.Ename, E.Eid, E.Ephone
Dname CHAR (30), FROM Department D Employee E
Dacctno CHAR (6) )
WHERE E.Did = D.Did
CREATE TABLE employee (
Ename CHAR (30),
Did INTEGER,
SalaryCode INTEGER,
Eid INTEGER PRIMARY KEY,
Ephone CHAR (10),
FOREIGN KEY (Did) REFERENCES department (Did) )
SQL injection attacks
• One of the most prevalent and dangerous network-based security
threats
• Sends malicious SQL commands to the database server
• Depending on the environment SQL injection can also be exploited to:
▪ Modify or delete data
▪ Execute arbitrary operating system commands
▪ Launch denial-of-service (DoS) attacks
A typical
injection
attack
Sample SQL injection
• The SQLi attack typically works by prematurely terminating a text
string and appending a new command
SELECT fname
FROM student
where fname is ‘user prompt’;
User: John’; DROP table Course;--
Sample SQL injection: tautology
$query= “
SELECT info FROM user WHERE name =
`$_GET[“name”]’ AND pwd = `GET[“pwd”]`
”;
Attacker enters: ` OR 1=1 –-
In-band attacks
• Tautology: This form of attack injects code in one or more conditional
statements so that they always evaluate to true
• End-of-line comment: After injecting code into a particular field,
legitimate code that follows are nullified through usage of end of line
comments
• Piggybacked queries: The attacker adds additional queries beyond
the intended query, piggy-backing the attack on top of a legitimate
request
Database Access Control
• DBMS provide access control for database
• assume have authenticated user
• DBMS provides specific access rights to portions of the database
▪ e.g. create, insert, delete, update, read, write
▪ to entire database, tables, selected rows or columns
▪ possibly dependent on contents of a table entry
• can support a range of policies:
▪ centralized administration
▪ ownership-based administration
▪ decentralized administration
Inferential attack (gathering info)
• There is no actual transfer of data, but the attacker is able to
reconstruct the information by sending particular requests and
observing the resulting behavior of the Website/database server
▪ Illegal/logically incorrect queries: lets an attacker gather important
information about the type and structure of the backend database of a Web
application
Out-band attack
• This can be used when there are limitations on information retrieval,
but outbound connectivity from the database server is lax
SQLi countermeasures
• Defensive coding: stronger data validation
• Detection
▪ Signature based
▪ Anomaly based
▪ Code analysis
• Runtime prevention: Check queries at runtime to see if they conform
to a model of expected queries
SQL Access Controls
• If the user has access to the entire database or just portions of it
• Two commands:
▪ GRANT {privileges | role} [ON table] TO {user | role | PUBLIC}
[IDENTIFIED BY password] [WITH GRANT OPTION]
o e.g. GRANT SELECT ON ANY TABLE TO john
▪ REVOKE {privileges | role} [ON table] FROM {user | role |
PUBLIC}
o e.g. REVOKE SELECT ON ANY TABLE FROM john
▪ WITH GRANT OPTION: whether grantee can grant “GRANT” option to
other users
• Typical access rights are:
▪ SELECT, INSERT, UPDATE, DELETE, REFERENCES
An Access Control Model
• Trong hệ CSDL, A[s,o] còn chứa các điều kiện cần thỏa để chủ thể s
có thể truy cập đối tượng o
▪ Phụ thuộc dữ liệu (data-dependent): chỉ xem được thông tin của các nhân viên
có salary < 1000 trong bảng Employee
▪ Phụ thuộc thời gian (time-dependent): chỉ được truy cập bảng Employee từ
8:00 sáng đến 5:00 chiều
▪ Phụ thuộc ngữ cảnh: có thể truy cập riêng từng thuộc tính name và salary
trong bảng Employee, nhưng không thể truy cập cả 2 thuộc tính này cùng lúc.
▪ Phụ thuộc lịch sử: chỉ xem được thuộc tính salary của các nhân viên nếu như
trước đó chưa xem thuộc tính name của nhân viên.
22
Cascading Authorizations
Role-Based Access Control
• Role-based access control work well for DBMS
▪ eases admin burden, improves security
• Categories of database users:
▪ application owner
▪ end user
▪ administrator
• DB RBAC must manage roles and their users
Database Encryption
• Databases typical a valuable info resource
▪ protected by multiple layers of security: firewalls, authentication, O/S access control systems,
DB access control systems, and database encryption
• Can encrypt
▪ entire database - very inflexible and inefficient
▪ individual fields - simple but inflexible
▪ records (rows) or columns (attributes) - best
o also need attribute indexes to help data retrieval
• Varying trade-offs
Summary
• Introduced databases and DBMS
• Relational databases
• Database access control issues
▪ SQL, role-based
• Inference
• Database encryption
Bài tập
Consider an SQL statement:
SELECT id, forename, surname FROM authors WHERE forename = ‘john’ AND surname = ‘smith’
a. What is this statement intended to do?
b. Assume the forename and surname fields are being gathered from user-supplied input, and
suppose the user responds with:
• Forename: jo’hn
• Surname: smith
• What will be the effect?
c. Now suppose the user responds with:
• Forename: jo’; drop table authors--
• Surname: smith
• What will be the effect?
Bài tập
• shows a fragment of code that implements the login functionality for a database application. The code dynamically
builds an SQL query and submits it to adatabase.
1. String login, password, pin, query
2. login = getParameter(“login”);
3. password = getParameter(“pass”);
3. pin = getParameter(“pin”);
4. Connection conn.createConnection(“MyDataBase”);
5. query = “SELECT accounts FROM users WHERE login=’” +
6. login + “‘AND pass = ’” + password +
7. “‘AND pin=” + pin;
8. ResultSet result = conn.executeQuery(query);
9. if (result!=NULL)
10 displayAccounts(result);
11 else
12 displayAuthFailed();
Code for Generating an SQL Query:
a. Suppose a user submits login, password, and pin as doe, secret, and 123. Show theSQL query that is generated.
b. Instead, the user submits for the login field the following:
• ’ or - -
• What is the effect?
Bài tập
c. suppose a user enters the following into the login field:
• ’UNION SELECT cardNo from CreditCards where acctNo = 10032 - -
• What is the effect?
Bài tập
Bài tập
• A sequence of grant operations for a specific access right on a table.
Assume at B revokes the access right from C. Using the conventions
defined in Section 5.2 , show the resulting diagram of access right
dependencies.
Bài tập
Imagine you are the database administrator for a military transportation system. Youhave a
table named cargo in your database that contains information on the various cargoholds
available on each outbound airplane. Each row in the table represents a singleshipment and
lists the contents of that shipment and the flight identification number. Onlyone shipment per
hold is allowed. The flight identification number may be cross-referencedwith other tables to
determine the origin, destination, flight time, and similar data. Thecargo table appears as
follows:
Bài tập
• Users hulkhogan and undertaker do not have the SELECT
access right to theInventory table and the Item table. These
tables were created by and are owned by userbruno-s. Write
the SQL commands that would enable bruno-s to grant
SELECT access tothese tables to hulkhogan and undertaker
Bài tập
• Consider a database table that includes a salary attribute. Suppose
the three queries sum, count, and max (in that order) are made on
the salary attribute, all conditioned on the same predicate involving
other attributes. That is, a specific subset of records is selected and
the three queries are performed on that subset. Suppose the first two
queries are answered, and the third query is denied. Is any
information leaked?
Bài tập
• Thực hiện kiểm soát quyền truy cập:
▪ User_Alice: Có quyền xem toàn bộ cột
"Tên" và "Nghề nghiệp," nhưng chỉ được
xem các hàng có mức lương dưới
50,000.
▪ User_Bob: Có quyền xem và chỉnh sửa
toàn bộ cột "Lương," nhưng chỉ có thể
xem thông tin các cột khác.
▪ User_Charlie: Có quyền truy cập vào
toàn bộ dữ liệu, bao gồm cả việc chỉnh
sửa mọi thông tin.
▪ Cấp quyền cho alice chỉ xem name
không xem lương trong cùng ngày truy
vấn 35
