05 Database Security - Chunk 01 (s.1-38)

Overview

Chapter 5 (Database Security; s.1; the file’s extracted text also names EECS710 Information Security, Prof. Hossein Saiedian, Fall 2014, which is not visible on the rendered slide image) starts with relational database vocabulary and SQL (s.2-10), then spends a long section on SQL injection attacks, their three categories and their countermeasures (s.11-20). It moves to database access control and the SQL GRANT/REVOKE commands (s.21-24), then inference (deducing unauthorized data from authorized queries) (s.25-29), and ends with database encryption and how to query encrypted data using row-level encryption plus indexes (s.30-37). Quiz style: Past Exam - Question Bank & Style Guide.

Detailed notes

1. Databases and relational databases (s.2-9)

• A database system is a structured collection of data stored for use by one or more applications; it contains the relationships between data items and groups of data items; it can contain sensitive data that needs securing; a query language provides a uniform interface (s.2). Architecture figure (s.3): see Figures.
• Relational database (s.4-5): constructed from tables of rows and columns; each column holds a particular type of data; each row has a specific value per column; ideally one column has all-unique values forming an identifier/key for the row; multiple tables linked by a unique identifier present in all tables; SQL is the query language to access data items meeting criteria.
• Terms (s.7): 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 returning selected rows and columns from one or more tables.
• Examples: phone-company schema linked by PhoneNumber (s.6), abstract relation with attributes $A_1\dots A_M$ and records $1\dots N$ (s.8), Department/Employee tables and a derived view (s.9). See Concept - Relational Database.

2. SQL (s.10)

• Originally developed by IBM in the mid-1970s; a standardized language to define, manipulate and query data in a relational database; several similar versions of the ANSI/ISO standard.
• Slide examples: CREATE TABLE department (Did INTEGER PRIMARY KEY, Dname CHAR(30), Dacctno CHAR(6)); CREATE TABLE employee (Ename CHAR(30), Did INTEGER, SalaryCode INTEGER, Eid INTEGER PRIMARY KEY, Ephone CHAR(10), FOREIGN KEY (Did) REFERENCES department (Did)); CREATE VIEW newtable (Dname, Ename, Eid, Ephone) AS SELECT ... FROM Department D, Employee E WHERE E.Did = D.Did. See Concept - SQL.

3. SQL injection (SQLi) (s.11-15)

• One of the most prevalent and dangerous network-based security threats; it sends malicious SQL commands to the database server; depending on the environment it can also modify or delete data, execute arbitrary operating system commands, launch denial-of-service (DoS) attacks (s.11). Figure s.12: a typical injection attack path.
• Tautology example (s.13-14): code builds SELECT * FROM items WHERE ID = ' + username + ' AND password = ' + password + '. With username wiley and password name' OR 'a'='a the query becomes WHERE ID = 'wiley' AND password = 'name' OR 'a'='a';. The OR 'a'='a' makes the WHERE clause always true, so the query is equivalent to SELECT * FROM items; and returns all entries in the items table, regardless of the entered password; the attacker bypasses the restriction to items owned by the authenticated user.
• End-of-line comment + piggybacked query (s.15): the attack typically works by prematurely terminating a text string and appending a new command. SELECT lname FROM student WHERE fname is 'user prompt' with input John'; DROP table Course;--. The trailing -- makes the rest of the statement a comment, removing the leftover single quote.
• See Concept - SQL Injection.

4. SQLi categories (s.16-19)

Type	Description (s.16)	Example (s.16)
In-band	uses the same channel for attack and data retrieval	SQL injection showing data on a web page
Inferential	deduces data from behaviour or timing, no direct data shown	Blind SQL injection
Out-of-band	uses one channel to attack, another to get data	SQLi using DNS/HTTP to send results externally

