Database security. Problems and Solutions

261 Pages·2022·6.83 MB·English

by  Christopher Diaz

D s
atabase ecurity
LICENSE, DISCLAIMER OF LIABILITY, AND LIMITED WARRANTY
By purchasing or using this book and its companion files (the “Work”), you agree that
this license grants permission to use the contents contained herein, but does not give
you the right of ownership to any of the textual content in the book or ownership to
any of the information, files, or products contained in it. This license does not per-
mit uploading of the Work onto the Internet or on a network (of any kind) without
the written consent of the Publisher. Duplication or dissemination of any text, code,
simulations, images, etc. c ontained herein is limited to and subject to licensing terms
for the respective products, and permission must be obtained from the Publisher or
the owner of the content, etc., in order to reproduce or network any portion of the
textual material (in any media) that is contained in the Work.
Mercury Learning and Information (“MLI” or “the Publisher”) and any-
one involved in the creation, writing, production, accompanying algorithms, code,
or computer programs (“the software”), and any accompanying Web site or soft-
ware of the Work, cannot and do not warrant the performance or results that might
be obtained by using the contents of the Work. The author, developers, and the
Publisher have used their best efforts to ensure the accuracy and functionality of the
textual material and/or programs contained in this package; we, however, make no
warranty of any kind, express or implied, regarding the performance of these con-
tents or programs. The Work is sold “as is” without warranty (except for defective
materials used in manufacturing the book or due to faulty workmanship).
The author, developers, and the publisher of any accompanying content, and anyone
involved in the composition, production, and manufacturing of this work will not be
liable for damages of any kind arising out of the use of (or the inability to use) the
algorithms, source code, computer programs, or textual material contained in this
publication. This includes, but is not limited to, loss of revenue or profit, or other
incidental, physical, or consequential damages arising out of the use of this Work.
The data used throughout this text, including names of persons and companies are
for instructional purposes only. They have been researched with care but are not
guaranteed for any intent beyond their educational purpose.
The sole remedy in the event of a claim of any kind is expressly limited to replace-
ment of the book and only at the discretion of the Publisher. The use of “implied
warranty” and certain “exclusions” vary from state to state, and might not apply to
the purchaser of this product.
Companion files are available for download from the publisher by writing to
[email protected].
D s
atabase ecurity
Problems and Solutions
Christopher Diaz, Ph.D.
Mercury Learning anD Information
Dulles, Virginia
Boston, Massachusetts
New Delhi
Copyright ©2022 by Mercury Learning and Information LLC. All rights reserved.
This publication, portions of it, or any accompanying software may not be reproduced in any way,
stored in a retrieval system of any type, or transmitted by any means, media, electronic display
or mechanical display, including, but not limited to, photocopy, recording, Internet postings, or
scanning, without prior permission in writing from the publisher.
Publisher: David Pallai
Mercury Learning and Information
22841 Quicksilver Drive
Dulles, VA 20166
[email protected]
www.merclearning.com
1-800-232-0223
C. Diaz. Database Security.
ISBN: 978-1-68392-663-4
The publisher recognizes and respects all marks used by companies, manufacturers, and developers
as a means to distinguish their products. All brand names and product names mentioned in this book
are trademarks or service marks of their respective companies. Any omission or misuse (of any kind)
of service marks or trademarks, etc. is not an attempt to infringe on the property of others.
Library of Congress Control Number: 2022940435
222324321 Printed on acid-free paper in the United States of America.
Our titles are available for adoption, license, or bulk purchase by institutions, corporations, etc. For
additional information, please contact the Customer Service Dept. at 800-232-0223(toll free).
All of our titles are available in digital format at academiccourseware.com and other digital vendors.
The sole obligation of Mercury Learning and Information to the purchaser is to replace
the book, based on defective materials or faulty workmanship, but not based on the operation or
functionality of the product.
I thank my wife Sindy, who assisted with developing case studies,
as well as my family for inspiration and support.

C
ontents
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
viii • Contents
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
Contents • ix
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 186
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 locking 217