Table of Contents
Dedication v
Preface xi
Chapter 1 Introduction to Information Security, Data Security, and Database Security 1
1.1 Information Security 2
Confidentiality 2
Integrity 2
Availability 3
1.2 Security Threats, Controls, and Requirements 4
Security threats 4
Security controls 5
Security requirements 5
1.3 Data Security 6
1.4 Database Security 7
Data confidentiality 7
Data integrity 8
Data Availability 14
1.5 Summary 15
Chapter 2 Database Design 17
2.1 Normalization 18
2.2 Surrogate Keys and Data Integrity 24
2.3 Normalization, Access Restrictions, and Beyond 27
2.4 Summary 29
Chapter 3 Database Management and Administration 31
3.1 Backup and Recovery 32
Backup and restore of a specific database 33
Backup and restore of multiple specific databases 36
Backup and restore of specific tables 36
Backup of users, privileges, and other components 38
Deciding what to backup 39
3.2 User Account Security Configurations 40
Password expiration 40
Disabling/enabling user accounts 45
3.3 Summary 46
Chapter 4 Database User Accounts 47
4.1 Creating and Removing Database User Accounts 48
4.2 Listing User Accounts 53
4.3 Host-Restricted Accounts 54
4.4 Summary 58
Chapter 5 Database Privileges 59
5.1 Overview of Privileges and Database-Level Privileges 61
5.2 Capability to Manage Privileges 66
5.3 Listing Privileges 67
5.4 Removing Privileges 70
5.5 Working with TLS and Table-Level Privileges 73
5.6 TLS and Normalization Revisited 83
5.7 Column Level Security (CLS) 89
5.8 CLS and Evolving Data Access Requirements and Data 98
The capability for CEO and CFO to read salary data 99
The capability for employees to see address data 100
The capability for executives to keep private notes in the budget table 101
5.9 Row Level Security 104
5.10 Summary 104
Chapter 6 Roles 105
6.1 Defining Role Members and Data Access Requirements 106
6.2 Creating a Database Role, Showing Role Privileges, and Removing a Role 111
6.3 Assigning Privileges to Roles 113
6.4 Database Users and Role 118
Adding and removing a database user to a role 119
Listing, setting, and testing a user's role 121
The default role 125
Listing privileges and roles revisited 127
6.5 Roles and Evolution 131
A new employee is hired 131
An employee adds a role or moves to another role 133
An employee leaves a role or the organization 134
6.6 Summary 135
Chapter 7 Database Security Controls for Confidentiality 137
7.1 Views 137
Concept of a view 137
Creating a view 139
Showing a list of views and a view definition 141
Accessing the data of a view 142
Security considerations of a view 144
Deleting and redefining views 148
Views and multiple data access requirements 150
7.2 Encryption, Decryption, and Hashing 153
Encryption 154
Decryption 155
Hashing 156
Salting 162
7.3 Stored Routines 167
Stored functions 169
Stored procedures 173
Revisiting the password authentication implementation 175
7.4 Summary 177
Chapter 8 Transactions for Data Integrity 179
8.1 Commits, Rollbacks, and Automatic Commits 180
8.2 Beginning a Transaction with COMMIT or ROLLBACK 183
8.3 Beginning a Transaction with START TRANSACTION 190
8.4 Condition Issued COMMIT or ROLLBACK 190
8.5 Exception Issued ROLLBACK 192
8.6 A Larger Demonstration of Transactions 197
8.7 Summary 206
Chapter 9 Data Integrity with Concurrent Access 207
9.1 Concurrent Access and Backups 207
9.2 Concurrent Access with DML Statements 212
Table-level looking 217
Row-level locking 223
UPDATE locks 224
SHARE locks 227
9.3 Deadlock 231
9.4 Summary 234
Appendix 235
Index 245