• In-band types (s.17): tautology (injects code in conditionals so they always evaluate to true, s.13), end-of-line comment (-- nullifies legitimate code that follows, s.15), piggybacked queries (extra queries added on top of a legitimate request, s.15).
• Inferential (s.18): there is no actual transfer of data, but the attacker reconstructs information by sending particular requests and observing the behaviour of the website/database server. Technique named: illegal/logically incorrect queries let an attacker learn the type and structure of the backend database; the vulnerability used is that the default error page returned by application servers is often descriptive.
• Out-of-band (s.19): data retrieved through a different channel (e.g. an email with the query results sent to the tester); used when information retrieval is limited but outbound connectivity from the database is lax.
• See Concept - SQLi Categories.

5. SQLi countermeasures (s.20)

• Defensive coding: stronger data validation; insufficient input validation leads to SQLi; e.g. input type checking so inputs supposed to be numeric contain only digits.
• Detection: signature based (match specific attack patterns), anomaly based (define normal behaviour then detect abnormal), code analysis (generate a wide range of SQLi attacks and assess the system’s response).
• Runtime prevention: check queries at runtime to see if they conform to a model of expected queries. See Concept - SQLi Countermeasures.

6. Database access control (s.21-24)

• A DBMS provides access control: it assumes an authenticated user and provides specific access rights (create, insert, delete, update, read, write) to the entire database, tables, selected rows or columns, possibly depending on the contents of a table entry (s.21).
• Three administration policies (s.21):

Policy	Who may grant and revoke
Centralized administration	a small number of privileged users
Ownership-based administration	the owner (creator) of a table, for that table
Decentralized administration	the owner, who may also authorize other users to grant and revoke rights on the table

• SQL commands (s.22): GRANT {privileges | role} [ON table] TO {user | role | PUBLIC} [IDENTIFIED BY password] [WITH GRANT OPTION], e.g. GRANT SELECT ON ANY TABLE TO john; REVOKE {privileges | role} [ON table] FROM {user | role | PUBLIC}, e.g. REVOKE SELECT ON ANY TABLE FROM john. Typical rights: SELECT, INSERT, UPDATE, DELETE.
• Right semantics (s.23): SELECT = read the entire database, individual tables or specific columns; INSERT = insert rows, or rows with values for specific columns; UPDATE = similar to INSERT; DELETE = delete rows from a table.
• Clauses (s.24): TO = user or role receiving the rights; PUBLIC = any user has the rights; IDENTIFIED BY = a password that must be used to revoke the rights of this GRANT (as stated on the slide); GRANT OPTION = grantee can grant this right to other users, with or without the grant option.
• See Concept - Database Access Control Policies and Concept - GRANT and REVOKE.

7. Inference (s.25-29)

• Inference in database security is “the process of performing authorized queries and deducing unauthorized information from the legitimate responses received” (s.25). Figure s.25: access control lets the user reach non-sensitive data and metadata, but inference derives sensitive data despite the blocked unauthorized access.
• Two techniques (s.26): analysing functional dependencies between attributes within a table or across tables (example: Rank $\to$ Salary), and merging views with the same constraints.
• Merging views example (s.27-28): views V1 = SELECT Availability, Cost FROM Inventory WHERE Department = "hardware" and V2 = SELECT Item, Department FROM Inventory WHERE Department = "hardware". Users are not authorized to see the relationship between Item and Cost. A user who knows the structure of the Inventory table and that the views maintain the same row order as the inventory can merge the two views to build table (c) of Figure 5.8: Shelf support (in-store/online, 7.99), Lid support (online only, 5.49), Decorative chain (in-store/online, 104.99), all hardware.
• Two countermeasure approaches (s.29): inference detection during database design (remove the inference channel by altering the database structure or the access control regime, e.g. split a table or use finer-grained RBAC roles) and inference detection at query time (detect an inference channel during a query or series of queries and deny or alter the query).
• See Concept - Inference.

8. Database encryption (s.30-37)

• Databases are a valuable resource protected by multiple layers: firewalls, authentication, O/S access control, DB access control and database encryption (s.30).
• What to encrypt (s.30): the entire database is inflexible (the user must download whole tables, decrypt and work with the results); records (rows) or columns (attributes) is best, and attribute indexes are also needed to retrieve data. To be flexible it must be possible to work with the database in its encrypted form.
• Architecture (s.31): user, client with query processor, metadata and encrypt/decrypt module, server with query executor and the encrypted database, and the data owner providing metadata and the database (see Figures).
• Example (s.32): SELECT Ename, Eid, Ephone FROM Employee WHERE Did = 15 becomes ... WHERE Did = 1000110111001110, i.e. the stored value is the encrypted bit string.
• Range-query problem (s.33): SELECT Ename, Salary FROM Employee WHERE Salary > 7000 fails because the set of encrypted values does not preserve the ordering of the original attribute.
• Solution (s.34-36): encrypt each entire row as one block $E(k,B)$ and keep indexes per attribute (e.g. Eid ranges 1-200 $\to$ 1, 201-400 $\to$ 2, 401-600 $\to$ 3, 601-800 $\to$ 4, 801-1000 $\to$ 5; names A,B $\to$ 1 … I,J $\to$ 5). Query “eid < 300”: the query processor requests all records with $I(eid)\le 2$; the server returns those rows; the processor decrypts all returned rows, discards those not matching the original query and returns plaintext to the user.
• Further solutions: research has produced more flexible ways to query encrypted databases (CryptDB link on the slide, s.37).
• See Concept - Database Encryption and Concept - Querying an Encrypted Database.

9. Summary (s.38)

Introduced databases and DBMS; relational databases; database access control issues (SQL, role-based); inference; statistical database security issues; database encryption. Statistical database security is not on any slide (gap).

Figures & diagrams

• Database security architecture (s.3): database utilities feed the DDL processor, which writes the database description tables; user applications and user queries feed the DML and query language processor; the database description tables also feed the DML processor and the DBMS, and the DML processor outputs to the DBMS; the DBMS (transaction manager, file manager) reads the authorization tables and concurrent access tables and works on the physical database. DDL = data definition language, DML = data manipulation language.
• Relational database example (s.6): Primary table (PhoneNumber, Last name, First name, address) linked by PhoneNumber to Caller ID table, Touch-tone table (each: PhoneNumber, Has service Y/N), Billing History table (Date, Transaction type, Transaction amount) and Current Bill table (Current date, Previous balance, Current charges, Date of last payment, Amount of last payment). Takeaway: the common PhoneNumber column links the tables.
• Abstract relation (s.8): attributes $A_1,\dots ,A_j,\dots ,A_M$ across, records $1,\dots ,i,\dots ,N$ down, cell $x_{ij}$.
• Relational database elements (s.9): Department table (Did primary key; Dname; Dacctno) and Employee table (Ename, Did foreign key, SalaryCode, Eid primary key, Ephone) with 5 and 7 rows; plus a view (Dname, Ename, Eid, Ephone) with 7 rows. Recomputed check: all 7 employees appear once with the department name that matches their Did (4 human resources, 8 education, 9 accounts, 13 public relations, 15 services). Note two different employees named Robin (Eid 2345 and 2976), so Ename is not a key.
• Typical injection attack (s.12): hacker on the Internet reaches web servers through router, firewall and switch (wireless access point attached), web servers reach a web application server, which reaches database servers and the database; legend: green line = data exchanged between hacker and servers, yellow two-way-traffic sign icons (two of them) = two-way traffic between hacker and web server, starburst = credit card data retrieved from the database. Takeaway: the attack goes through the legitimate web path, past the firewall.
• Inference (s.25): non-sensitive data and metadata on the left, sensitive data (black) on the right, access control bar between; authorized access arrow reaches non-sensitive data, unauthorized access to sensitive data is crossed out, yet a grey inference arrow links non-sensitive to sensitive data; a large curved grey arrow runs from the user’s side up to the non-sensitive data and metadata (the inference route).
• Inference example, Figure 5.8 (s.28): (a) inventory table (6 items: Shelf support 7.99 hardware, Lid support 5.49 hardware, Decorative chain 104.99 hardware, Cake pan 12.99 housewares, Shower/tub cleaner 11.99 housewares, Rolling pin 10.99 housewares; Availability in-store/online or online only); (b) two views; (c) table derived by combining query answers (3 hardware rows).
• Database encryption architecture (s.31): (1) user sends the original query to the client’s query processor; (2) transformed query goes to the server’s query executor; (3) encrypted result returns; (4) plaintext result reaches the user. The client holds metadata and an encrypt/decrypt module; the data owner supplies metadata (dashed) and the encrypted database to the server.
• Row encryption with indexes (s.34-35): s.34 shows a plain table (Eid 23 Tom 70K Maple 45; 860 Mary 60K Main 83; 320 John 50K River 50; 875 Jerry 55K Hopewell 92) and an index table (Eid ranges, Ename letter groups); s.35 shows each row as one ciphertext $E(k,B)$ with index columns $I(eid),I(name),I(salary),I(addr),I(Did)$, e.g. row 1: 1, 10, 3, 7, 4. Checked: Eid 23, 860, 320, 875 map to indexes 1, 5, 2, 5, and Did 45, 83, 50, 92 appear as 4, 8, 5, 9 (consistent with tens-digit buckets, inferred). The name index on s.34 stops at I,J $\to$ 5 (”…”), so values like 10 and 7 for Tom and Mary mean the slide’s index table is truncated.

Key terms

Concept - Relational Database · Concept - SQL · Concept - SQL Injection · Concept - SQLi Categories · Concept - SQLi Countermeasures · Concept - Database Access Control Policies · Concept - GRANT and REVOKE · Concept - Inference · Concept - Database Encryption · Concept - Querying an Encrypted Database

Formulas

No formulas in the slides, but key code patterns (exact):

• Tautology input: name' OR 'a'='a $\Rightarrow$ WHERE ID = 'wiley' AND password = 'name' OR 'a'='a' (s.13).
• Piggyback + comment: John'; DROP table Course;-- (s.15).
• Grant: GRANT SELECT ON ANY TABLE TO john; revoke: REVOKE SELECT ON ANY TABLE FROM john (s.22).
• Index query rewrite: $eid<300\Rightarrow I(eid)\le 2$ (s.36).

Likely exam questions

1. Define primary key, foreign key and view. Primary key uniquely identifies a row; foreign key links one table to attributes in another; view = virtual table, result of a query returning selected rows and columns from one or more tables (s.7).
2. What is SQL injection and what can it do? Sending malicious SQL commands to the database server; can modify or delete data, execute OS commands, launch DoS (s.11).
3. Explain the tautology attack with an example. Injected name' OR 'a'='a makes the WHERE always true so SELECT * FROM items returns all rows regardless of password (s.13-14).
4. What do -- and ; do in the injection John'; DROP table Course;--? ; ends the query so a second command (piggyback) runs; -- turns the rest into a comment and removes the leftover quote (s.15).
5. Compare in-band, inferential and out-of-band SQLi.

	In-band	Inferential	Out-of-band
Channel	same for attack and results	no data transferred, behaviour/timing observed	different channel for results
Example	data on the web page	Blind SQLi	results via DNS/HTTP/email
Types / tool	tautology, end-of-line comment, piggybacked queries	illegal/logically incorrect queries, descriptive error pages	used when outbound connectivity is lax

(s.16-19) 6. List SQLi countermeasures. Defensive coding (input validation), detection (signature, anomaly, code analysis), runtime prevention (s.20). 7. Centralized vs ownership-based vs decentralized administration? (s.21) Few privileged users / table owner / owner can delegate granting and revoking. 8. Write the command to give john SELECT on every table, then take it away. GRANT SELECT ON ANY TABLE TO john / REVOKE SELECT ON ANY TABLE FROM john (s.22). Explain WITH GRANT OPTION (s.24). 9. What is inference? Give the two techniques and two countermeasure approaches. Authorized queries used to deduce unauthorized information; functional dependencies and merging views; detection at design time and at query time (s.25-29). 10. Why is encrypting a whole database inflexible, and how can range queries work on encrypted data? User must download, decrypt all; encrypted values lose ordering; fix by encrypting rows as blocks with attribute indexes, then decrypt returned rows and discard non-matching ones (s.30, 33-36). 11. Fill in the blank: in a GRANT, the value ____ in the TO clause means that any user has the specified rights. PUBLIC (s.24).

Added context

Statistical databases appear in the s.38 summary only. Do not invent their content here; see Added Context - Topics Mentioned but Not on the Slides.

Common confusions

• Tautology vs piggyback vs comment: tautology changes the condition to true; piggyback adds another statement after ;; end-of-line comment -- cuts off the rest. The slide’s own examples can combine them (s.17 points to s.13 and s.15).
• Inferential SQLi vs inference (database security): inferential SQLi is an attack category (blind injection, deducing from behaviour, s.16, 18); inference is a general leak where authorized queries reveal unauthorized facts (s.25).
• Out-of-band vs in-band: out-of-band uses a second channel (e.g. email, DNS), in-band uses the same one (s.16).
• PUBLIC vs GRANT OPTION: PUBLIC means any user; GRANT OPTION means the grantee can pass the right on (s.24).
• Closed vs open applies to access control (see Concept - Open and Closed Policies), not to SQLi.
• Encrypting columns vs rows: slides call “records (rows) or columns” best; the worked example uses whole rows with indexes (s.30, 35).

Slide errors and gaps noticed

• s.10: the CREATE VIEW newtable example is missing commas (D.Dname E.Ename, Department D Employee E); write D.Dname, E.Ename and Department D, Employee E. Also the first bullet reads “Structure Query Language (SQL)”; the slide title “Structured Query Language” is correct.
• s.13: the query string quoting on the slide (' ") is garbled; the final query shown is what the slide says (s.14): the OR condition makes the WHERE clause always true. (Added context: this works because AND binds tighter than OR.)
• s.15: the example queries table student but drops Course; the point is only to show a piggybacked statement.
• s.33: the example uses Salary > 7000 while s.34 salaries are written 70K, 60K, 50K, 55K (mismatch of units, harmless for the concept).
• s.38 lists “Statistical database security issues”; no slide covers it.
• s.24: the claim that IDENTIFIED BY supplies the password needed to revoke is as printed on the slide; see the caveat in Added Context - Topics Mentioned but Not on the Slides (added context: mainstream SQL does not use IDENTIFIED BY this way).

Slide coverage

Slide	Content	Covered in
1	Title: Chapter 5 Database Security	Overview
2	Database systems	Detailed 1
3	Database security architecture figure	Detailed 1, Figures
4	Relational databases	Detailed 1
5	Relational databases (more)	Detailed 1
6	Relational database example figure	Detailed 1, Figures
7	Relational database terms	Detailed 1
8	Abstract view of a relation	Detailed 1, Figures
9	Relational database elements (tables, view)	Detailed 1, Figures
10	SQL and CREATE statements	Detailed 2
11	SQL injection attacks	Detailed 3
12	A typical injection attack figure	Detailed 3, Figures
13	SQLi example: tautology	Detailed 3
14	Tautology result	Detailed 3
15	Piggyback and end-of-line comment	Detailed 3
16	SQLi categories table	Detailed 4
17	In-band attacks	Detailed 4
18	Inferential attacks	Detailed 4
19	Out-of-band attacks	Detailed 4
20	SQLi countermeasures	Detailed 5
21	Database access control, administration policies	Detailed 6
22	SQL GRANT/REVOKE	Detailed 6
23	Access right semantics	Detailed 6
24	TO, PUBLIC, IDENTIFIED BY, GRANT OPTION	Detailed 6
25	Inference definition + figure	Detailed 7, Figures
26	Two inference techniques	Detailed 7
27	Merging views example	Detailed 7
28	Figure 5.8 inference example	Detailed 7, Figures
29	Inference countermeasures	Detailed 7
30	Database encryption options	Detailed 8
31	Database encryption architecture figure	Detailed 8, Figures
32	Query encrypted database example	Detailed 8
33	Range query problem	Detailed 8
34	Rows as blocks and indexes	Detailed 8, Figures
35	Encrypted rows with index columns	Detailed 8, Figures
36	Query with eid<300	Detailed 8
37	More solutions (CryptDB link)	Detailed 8
38	Summary	Detailed 9

Previous: 04 Access Control - Chunk 01 (s.1-28) (previous deck) | Next: none | Up: 05 Database Security - Index