Introduction - Purdue University Calumet

Document Sample
Introduction - Purdue University Calumet Powered By Docstoc
					                     Purdue        University Calumet
                     School        o f T e c h n o l o g y

           Database Security Policies and Procedures
             and Implementation for the Disaster
             Management Communication System
                      In partial fulfillment of the requirements for the
                        Degree of Master of Science in Technology

                                     A Directed Project


                                   Radostina Georgieva

Committee Member                                  Approval Signature              Date

Professor Barbara Nicolai, Chair    _______________________________________   ____________

Professor Charles Winer             _______________________________________   ____________

Dr. Keyuan Jiang                    _______________________________________   ____________
                                                                            Database Security Policies and Procedures for DMCS 2

                                                           Table of Contents

Abstract/Executive Summary ........................................................................................................3
Introduction ...................................................................................................................................3
Statement of Problem ...................................................................................................................5
Significance of Problem .................................................................................................................5
Statement of Purpose ....................................................................................................................6
Limitations .....................................................................................................................................7
Literature Review...........................................................................................................................8
  Introduction ................................................................................................................................8
  Database security challenges and requirements.......................................................................9
Procedures ...................................................................................................................................25
Time Action Plan ..........................................................................................................................27
Conclusion ....................................................................................................................................27
Appendix A...................................................................................................................................36
  Introduction ..............................................................................................................................36
  Part I Security Architecture ...................................................................................................39
  Part II Operating System Security Fundamentals .................................................................51
  Part III Administration of Users .............................................................................................63
  Part IV Profiles, Passwords, Privileges, and Roles ................................................................71
  Part V Database Application Security Models ......................................................................84
  Part VI Virtual Private Database............................................................................................91
  Part VII Database Auditing Models .....................................................................................103
  Part VIII Application and Database Activities Auditing ......................................................113
Appendix B .................................................................................................................................124
                                                  Database Security Policies and Procedures for DMCS 3

                                 Abstract/Executive Summary

       Databases have become a major component of many modern organizations. They store

sensitive information, which often is the target of malicious attacks. Attacks have become

more targeted toward certain information or organizations, which has made information

security a problem that needs to be addressed by every organization.              The securing of

information needs to be done in accordance with government regulations. With laws that need

to be obeyed and new security threats being introduced along with technology advancement,

information security becomes a challenge. The Disaster Management Communication System

handles confidential and personal information and therefore becomes a target to information

security attacks. If not secured, the database is vulnerable and accessible to virtually anybody,

which means that clients’ personal information is exposed. This could make the DMCS subject

to penalties and fines.

       Data encryption combined with proper user administration (assigning roles and

passwords) and auditing is a good way of protecting information. The advantage of data

encryption is that it protects data while meeting numerous regulations, such as Health

Insurance Portability and Accountability Act, Sarbanes-Oxley Act, and Privacy Act of 1974. SQL

Server 2008 provides built-in data encryption and key management features, which are utilized

for the database security for the Disaster Management Communication System.


       “Security violations and attacks are increasing globally at an annual average rate of

20%.” (Afyouni, 2005). Today, organizations have more operational functions as well as
                                                    Database Security Policies and Procedures for DMCS 4

support functions depending on databases. As databases become the foundation of many

companies functionality and as it becomes more vulnerable to various attacks, database

security becomes an important matter. Databases often contain confidential information such

as customers and employees personal information, which makes securing the database a

crucial part of building a reliable system. It is important that databases be properly secured

while the right information is still available to the right customer or employee.

       There are different types and levels of severity for database attacks. The attacks could

be internal, caused by excessive privileges given to customers and employees, or external such

as SQL injection by an attacker. Severity of database attacks depends on how much and what

part of the system has been violated.

       The U.S. government has issued regulations regarding database security, which

companies are to follow. There are standard policies and procedures for securing databases.

“All organizations in the public, private, or government sector are therefore required to

establish effective computer security policies and procedures either by law, contract, or just

plain good business practice” (Peltier, 1991). Utilizing the procedures in accordance with the

government regulations is the first step of securing a database. Each database needs to be

periodically audited and the security needs to be updated.

       For the Disaster Management Communication System, database security is crucial. The

database contains confidential and personal information. In order for the system to operate

effectively, its database needs to be secured so that data loss and attacks do not occur.
                                                Database Security Policies and Procedures for DMCS 5

                                   Statement of Problem

       The Disaster Management Communication System’s database is designed to be

remotely accessed by numerous users, and needs to provide accurate and often confidential

information. The database contains vulnerable information, which if compromised, could

sabotage the system’s operations. The problem is that currently there is no security for the

DMCS and keeping the integrity of the database intact becomes an important task. Internal as

well as external threats need to be taken into consideration when implementing database

security policies and procedures. The Gramm-Leach-Bliley Act (GLBA) requires “all financial

institutions to design, implement, and maintain safeguards to protect customer information,”

(GLB Compliance - Gramm-Leach Bliley Act, 2006).       The Health Insurance Portability and

Accountability Act (HIPAA) requires “safeguards to protect the privacy of personal health

information, and sets limits and conditions on the uses and disclosures that may be made of

such information without patient authorization,” (Health Information Privacy).          These are

compliance requirements that companies need to follow when implementing the security


                                  Significance of Problem

       The Disaster Management Communication System is designed to store clients’ personal

information such as social security numbers, and addresses; as well as information about

available resources from the system, such as vouchers, food, and transportation. “With the

constant march toward a paperless business environment, database systems are increasingly

being used to hold more and more sensitive information, so they present an increasingly
                                                  Database Security Policies and Procedures for DMCS 6

valuable target” (Litchfield, Anley, Heasman, & Grindlay, 2005). If not secured properly, the

database becomes vulnerable to attacks, which could lead to several implications. The entire

system could become corrupted and/or nonfunctional, data loss can occur, and personal

information could be stolen. Improper database security can be considered violation of laws

and lead to significant financial loss and lawsuit. Building security into the system keeps it

intact and running efficiently, which will benefit the whole community.

                                    Statement of Purpose

       This project studies data exploitation, threats, and defense, as well as securing

databases in a SQL Server environment specifically. It discusses SQL Server 2008 best security

practices and how to apply them to the Disaster Management Communication System to

comply with federal regulations. The final product of the project is a Database Security Policy

and Procedure Manual including the installation of the SQL Server 2008 software.                 The

Database Security Policy and Procedure Manual provide detailed instructions and

recommendations regarding security features enforced for the server and database of the

Disaster Management Communication System. By installing SQL Server 2008 and enabling

built-in features, many database security issues are addressed and government regulations



       SQL       Structured Query Language

       HIPAA     Health Insurance Portability and Accountability Act
                                                 Database Security Policies and Procedures for DMCS 7

      SOX       Sarbanes-Oxley Act

      ePHI      Electronic Protected Health Information

      MSCAPI Microsoft Cryptographic Application Programming Interface

      TDE       Transparent Data Encryption

      EKM       Extensible Key Management

      PKI       Public Key Infrastructure

      HSM       Hardware Security Modules

      EFT       Electronic Funds Transfer

      DDL       Data Definition Language

      DML       Data Manipulation Language

      OLAP      Online Analytical Processing

      WMI       Windows Management Instrumentation

      DBMS      Database Management System


      This study is limited to researching SQL Server, therefore findings and conclusions about

database security risks and threats might be biased towards SQL Server, the environment in

which the Disaster Management Communication System is developed. The results of the

research might not completely represent how frequently these threats occur or what their

consequences are.
                                                   Database Security Policies and Procedures for DMCS 8


       A delimitation of this study is that it only addresses detailed data security procedures

specific to SQL Server and not any other database platforms. It is focused on the Disaster

Management Communication System project, and implemented according to its specific

requirements, and in compliance with government and HIPAA regulations for web access.

                                       Literature Review

       Databases are widely used in different types of industries and are responsible for storing

and managing sensitive information. They are what companies rely on for the proper and

effective operation of their business. In order for the databases to be efficient, they need to be

secured. Previously, security management used to rely mostly on intrusion detection, firewall,

and anti-virus software. Today “attacks are more targeted than before” and there is “an

increase in the number of attacks aimed at acquiring specific data or causing damage at specific

organizations” (Louvel, 2004). This is why security management today utilizes risk assessment

tools, which continuously detect security vulnerabilities and allow companies to take

preventive actions. Securing databases is a challenge for companies for several reasons.

Databases need to be secured in a way that sensitive information is only available to authorized

individuals and at the same time information is readily available to users that need it.

Authorization of the personnel to access confidential information could be risky. Internal

threats cannot be predicted or avoided but they need to be taken into consideration when

granting data access permission. Employees should only have access to information needed to
                                                    Database Security Policies and Procedures for DMCS 9

complete their job. Exposure of sensitive information should be kept at a minimum. Another

challenge with data security that companies face is the strict government regulations and

standards for protecting data. “Some regulations are focused on protection of specific industry

information, where others are more concerned with proper disclosure of data loss incidents

and general privacy attributes” (Shackleford, 2007).

Database security challenges and requirements

       Afyouni (2005) found that the cost of data loss is continuously rising because of various

attacks and improper implementation of database security.                    Information integrity,

confidentiality, and availability are the three components of information security.                The

accessibility and integrity of data need to be protected for the proper operability of any

company. Validation and verification controls are used to protect data and to ensure that

information has not been tampered with.

       In their book, Fernandez Summers, and Wood (1981) summarized that it is important to

restrict users of database access, providing them with only what is necessary for their job.

Databases have different levels of importance and sensitivity, as well as different ways to be

accessed and used, which need to be controlled and monitored in order to guarantee security.

The following security policies are discussed: security administration policies (centralized or

decentralized control, and ownership versus administration), policies for access-control

specification (maximized sharing, open and closed system, name-dependant access control,

content-dependant access control, context-dependant control, history-dependant control, and

access types), policies for information flow control, and policies for enforcing control.
                                                  Database Security Policies and Procedures for DMCS 10

       Peltier (1992) found that establishing responsibility levels and control of employees is

the best way to constrain the exposure of data. Security is described as a people problem

rather than a technical problem. One of the major database security problems stems from the

unnecessary access employees have to data. By classifying information in order to limit its

exposure, companies can ensure that controls are enforceable and operational costs are at a

minimum. Employee awareness programs or helping employees understand why data security

and complying with regulations are important is an initial step toward building security. Peltier

(1992) developed security policies and mission statements, created an employee awareness

program, and explained how to monitor employee and company compliance, as well as how to

meet security laws.

       Riccardi (2001) discussed reliability and security in database servers. He mentioned that

a secure system is one that generates backups, which along with transaction logs can do a full

recovery of databases even from the most catastrophic failures. Database systems need to be

designed with the possibility of failures in mind and plans for reacting in the case of failure

should be created. Riccardi (2001) listed several kinds of backup strategies: backup and

checkpoints, transaction logs, recovery via reprocessing, recovery via roll forward, recovery via

rollback, recovery from disk corruption, and automatic recovery. In order for a DBMS to be

reliable, it needs to support three types of security: account security for validation of users,

access security for protection of database objects, and operating system security for database

and file protection.

       New hardware and applications enter the market daily introducing new threats and

vulnerabilities. Until effective solutions for these new vulnerabilities exist, the vulnerabilities
                                                  Database Security Policies and Procedures for DMCS 11

are not published. “This gives users the opportunity to secure and protect their systems”

(netVigilance, 2009). History has shown that an intrusion in a system can go completely

undetected and if it is ever detected, it is often after information has been corrupted.

       Cook (Cook, 2009) described how to shorten the backup time in order to provide better

performance of an application. He listed two methods that could be helpful in lessening the

backup period: using backup data compression and backing up database to disk, which is not

used for storing the database itself or transaction log. Storing to the same disk can affect

recoverability in case the disk fails. Cook also explains the three basic backup methods SQL

Server offers: full backup, differential backup, and transaction log backup. When selecting a

backup method, three factors need to be taken into consideration: how large the data is, how

frequently it is changed, and what the nature of the data is. An advantage of SQL Server is that

it allows backups while users are active and while transactions are being processed. Cook

suggests that it is a good practice to perform full backup when the system is lightly loaded

because the backup process requires the use of resources (input/output). SQL Server also

offers the option of backing up individual files or groups of files. The author recommends

frequent testing of the backups using the Restore Verify only command.

       Yuhanna (2009) listed guidelines for developing a database security strategy. He found

it was important to understand what the applicable regulatory compliance standards are. The

regulatory requirements are to record all databases and classify them into highly sensitive,

sensitive, and non-sensitive categories; institute common policies for the databases of each

category; incorporate database security within the general security policy of the company; and

practice advanced security measures (encryption, auditing, and monitoring). Yuhanna also
                                                  Database Security Policies and Procedures for DMCS 12

provided three pillars he considered important in building a database security strategy: building

a strong foundation with authentication, authorization, and access control (AAA), classification

of databases, and patch management.          Taking preventive measures through encryption,

masking, change management, and establishing intrusion detection with auditing, monitoring,

and vulnerability assessment are key in building a strong database foundation.

       Security best practices need to address internal and external threats to database. Risk

could be diminished through the following steps: risk first has to be assessed, identified

vulnerabilities should be addressed as well, and finally real-time monitoring has to be

performed (Sortino, Database Security Standards and Audit Implementation, 2010). According

to IBM’s adaptable security solutions, the steps for achieving a desired state of information

security include definition of controls, discovering and classifying, enforcing controls,

addressing data retention, and lastly monitoring, auditing, and reporting (IBM, 2008).

       Natan (2005) listed the basic steps of implementing database security, which include

hardening of the environment, patching the database, audit the database, and defining an

access policy. Hardening of the database environment is also known as locking down, or "hack-

proofing" the database. By hardening the database environment, vulnerabilities from negligent

configuration options are removed. Natan stated that this process is based on three major

principles: limiting the access to important resources that can be intentionally or

unintentionally misused, disabling functions that are not required for the implementation, and

limiting the privileges of users. Patching is comprised of fixing or updating existing application

or database. Natan revealed that by auditing database access and activity security, issues could

be discovered and therefore quickly resolved. He also found that auditing creates a security
                                                  Database Security Policies and Procedures for DMCS 13

layer based on prevention. The author concludes that for an effective implementation of

database security, a security policy should be defined and implemented first.

       “Reaching a desired security posture that can meet business and compliance

requirements requires an enterprise-wide approach that directly maps to the business needs of

the organization” (IBM, 2008). For reaching desired information security IBM suggests the

following steps: define controls, discover and classify, enforce controls, address data retention,

and monitor, audit, and report. Defining controls means assessing strength and weaknesses of

the existing security system and comparing it with risk assessment results. Any potential

exposures should be ranked and then security policies created. Discovering and classifying data

involves finding out where data resides and who has access to it, as well as classifying sensitive

data, deciding how it is handled and secured according to compliance requirements. It is

important to enforce access controls to ensure the authenticity of all users accessing data.

Data retention relates to the volume of a company’s data, so that as the volume grows, the

requirements for storage must change to meet specific needs. For stored data to be easily

located inactive and low-value data should be moved to lower-cost storage resources. This will

provide more space for more valuable and used data. The last step of the information security

lifecycle is to monitor, audit, and report on information security activities. Monitoring data

access ensures that controls are in effect.

       Kiely (2006) explained that the encryption features SQL Server supports provide multiple

levels of server protection, which is defined as defense in depth. The technical article also

reveals that SQL Server supports a range of encryption algorithms and keys and provides secure

key management options including the option of having the user managing the keys. Kiely
                                                  Database Security Policies and Procedures for DMCS 14

affirmed that SQL Server supports a reliable encryption key hierarchy used for securing the keys

that it manages (symmetric keys, asymmetric keys, and certificates).

       In his report, Penn (Penn) recommended laptop, desktop, and full disk encryption as an

easy and cost-effective way of security management. However, he mentioned that database

encryption does not defend against SQL injections. The paper explained that in the beginning

of any encryption project, the following questions need to be answered: does data need to be

protected from authorized users, does data need to be protected from external attack, and

does all of the data need to be protected or just a single column? The author declared two

main use cases for data encryption, which determine the architectural options of the

organization. The encryption for media protection is implemented for protection from physical

or virtual theft by encrypting the entire database. Encryption for separation of duties is applied

when a single column needs to be protected. This kind of encryption requires more changes to

the database because it involves protecting data from rightful users. Penn suggested that the

encryption keys should be stored outside the database using third-party key management

products, which means that the keys are managed by a security administrator rather than the

database administrator and promotes the separation of duties. Another recommendation is

that a primary key should never be encrypted.

       A study by RSA Security (RSA) conveyed that while firewalls are effective for preventing

attackers from accessing information, they do not eliminate the internal threats, which are the

employees with authorization to access data. For securing data at rest, where firewalls are not

sufficient, data encryption is widely used. It is suggested that when planning a database

encryption strategy, the factors that need to be considered are how encryption works, how
                                                 Database Security Policies and Procedures for DMCS 15

data flows in the application, and how database security is a part of the company’s general

security policy. It is also important where the encryption is going to be performed, within or

outside of the database. The security of data depends on several things: what algorithms are

used, how they were implemented, what the key size is, where the keys are stored, and who

has access to them. The study lists the advantages and disadvantages of within database

encryption. Advantages are that applications are not affected by the encryption and data is

encrypted as soon as it is stored in the database. The disadvantages include extra processing

load, encryption keys are often stored within the database, and giving database administrators

access to the encrypted data. The study recommends the use of Hardware Security Module

(HSM), a hardware interoperating with the database, used for storage protection of encryption


        Transparent data encryption (TDE) is used in SQL Server 2008 for encrypting existing SQL

server database without having to change the application tires. TDE is a full database level

encryption that provides protection for the entire database without affecting existing

applications, processes, and database structures. Another built-in encryption feature of SQL

Server 2008 is cell-level encryption, which gives the opportunity for only sensitive database

fields to be secured. SQL Server 2008 provides database administrators with the option of

storing encryption keys and passwords to external hardware devices, or separately from the

data, extensible key management (EKM). “EKM uses the Microsoft Cryptographic API (MSCAPI)

provider for encryption and key generation. Third party vendors also provide enterprise key

management by using Hardware Security Modules or HSM.                 These HSM devices store

encryption keys on hardware or software modules that makes a database more secure because
                                                   Database Security Policies and Procedures for DMCS 16

the encryption keys do not reside with encryption data” (Pal, 2010).               The EKM can be

implemented after executing the following sp_configure command:

       sp_configure ‘show advanced’, 1




       sp_configure ‘EKM provider enabled’, 1




       Encrypting is considered the most effective technique for securing data, which at the

same time meets many regulations. Even if stolen, encrypted data is useless without the

corresponding decryption key. Protecting and managing keys, which will be discussed later in

the paper, is another challenge companies have to overcome. When encrypting data, there are

four stages of the process: securing data in motion, securing data at rest, providing access

controls, and providing data integrity controls.

       Data in motion is most effectively secured by high-speed encryption of network traffic.

“High-speed encryption fully satisfies companies’ security needs for data in motion while

meeting the requirements of multiple security mandates simultaneously” (Madison). Data at

rest resides in laptops and various portable storage devices that could be easily stolen or lost,

therefore full disk encryption is the best method for protecting this kind of data. This method

also meets multiple regulations simultaneously.             Providing access controls, or the
                                                    Database Security Policies and Procedures for DMCS 17

authentication of people to access information is based on digital identity.                Credentials

(passwords, biometrics) are attributes of the authenticated individuals, which give them access

to sensitive information. A good practice is the use of multifactor authentication, which

requires more than one credential.          Providing data integrity controls is protecting the

encryption keys, which can also be a challenge. “According to best practices, every time an

organization stores or backs up data it should encrypt the data with a different key. Very

quickly, enterprises have many keys to manage and protect.               The challenge then is not

encrypting the data, but managing and safeguarding what quickly becomes large volumes of

keys, which are subject to theft, loss, or destruction” (Asia S. , 2008).

       When securing data, for compliance reasons, the type of data that has to be secured is

important. The main categories of data are private individual data, personal health data,

financial data, military and government data, and sensitive/confidential data. For protecting

sensitive data, companies follow similar steps. First, the organization needs to be assessed and

the rules and requirements applying to it recognized. Data to be encrypted across the company

and going outside the company should be acknowledged as well as the data format. After

locating the data, there are three available options for protecting it. Eradication, or complete

removal of data, is not a viable option because of requirements for storing sensitive data.

Obfuscation requires modification of the format of the stored data, which makes it not easily

readable or accessible. Encryption is the most preferred and best option for data protection.

Finally, a reliable encryption policy that meets organizational requirements should be created.

       SQL Server 2008 supports a cryptography hierarchy using symmetric and asymmetric

keys, and digital certificates. “On the server level, there is a service master key and within the
                                                  Database Security Policies and Procedures for DMCS 18

scope of a database, there is database master key. The scope of the database master key is on

the entire database objects, certificates, and data in the database. Each database can have a

single master key” (Pal, 2010). A database master key can be created with the following


       USE master;



       ENCRYPTION BY PASSWORD = ‘36254h743p13620N4s’;


       Companies can deploy two key management approaches.                 Each one can be used

separately or they can be both used simultaneously. In the application-based approach, the

keys are stored and managed by the application itself. The library-based approach uses the

backup server for generating and managing the keys. Hardware Security Modules (HSM) are

tools that generate, store, and protect sensitive encryption keys. The advantage of using these

tools is that they offer audit options, they have a solution that is suitable for various

applications and industries, and they comply with many regulations. Two types of Hardware

Security Modules are available: public key infrastructure (PKI) and electronic funds transfer

(EFT). PKI is used for deployment and management of digital identities. A public key, which is

published, is used to encrypt the information before sending it and a private key, kept secret, is

used to decrypt it.

       A white paper issued by Imperva (2009) discusses SQL injection. It was found that in

order for the SQL injection to be exploited, detailed error messages are required. A blindfolded
                                                     Database Security Policies and Procedures for DMCS 19

SQL injection refers to the techniques that attackers use when no detailed error messages are


       Laws that companies need to comply with are:

               Sarbanes-Oxley Act (SOX) - enacted on July 30, 2002 and requires precise

                financial reporting and clear audit trials.

               Health Insurance Portability and Accountability Act (HIPAA) - enacted on August

                21, 1996, requires data security and patient privacy.

               Privacy Act of 1974 establishes guidelines for collection, use, and protection of

                personal information stored in organizations’ databases.

       Regulations exist that require companies to announce any data loss, which might have

consequences for their customers or employees.                Public disclosure is required anytime

unencrypted private data is potentially exposed. “Companies are being required to publicly

disclose breaches that put individual’s private data at risk, be it a customer, employee,

shareholder, partner, or other stakeholder” (Asia S. , 2008). Securing databases requires

protecting information in a way that satisfies specific regulations. Companies that do not

secure their data are subject to government and agencies penalties. If data loss occurs, they

are liable for customer information leaks and face massive financial loss as well as losing their


       “Database security is too often neglected or poorly managed, leading to an increased

risk of an accidental or malicious data breach” (Oltsik, 2009). The importance of information

security needs to be recognized to meet specific regulations by taking preventive measures.

”Compliance with regulations is believed to reduce some common threats to data security”
                                                   Database Security Policies and Procedures for DMCS 20

(Ponemon, 2009). In order to comply with regulations, organizations should follow certain

rules or best practices for protecting data.

       Imperva (2008) has published a paper explaining the lifecycle of data security and

compliance.    The first step listed assesses the environment by considering the following

variables: where does sensitive data reside; are the systems configured correctly; are there any

inherent security risks; and ensuring that the activities of internal and external users are

monitored. The second step, according to the article, is setting an automated mechanism for

creating and updating controls and policies. The third step of the data security, compliance

lifecycle is the enforcement of policies, auditing includes separation of duties, ensuring end

user accountability, and providing security at all levels. An auditing system should be able to

implement controls by notifying administrators about any suspicious activities in real time. The

last step is measuring the overall progress through built-it reports, security event analysis.

       ”SQL Server 2008 provides a strong integrated set of features to effectively manage user

and entity access to ePHI. These features include integration with Microsoft Windows Active

Directory domain authentication and authorization services, expanded Kerberos protocol

support, pre-defined database roles, encryption of authentication credentials over the network,

enforcement of domain password policy (on Windows 2003 servers and later), and more

granular access rules governing security principals, actions and objects…Securing and limiting

access to ePHI is a key objective of the HIPAA Security Rule” (Elliott, Zodrow, & Rozek,

Supporting HIPAA Compliance with Microsoft SQL Server 2008, 2010).

       SQL Server 2008 offers features that were not available in previous versions. It uses

policy-based management configuration for more effective management of the database. This
                                                    Database Security Policies and Procedures for DMCS 21

allows database administrators to “define standard rules or policies and enforce these rules for

configuring and managing SQL Server databases throughout the enterprise” (Pal, 2010). This

practice helps reduce the exposure of a database to security threats. SQL Server 2008 supports

channel encryption by default using SQL-generated certificates. The database engine uses

Windows group policy for password complexity, password expiration, and account lockout.

These features make the authentication more reliable. In SQL Server 2008 simple or short

passwords cannot be created, it is recommended that passwords are complex. An existing

password policy can be changed after the authentication mode is changed to mixed mode and

the following command is executed:

       ALTER LOGIN oldpassword WITH PASSWORD = ‘oldpassword’ UNLOCK, CHECK_POLICY =


       SQL Server 2008 changes the way metadata is protected. In previous versions, anyone

with access to the database was able to view metadata information. In the 2008 version, only

the owner of the metadata objects has access to it. By default, users do not have access to

metadata; however, it is possible that users are granted permissions for accessing metadata.

Another improvement in the 2008 version of SQL Server is that it offers new auditing features.

An audit object can be created with the CREATE SERVER AUDIT statement. Audit information

can be logged in a file, Windows application log, and Windows security log. An example of

creating an audit object that logs activity to a file would be:


       TO FILE ( FILEPATH=’\SQLSERVPROD_1Audit_LogTest’ );
                                                  Database Security Policies and Procedures for DMCS 22

       For updating patches to avoid security threats, SQL Server 2008 uses “Windows update

to automatically download SQL Server 2008 patches and install throughout the enterprise to

reduce threats caused by known software vulnerabilities” (Pal, 2010).

       According to Analysis Services Overview (Microsoft, Analysis Services Overview, 2007),

Analysis Services is designed to provide outstanding performance and is scalable enough to

support applications with millions of records and thousands of users depending on the version

of SQL Server installed. New, combined tools help improve developer’s productivity which

results in better design and faster implementation. In SQL Server 2008, Analysis Services

provides best practice design alerts, which creates automatic notification of possible design

problems during the development process, which in turn makes the development process

faster. Aside from the real time alerts in the development process, the entire solution design

can be inspected for alerts. Information security is based on the C.I.A. triangle (confidentiality,

integrity, availability). When databases become larger, the information needed by a user can

become hard to find as well. Database size and availability to users is managed by “eliminating

redundant storage, reducing processing costs, eliminating the synchronization requirement

between data marts and removing data consistency and integrity issues caused by storing

multiple copies of the same data” (Microsoft, Analysis Services Overview, 2007). Analysis

Services stores data in a compressed format known as Multidimensional OLAP (MOLAP); in a

relational database, Relational OLAP (ROLAP); or in a mixed form, known as Hybrid OLAP

(HOLAP). Multidimensional OLAP offers a higher-level performance compared to the other two

formats, providing users with fast access to high volume of pre-aggregate data.
                                                Database Security Policies and Procedures for DMCS 23

       SQL Server 2008 has its own specific security best practices.           Rules for creating

passwords state that they should be complex and have expiration policies.                Therefore,

passwords should be changed after the first log in. The number of administrators should be

kept minimal and administrator privileges should only be used when needed. A company

should always keep track of who has access to corporate data. Owners of databases need to be

as few as possible, and they need to be distinct. A good practice is to have the Cross-Database

Ownership Chaining option turned off unless multiple databases are being used as a single unit.

Best practices for using schemas require the grouping of similar objects, granting ownership

and permissions for database object protection. The number of owners of schemas should be

minimal and each owner should be distinct. Granting permissions to database object should be

done by implementing the least privilege principle, keeping guest access disabled, managing

permissions through database roles, and keeping access within modules. Information about

databases is stored in catalogs. It is imperative that these catalogs are protected, which

according to SQL Server 2008 security best practices must be secured by default. In order for a

database to be kept secure when practicing remote data execution, remote servers should be

replaced with linked servers and constrained delegation should be used when pass-through

authentication to a linked server is necessary. Execution context best practices suggest the

execution context on modules should be set unambiguously instead of leaving it at default

(EXECUTE AS should be used instead of SETUSER). EXECUTE AS options are caller, owner,

creator, and any user with a valid username. Data encryption in SQL Server 2008 should be

done according to following rules: sensitive and high-value data should be encrypted using

symmetric keys, symmetric keys should be protected using asymmetric keys or certificates, keys
                                                Database Security Policies and Procedures for DMCS 24

should be protected with passwords, and master keys should be removed from the system.

Service master keys, database master keys, and certificates need to be backed up through key-

specific Data Definition Language (DDL) statements. Symmetric and asymmetric keys should be

backed up by taking a backup of the database.           The SQL Server 2008 best practices

requirements for auditing recommend auditing of unsuccessful as well as successful logins

when storing highly sensitive data, using Windows Management Instrumentation (WMI) for

receiving emergency events alert, and using trace events for auditing Data Manipulation

Language (DML). Rules for patching SQL Server suggest enabling automatic updates.

       “Security is a crucial part of any mission-critical application” (Beauchemin, SQL Server

2005 Security Best Practicies - Operational and Administrative Tasks, 2007). Implementing

security features for the Disaster Management Communication System based on the SQL Server

2008 policies makes it efficient and reliable. The DMCS is designed to provide communication

means and preparedness plans in case of natural disasters. It should provide access to data

that is often sensitive or personal. In order for the system to work effectively and to be in

compliance with government regulations, information security should is implemented through

“enforcing information integrity and shielding data from being tampered with by unauthorized

persons, being modified accidentally by employees, or losing consistency because of the

incorrect coding of business requirements and rules” (Afyouni, Database Security and Auditing,

2005). Information security should be focused on protecting the confidentiality, integrity, and

availability of data. The database of the Disaster Management Communication System is

secured through encryption according to the SQL Server 2008 guidelines.
                                                 Database Security Policies and Procedures for DMCS 25

       This research is focused on SQL Server and Windows OS because this is the

environment, in which the system is currently built. The DMCS has been converted from Oracle

to SQL Server, which required a redesign of the system. Testing new platforms such as MySQL

or Access would not be in best interest of the developers.


       After conducting research about database security, particularly topics related to threats,

best practices, procedures, and agency regulations, the obtained information was used for

preparing the database security manual included in appendix A. The studied procedures were

applied to the Disaster Management Communication System to provide a secure and reliable

environment. After implementing the security features, supported by SQL Server 2008, the

system meets the regulatory requirements of HIPAA, SOX, and the Privacy Act of 1974. By

securing the system and the database, client information is protected from theft and

unintended exposure, especially to unauthorized entities.

       The main procedures of implementing security for the Disaster Management

Communication System are as follows:

       1.     Researching and producing the Database Security Policies and Procedures


       2.     Verify the physical security of the server that is going to host the SQL Server


       3.     Update Windows operating system for security, performance, and reliability

                                                  Database Security Policies and Procedures for DMCS 26

       4.        Install SQL Server 2008 remotely through and implement

   security for the system. The installation is documented in detail in appendix B.

       5.        Logging in remotely ensures the server is properly running and SQL Server 2008

   is working.

       Information is classified and divided into levels: confidential, restricted, internal, and

public. Access to these levels of information is granted to users and employees according to

their position and job requirements, applying the least privilege rule.

       The operating system was updated with the latest patches. Service packs and system

logging was enabled for process tracking and recovery purposes, also the Activity Monitor in

SQL Server is used for screening processes and actions performed on the server.

       Windows Integrated logins are used for providing users with access to the system and

passwords requirements and account lockout policy are established.

       Ensuring application security through assigning database roles to users to give them the

right privileges and to limit access. The length of all fields in the .aspx pages were limited to

reduce the possibility of sql injections occurring. Date format is verified using the isdate()

function. Hypertext Transfer Protocol Secure (HTTPS) is used for authorization and secure

transactions.    Automatic audits should be performed to produce reports for the system

integrity evaluation. Audits should track any changes made to the database.

       The mentioned security features for the DMCS are adequate and sufficient for the

moment. The security will need to be revised and updated in the future, as new technologies

and respectively new threats become available.
                                                                               Database Security Policies and Procedures for DMCS 27

                                                             Time Action Plan

    Task                                                                  Duration of Task
 and manual
Windows OS

  Install SQL
 Server 2008


Final Review
                Week 1

                         Week 2

                                  Week 3

                                           Week 4

                                                    Week 5

                                                             Week 6

                                                                      Week 7

                                                                                 Week 8

                                                                                          Week 9

                                                                                                   Week 10

                                                                                                             Week 11

                                                                                                                       Week 12

                                                                                                                                 Week 13

                                                                                                                                           Week 14

                                                                                                                                                     Week 15

                                                                                                                                                               Week 16

        Databases attacks could result in exposure of personal information, significant financial

loss, reputation damage, and legal suits. In order for these consequences to be avoided,

companies should have information security policies and procedures designed in compliance

with government regulations. Database security should meet specific requirements and assure

information protection and availability, which is a challenging task. There are existing manuals

with important steps and guidelines for database security implementation. The SQL Server

2008 software package provides features for generating data encryption and key management.

Encryption of data is recognized to be an effective way of protecting sensitive information while
                                               Database Security Policies and Procedures for DMCS 28

meeting various regulations. The product of this research is the Database Security Policy and

Procedure Manual, which includes a walkthrough of how security should be implemented for

the DMCS.
                                                   Database Security Policies and Procedures for DMCS 29


Achieving end-to-end information security: five critical steps. (2008, August). Retrieved April 17,

       2010, from


Afyouni, H. A. (2005). Database Security and Auditing.

Analysis Services Overview. (2007). Retrieved November 15, 2010, from microsoft:

Asia, S. (2008, October 31). An enterprise strategy for data encryption and key management.

       Retrieved September 17, 2010

Banister, D. (n.d.). Regular Expressions Make Pattern Matching And Data Extraction Easier.

       Retrieved March 7, 2011, from MSDN Magazine:


Beauchemin, B. (2007, March). SQL Server 2005 Security Best Practicies - Operational and

       Administrative Tasks. Retrieved April 30, 2010, from

Beauchemin, B., Berglund, N., & Sullivan, D. (2005, June 28). SQL Server password policies and

       credentials. Retrieved June 06, 2010, from searchsqlserver:


Byfield, B. (2005, November 22). Nine principles of security architecture. Retrieved May 20,

       2010, from
                                                 Database Security Policies and Procedures for DMCS 30

Chigrik, A. (2003, August 13). Managing Users Permissions on SQL Server. Retrieved February

       17, 2011, from Database Journal:


Cook, R. (2009). SQL Server data backup and recovery best practices. Retrieved May 04, 2010,



Database Security, Virtualization and Cloud Computing. (2010). Retrieved May 04, 2010, from

CDW. Disaster Recovery: Plan for the Worst, Expect the Best. Retrieved May 04, 2010, from


Elliott, D., Zodrow, C., & Rozek, P. (2010). Supporting HIPAA Compliance with Microsoft SQL

       Server 2008. Retrieved November 13, 2010, from

Elliott, D., Zodrow, C., & Rozek, P. (2010). Supporting HIPAA Compliance with Microsoft SQL

       Server 2008. Retrieved November 13, 2010, from
                                                  Database Security Policies and Procedures for DMCS 31

Fogie, S. (2004, January 01). Security Reference Guide. Retrieved May 25, 2010, from informIT:

GLB Compliance - Gramm-Leach Bliley Act. (2006). Retrieved March 31, 2011, from safetysend:

Health Information Privacy. Retrieved March 31, 2011, from hhs:

Huey, P. (2007, July). 7 Auditing Database Activity. Retrieved July 07, 2010, from


IBM. (2008, August). Achieving end-to-end information security: five critical steps. Retrieved

       April 17, 2010, from


Imperva Data Security and Compliance Lifecycle. (2008). Retrieved May 04, 2010, from

Kiely, D. (2006, December). Protect Sensitive Data Using Encryption in SQL Server 2005.

       Retrieved May 04, 2010, from

Litchfield, D., Anley, C., Heasman, J., & Grindlay, B. (2005). The Database Hacker's Handbook:

       Defending Database Servers. Wiley.

Louvel, S. (2004, October). Customer Data Security: Bulletproofing the Boundaries. Retrieved

       April 27, 2010, from
                                                 Database Security Policies and Procedures for DMCS 32

Madison. 4 Steps to Data Security Compliance. Retrieved April 27, 2010, from


Melomed, E. (2006, February 01). Configuring the Analysis Services Query Log. Retrieved

       February 17, 2011, from Microsoft:


Microsoft. (2007). Analysis Services Overview. Retrieved November 15, 2010, from microsoft:

Microsoft. (2005, August 22). IIS 6.0 Operations Guide. Retrieved February 16, 2011, from

       Microsoft TechNet:

Microsoft. Integrated Windows Authentication. Retrieved February 17, 2011, from Microsoft :


Natan, R. B. (2005, May 02). Essential Steps to Implementing Database Security and Auditing.

       Retrieved May 04, 2010, from

Oltsik, J. (2009, October). Databases at Risk. Retrieved February 20, 2010, from

                                                  Database Security Policies and Procedures for DMCS 33

Page, R. (2007, February 20). SQL Server Security Cribsheet. Retrieved June 05, 2010, from



Pal, T. (2010, August 27). Top 10 SQL Server 2008 Security Features. Retrieved November 13,

       2010, from Database Journal:


Penn, J. Expert Analyst Reviews the Ins and Outs of Database Encryption. Retrieved May 04,

       2010, from


Ponemon, L. (2009, November 30). The State of Privacy & Data Security Compliance. Retrieved

       April 17, 2010, from


Proactively Reduce Risk and Improve IT Security in Physical and Virtual Environments. (n.d.).

       Retrieved April 18, 2010, from


Redman, M. (2008, November). SQL Server Best Practices – Implementation of Database Object

       Schemas. Retrieved June 03, 2010, from msdn:

                                                 Database Security Policies and Procedures for DMCS 34

Riccardi, G. (2001). Principles of Database Systems with Internet and Java Applications. Addison


RSA. Securing Data at Rest: Developing a Database Encryption Strategy. Retrieved May 04,

       2010, from

Securing data at rest. Retrieved April 27, 2010, from


Shackleford, D. (2007, November). Regulations and Standards: Where Encryption Applies.

       Retrieved April 27, 2010, from

Sortino, J. Database Security Standards and Audit Implementation. Retrieved May 04, 2010,

       from http://www.isaca-


Sortino, J. (2010, June 25). Database Security Standards and Audit Implementation. Retrieved

       May 04, 2010, from http://www.isaca-


Thales. Database Security for Microsoft SQL Server 2008 . (2009). Retrieved November 13, 2010,

       from asiapeak:
                                                Database Security Policies and Procedures for DMCS 35


The Anatomy of an Insider: Bad Guys Don’t Always Wear Black . (2009). Retrieved May 04,

       2010, from

Thomas, J., & Catanzano, J. (2005, November). SAP with Microsoft SQL Server 2005: Best

       Practices for High Availability, Maximum Performance, and Scalability. Retrieved May

       04, 2010, from

Yuhanna, N. (2009, September 28). Your Enterprise Database Security Strategy 2010. Retrieved

       May 04, 2010, from

                                                  Database Security Policies and Procedures for DMCS 36

                                           Appendix A


       Disaster Management Communication System requires information security that is

going to maintain it reliable and available while meeting various government regulations. The

Health Insurance Portability and Accountability Act (HIPAA) offers federal protection for

personal health information own by medical service providers and it gives patients rights

related to that information. HIPAA Privacy Rule controls the disclosure and use of Protected

Health Information (PHI), which is essentially individual’s health status, health care provision,

and payment for health care. For the Disaster Management Communication System to comply

with the HIPAA Privacy Rule it needs to:

          Release PHI within thirty days of individual’s request, or when required by law;

          Whenever PHI is requested by other organizations, DMCS must first obtain the

           individual’s consent and disclose as little information as possible;

          When an individual requests correction of PHI, DMCS needs to make sure that the

           requested changes are reasonable, and if PHI is found to be incorrect it needs to be


          DMCS needs to make communications with individuals confidential;

          DMCS must inform individuals whenever their PHI is used;

          Documentation of PHI disclosures need to be made;
                                                Database Security Policies and Procedures for DMCS 37

      Available SQL Server 2008 security features for meeting HIPAA technical safeguards

             Hipaa 45 cfr §164 .312                       SQL Server 2008 Features
§164.312(a)(1)                                  Windows/Active Directory & SQL Server
Access Control Standard                         Authentication and Authorization
§164.312(a)(2)(i) Unique User Identification    Windows/Active Directory & SQL Server
Specification – Required                        Authentication
                                                Emergency Access Policy, Procedures and
Emergency Access Procedure Specification –
                                                Pre-Staged Accounts; SQL Server Audit
§ 164.312(a)(2)(iii) Automatic Logoff           Windows/Active Directory Group Policy
Specification – Addressable                     Enforcement
§ 164.312(a)(2)(i) Encryption and Decryption
                                                EKM; Cell-level Encryption
Specification – Addressable
                                                Windows and SQL Server Authentication;
§ 164.312(d) Person or Entity Authentication
                                                Domain Password Policy Enforcement;
                                                Transport Layer Security (TLS\SSL)
§ 164.312(b) Audit Controls Standard            SQL Server Audit; Policy-Based Management
                                                Database and Application Development
                                                Standards and Guidelines;
§ 164.312(c)(1)
                                                Constraints, Triggers and Referential Integrity;
Integrity Standard
                                                Database Performance Collection;
                                                Database Backup
§ 164.312(e)(1)
Transmission Security Standard

§ 164.312(e)(2)(i) Integrity Controls           Transport Layer Security (TLS\SSL)
Specification – Addressable
§ 164.312(e)(2)(ii)
Encryption Specification – Addressable

       Sarbanes-Oxley Act (SOX) requires that certain financial risks be addressed, precise

financial reporting, and clear audit trials.   To comply with SOX, Disaster Management

Communication System needs to recognize what components of the system play crucial parts in

the financial reporting process. These components include databases, networks, and operating

systems. Regular assessments need to be performed to verify that the processed information is
                                                   Database Security Policies and Procedures for DMCS 38

complete and accurate. Security and privacy of data can be confirmed through the following


          Authentication – processes performed by the system to provide authentication of


          Authorization – processes that guarantee the system is accessed only by approved


          Validity – processes that ensure only valid data is being processed.

          Identification – processes that verify all users are uniquely identified.

          Completeness – processes that ensure all records are processed from beginning to


       Available SQL Server 2008 security features for meeting SOX technical safeguards

              SOX requirements                                SQL Server 2008 Features
Section 302 Corporate Responsibility for
                                                   Report Builder
Financial Reports
Section 404 Management Assessment of
                                                   SQL Server Authentication and Authorization;
Internal Controls
                                                   Report Builder
Section 409 Real Time Issuer Disclosures
                                                   Activity Monitor; SQL Server Audit
Section 902 Attempts and Conspiracies to
Commit Fraudulent Offenses                         SQL Server Authentication and Authorization

       The Privacy Act of 1974 establishes guidelines for collection, use, and protection of

personal information stored in organizations’ databases. According to the Privacy Act, it is

illegal to disclose personal information without the written consent of the individual. The
                                                   Database Security Policies and Procedures for DMCS 39

Privacy Act allows individuals to access their records. In order for the Disaster Management

Communication System to meet the requirements of the Privacy Act it must:

          Allow individuals to access their personal information.

          Allow individuals to request corrections to their information.

       An important legislation that the Disaster Management Communication System needs

to conform to requires reporting to the government of any security breaches. When these

breaches are of significant magnitude, they have to be announced to the public as well. The

government has introduced this legislation with the hope that companies will increase their

security standards to avoid financial and reputation loss. In the case of security violation when

clients’ personal information has been exposed, DMCS has to report the incident to the

government and the public.

                                   Part I Security Architecture

       Information systems are an integral part of modern businesses, and in order for them to

be effective and reliable, they need to be equipped with security features. They need to

protect data entry as well as the produced information. The three basic factors information

security is based on are confidentiality, integrity, and availability (the C.I.A. triangle). Security

architecture is closely related with the C.I.A. triangle and its implementation in companies. The

components of security architecture vary from physical equipment to logical security tools.

       The purpose of information security architecture is to protect logical and physical assets

through ensuring that confidentiality, integrity, and availability of data are not breached.

Confidentiality refers to the prevention of unauthorized individuals from accessing confidential
                                                 Database Security Policies and Procedures for DMCS 40

information; confidential information is protected through classification of information, which

makes it available to authorized individuals only. Information needs to be properly classified in

levels according to the type of business and government agencies, and each level needs to have

specific security measures. Most often information is classified according to the degree of

confidentiality necessary to protect the information.

       Integrity requires that data at all levels throughout the system is consistent and

accurate, and have not been intentionally or accidentally tampered with. Data integrity is

considered to be violated when at least one of the following is present: invalid data, redundant

data, inconsistent data, data anomalies, data read inconsistency, or data nonoccurrence.

       Availability of an information system is the notion that the system should be accessible

to authorized individuals, and the system determines what the individuals are allowed to do

with the accessed information. The availability of an information system can be affected by the

following factors: external attacks and lack of system protection, a system failure without a

disaster recovery strategy available, extremely restrictive security policies and procedures, or

improper authentication of customers.

       Adhering to the C.I.A. triangle is important for the Disaster Management

Communication System (DMCS) in order to keep it secure and reliable for its clients and

employees. Confidentiality of information makes clients and employees feel confident that

their personal information will not be exposed to any unauthorized entities. For the system to

run flawlessly its data needs to be consistent, accurate and the data integrity needs to be

protected. Because DMCS is accessed by multiple users simultaneously, the concurrency of the

database is increased which in turn can cause problems when data is being changed. In SQL
                                                              Database Security Policies and Procedures for DMCS 41

Server, locks are used to protect the data and control how numerous users access and alter

shared data without conflicting with each other. Major concurrency problem is the loss of

updates resulting in invalid data and unexpected query results. Locking prevents multiple users

from making changes to the data at the same time. Exclusive (X) locks are used for INSERT,

UPDATE, and DELETE operations and by preventing access to a resource by concurrent

processes exclusive locks ensure that multiple changes are not made to the same resource at

one time. When exclusive locks are used, data modification statements combine modification

and read operations. Read operations are performed to acquire data before performing the

modification operations. Lastly, in order for the Disaster Management Communication System

to be completely effective, it should be available and ready for utilization by any authorized

user at any time.

        Information security architecture protects logical and physical assets by ensuring that

the C.I.A. triangle is intact. Figure 1-1 illustrates the C.I.A. triangle.

         - Data and information is                                             - System is available at all
         classifies into different levels                                      times only for authorized users
         of confidentiality to ensure that                                     and authenticated persons.
                                           Confidentiality      Availability   - System is protected from
         only authorized users access
         the information.                               security               being shut down due to external

                                             - Data and information is
                                             accurate and protected from
                                             tampering by unauthorized
                                             - Data and information is

                                     Figure 1-1: Information security C.I.A. triangle

        The C.I.A. triangle can be kept intact by utilizing the following components: policies and

procedures, security personnel and administrators, detection equipment, security programs,
                                                  Database Security Policies and Procedures for DMCS 42

monitoring equipment, monitoring applications, and auditing procedures and tools. Security

architecture needs to enforce security features at all levels of the database. It is essential that

all security access points are recognized and addressed in the security implementation in order

for important data to be protected. At all security access points database security must be

implemented, enforced, and audited to prevent unauthorized actions. Database security access

points include people, applications, network, operating system, database management system,

data files, and data. Figure 1-2 illustrates database security access points. The implementation

of the security features for the access points needs to start from people and follow the order

listed above. The reason is that the people component covers the largest area, respectively

covering the number of individuals accessing the database (managers, users, administrators,

developers, visitors) and representing the greatest possibility of risk. When security access

points are not protected, a security gap exists. Security gaps open vulnerabilities to the system.

These vulnerabilities need to be closely monitored because they are potential threats for the

system. Threats are security risks that often become a system breach, leading to violation of

data integrity.
                                                    Database Security Policies and Procedures for DMCS 43




                                         Operating System


                                              Data Files


                                          Database Security

                              Figure 1-2: Database security access points

       The build of efficient information security architecture requires the following:

              understanding the nature of threats

              setting up safeguards that adequately prevent loss, theft, destruction,

               corruption, tampering, copying, deletion, modification of data and information

              response to incidents in a way that reduces their impact

              assessing the damage, identifying the source of breach, and correcting it

              recover as quickly as possible, evaluating the breach, reviewing and updating

               information security safeguards

       Security architecture prevents security breaches, minimizes their effect them when they

occur, and helps systems recover from intrusions. Each organization has its own unique

business context, risk analysis, mitigation, and security policies but there are nine basic

principles of security architecture that apply to most of them.

           1. Set up a security policy for the system.
                                         Database Security Policies and Procedures for DMCS 44

   You need to know what is on your system in details so you know what needs to

   be secured. This requires removing existing programs that conflict with the

   security policy, and removing the default choices when installing programs.

   Instead, rules that are more precise should be set up to control access to and use

   of data.

2. Actions should be able to be verified.

   Verifying an action means to make sure that it is carried out because of a certain

   block of source code and unexpected actions are not taking place. This provides

   transparency to the system so it is clear what exactly is being done, and what

   files are downloaded.

3. Utilize the least privilege policy.

   This principle suggests that every user, process, and program should be given

   only the access of system resources that are necessary. Responsibilities and

   privileges should be assigned and in case users need greater access, they should

   be allowed to have it for as little as possible. “Least privilege is one of the

   reasons why, ideally, users should be added to groups only as necessary, rather

   than being automatically added to a number of common ones,” (Byfield, 2005).

4. Practice defense in depth.

   Security should be enforced on different levels in order to provide a safer

   environment. Although firewalls are often sufficient security precautions of

   systems, when the firewall is breached the whole system is at risk. This is why
                                       Database Security Policies and Procedures for DMCS 45

   security at different levels, or in depth defense, is important for protecting the

   entire system.

5. Auditing the system

   Monitoring and recording system changes are imperative for keeping the system

   secure.    Keeping track of these changes can tell us when the system is

   compromised. Changes can be recognized by the system’s own tools or by

   intrusion detection systems.

6. Build the security to contain intrusions.

   The benefit of designing a system security to contain intrusions is that it limits

   the access of hackers to the system; and when an intrusion occurs, it minimizes

   the consequences for the system. Another advantage of containment is that it

   makes it safe to run an untested program in isolation.

7. The more defenses the better.

   Similar to defense in depth, this principle suggests that the more defenses the

   system has, the more protected it is. Since users are the component of the

   system that presents the highest level of risk, it is important that they are

   familiar with the basic security practices. They need to follow these practices


8. Secure systems as a preventive measure.

   Securing a system after an attack has already occurred will protect it from future

   attacks but it is not going to erase the consequences of previous ones. After a
                                                 Database Security Policies and Procedures for DMCS 46

              system has been attacked, the steps that need to be taken to make it secure are

              reinstall the basic input/output system (BIOS); reformat the hard drive; restore

              files from a backup taken before the system was breached.                While these

              processes are running, the system is not available. To avoid the lengthy process

              of restoring the system, it is a good practice to implement all security principles

              from the beginning.

          9. Practice full disclosure.

              Announcing to users when your system has been attacked is a requirement of

              the Security breach notification law. This gives users the opportunity to take

              their own precautions, for example logging off the system until the vulnerability

              has been handled.

       Applying the principles of security architecture and balancing them to meet users’

requirements and needs, while making sure agency regulations are followed, is a challenging

task. Having proactive security architecture not only minimizes the risk of attacks but it also

makes recovery from eventual attacks easier.

                Disaster Management Communication System Requirements

       Confidentiality provides assurance that information is shared only among authorized

individuals or organizations. Violation of information confidentiality can occur when data is

handled by unauthorized users. The classification of the information should determine its level

of confidentiality and the appropriate safeguards. Authorized users within the DMCS are
                                                  Database Security Policies and Procedures for DMCS 47

individuals (employees, clients) who have been granted access to its information and resources.

Assigning passwords and setting server permissions can help prevent unauthorized users from

accessing the system.

       In order to provide the appropriate degree of information confidentiality for the DMCS,

information needs to be separated and classified as follows:

              Restricted – client personal information, such as social security numbers, medical


              Confidential – client general information including names, address, telephone

               number, date of birth

              Internal – information used within the system such as telephone numbers,

               schedules, maps.

              Public – published phone numbers, addresses, and other general information.

       Information classified as restricted should be available only to certain employees whose

jobs require it and following the least privilege practice. Access to this type of information can

be granted in exceptional cases. An access control list with the names of all employees having

access to restricted information should be maintained.

       Confidential information should be available to employees who require it for their job

functions and to other authorized nonemployees with a nondisclosure agreement. Access to

this type of information can also be granted on a need-to-know basis and an access list should

be kept.
                                                    Database Security Policies and Procedures for DMCS 48

        Internal information is made available to all employees and some nonemployees

(customers, suppliers, volunteers).      Public information is available to the public with no


        Data integrity can be compromised because of physical hardware defects, hardware

design errors, system (software) design errors, and data communications and transfer. For

defense against hardware, software, or communication systems failures, the DMCS need to be

supplied with an uninterruptible power source (UPS) and offline data backup program. Safety

measures that significantly reduce the chances of data integrity breaches and need to be

applied to the DMCS are as follows:

               On a regular basis, back up data and other software resources, as well as store

                current copies at a secure off-site location.

               Back up data at intervals determined by the length of the recovery progress.

               Avoid using any software not originating from a trusted source.

               Enable auto-save features in system software and utilities.

               Use up-to-date virus protection software.

               Implement and maintain auditing/detection tools for detecting and reporting

                changes to mission critical system files.

               Always have a properly maintained UPS and power-conditioning equipment.

        Applying the nine basic principles of security architecture to the DMCS to prevent

security breaches and minimize the consequences when a breach occurs.
                                        Database Security Policies and Procedures for DMCS 49

1. Security policy adequate for the information on the system needs to be set up,

   that means taking into consideration the fact that restricted information is being

   stored which needs to be properly protected by assigning strong access

   passwords to authorized users only. Access and modification of data should be

   limited and monitored.

2. To verify that all actions are a result of a planned or intentionally executed

   source code, logs need to be kept for keeping track of all transactions within the


3. Applying the least privilege policy to the DMCS would grant access to

   confidential information only to authorized users, which minimizes the risk of

   data integrity breaches.

4. For the DMCS, defense in depth should encompass physical security,

   authentication and password security, logging, auditing, firewalls, antivirus

   software. All of these components need to be up-to-date in order to provide

   effective security for the system.

5. The system needs to be audited on a regular basis to detect any changes and

   eventual breaches. SQL Server 2008 provides a built-in auditing feature that

   stores all audit logs. With this feature, the system tracks who has accessed, or

   has made an attempt to access the data, which can help detecting unauthorized

                                                 Database Security Policies and Procedures for DMCS 50

           6. Containing the intrusion means that if the attack is not major, for example denial

              of service, the system does not require to be shut down. Instead, the intrusion

              should be prevented from spreading. When the attack is compromising security

              of the system, it needs to be quickly shut down.

           7. All employees and clients of the DMCS, who have access to the system, need to

              be familiarized with the established security practices and follow them strictly.

           8. In the case of a security breach, the DMCS needs to have its files restored from a

              backup made prior to the attack, the basic input/output system (BIOS) needs to

              be reinstalled, and the hard drive needs to be reformatted.

           9. Whenever a system attack occurs, it needs to be disclosed to the public. The

              DMCS stores customers information and therefore is required by law to inform

              them of any possible threats to their accounts. System administrators should be

              responsible for the announcement of any system attacks.

       Figure 1-3 represents a breakdown of the database security access points for the DMCS

that require the implementation of individual security features.
                                                                   Database Security Policies and Procedures for DMCS 51

                     SA, developers, data entry employees, clients, suppliers, volunteers

                                     Data collection and reporting screens

                                Method of connection to the reporting screens

                                       Microsoft Windows Server 2003

                                                SQL Server 2008


            Files storing information about clients, transactions, products, orders, disaster specifics

                                                     Operating System

                               Information processes and/or stored in the system
                                                         Data Files


                                               Database Security
                                                     Database Security


                           Figure 1-3: Database security access points for the DMCS

                         Part II Operating System Security Fundamentals

       The operating system controls the resources of the computer such as memory, devices,

and processing time. Therefore, operating system security is important for the protection of

the database and integrity of stored data. A compromised operating system security opens

possibilities for attacks on the database because databases reside on technology managed by

operating systems. One of the purposes of an operating system is to provide functionality for

enforcing security measures such as authorization and authentication, user administration,

password policies, and e-mail security. The components of an operating system (services, files,

and memory), serve as access points to the database, which means they have to be configured

properly and secured to prevent security threats. Figure 2-1 represents the layers of an

operating system.
                                                   Database Security Policies and Procedures for DMCS 52

                                        Operating System

                                          Memory layer

                                          Services layer

                                            Files layer

                          Figure 2-1: Operating system security environment

       The operating system is the middle level of a computer system; it connects the

computer hardware with the computer software, making it possible for a variety of tasks and

problems to be solved. Without the operating system, the computer hardware is not usable.

The operating system provides users with an interface that makes the hardware usable through

various applications and tools. Some of the functions of the operating system are managing

computer resources, enforcing security measures, controlling activities flow, multitasking,

multisharing, and managing user actions and accounts.

             Managing computer resources, such as memory, input and output devices, the

              central processing unit, and disk storage

             Enforcing security measures through built-in features and tools

             Controlling flow of activities by keeping track of what actions take place in the

              system; providing timestamps

             Multitasking – executing multiple tasks simultaneously

             Multisharing – providing the opportunity for multiple users to use the hardware

              at the same time
                                                  Database Security Policies and Procedures for DMCS 53

              Managing user actions and accounts – controlling users’ actions on the system;

               setting up permissions and access controls for users

       The security of the operating system depends on the three components mentioned in

figure 2-1. The more secure these components are the more secure the operating system will

be. These components need to be properly configured to ensure a secure environment for the

system. The services component is essentially the functions of the operating system, which

users use to access the operating system and to be able to perform any tasks. Because these

services help for the authentication of users and manage password policies and user

administration, they need to be properly secured, or they become vulnerabilities and possibly

security threats. File transfer and file sharing also represent a common threat to the security of

the system. Because files are data carriers, they need to be adequately secured to prevent the

possibility of data loss or privacy violation. The granting of read, write, and execute privileges

to users, needs to be thoughtfully performed. In Windows, the operating system in which the

Disaster Management Communication System is built, file permissions can be changed in the

following way: right-click on a file; click on Properties, then click on the Security tab. This

window allows us to grant (Allow) or revoke (Deny) privileges to users.
                                                     Database Security Policies and Procedures for DMCS 54

                                     Allow/deny privileges to users

       Best practices for transferring files suggest:

          1. When possible, use the Secure FTP utility instead of the normal FTP utility.

          2. Create two directories for FTP – the first one with write permission only for

              uploading files and the second with read permission only for downloading files.

          3. Create unique accounts for FTP that do not have access to any directories or file

              outside the upload and download directories.

          4. Turn on logging to scan FTP logs for suspicious or unusual activities.

          5. Grant FTP privileges only to authorized users.

       Memory, as a component of the operating system, can also be an access point for

security violations. Through malfunction of poorly written programs, the content of memory

can be harmed and data integrity violated. In the event of data being damaged by a certain

program, that program should not be used any more, or it should be patched.
                                                 Database Security Policies and Procedures for DMCS 55

       An essential feature of the operating system is that it provides authentication of users,

which “is a process that verifies the identity of the user to permit access to the operating

system. A weak authentication method exposes the system to security risks and threats,”

(Afyouni, Database Security and Auditing, 2005, p. 45). There are two types of authentication

methods available: physical and digital. The physical authentication method allows or denies

physical access to the company premises. This is most often done with magnetic cards and card

readers. When a higher level of security is required, biometric or biomagnetic technologies are

used for authentication of employees (fingerprint scans, voice recognition, and signature

recognition). The digital authentication method uses digital devices or software for verifying

employees’ identity. Some of the devices used in this kind of authentication are digital

certificates, digital cards, lightweight directory access protocol (LDAP), remote authentication

dial-in user services (RADIUS), secure remote password (SRP).

       After the authentication process, or the user has been recognized and granted access to

the system, the purpose of the authorization process is to control users’ actions according to

their privileges and rights.

       User administration is a feature of the operating system that allows administrators to

create user accounts, set password policies, and grant privileges to users. User administration

has to be properly implemented to avoid security risks and threats. Best practices for user

administration that are applied for the DMCS include:

           1. Use a consistent naming method that includes a combination of first and last

               name of the user.
                                                   Database Security Policies and Procedures for DMCS 56

           2. Do not use default passwords and force users to change their password at the

              first logon, as well as inform users how to select a strong password, according to

              the company’s password policy (explained in detail later in the paper).

           3. All passwords need to be encrypted and saved in a well-secured file.

           4. When a machine is compromised, change all passwords for all accounts.

           5. Lock accounts of users when their employment is ended, and accounts that are

              not used for a certain period.

           6. Grant privileges to machines only to users that need it.

           7. When connecting remotely, use Secure Shell (ssh) for telneting, Secure Copy

              (scp) for file copying, and Secure FTP for file transfer.

           8. When a system is compromised, isolate it from other systems to avoid intrusion.

           9. Practice random auditing procedures daily.

       The system administrator along with the security manager is responsible for creating a

password policy that fits within the company’s mission and is enforced at all levels of the

organization. Best practices for password policies suggest:

           1. Password aging – determines how many days one password can be in effect

              before it has to be changed, usually three months.

           2. Password reuse – determines how many times one password can be reused;

              after how many days it can be reused whether it can be reused at all.

           3. Password history – how many passwords should be kept in record for one


           4. Password encryption – encryption and storage of passwords
                                                  Database Security Policies and Procedures for DMCS 57

           5. Password storage – where passwords are stored and protected

           6. Password complexity – determines the minimum length and combination of

               lowercase and uppercase letters, digits, and symbols. The password should not

               include first or last name of the user, their city, spouses’, children’s, or pets’

               names, telephone number, license number.

           7. Logon retries – limit the number of unsuccessful logons before an account is

               locked, usually three.

           8. Password protection – employees should be educated on how to protect their

               passwords and the danger of revealing them.           If a password needs to be

               recorded, it should be encrypted so only its owner can access it.

           9. Single sign-on – allows using numerous servers after having logged in to one.

               This should not be performed in projects with critical missions, and in

               government or financial institutions.

       User administration and password policies are only a part of securing an operating

system. The major steps in the security for the different operating systems are essentially the

same. What distinguish operating systems are the different features and options they provide,

such as security enforcement, account management, and activities control.                 For a safe

environment to be built, the following rules need to be followed:

              Install the latest system patches. Windows needs to be updated to install the

               latest patches, which can be done by selecting Windows Update from the Start

               menu. Automatic updates should be enabled for the operating system, and for
                                       Database Security Policies and Procedures for DMCS 58

    any other applications that allow it: Start Control Panel  System 

    Automatic Updates Keep my computer up to date option should be selected.

   Verify user account security. All guest accounts should be disabled if possible.

    All accounts need to have passwords that meet the requirements of the

    password policy.      Most users should have Limited account type, and

    administrative privileges should be limited.

   Remove all unnecessary applications and network services.              Some services

    including file sharing should be disabled by default.         Disabling of Network

    Dynamic Data Exchange is performed by Start button Settings Control Panel

    Administrative Tools Services change Startup type to Disabled instead of


   Windows Simple File Sharing should not be used because it shares files

    anonymously without any security of a user account. To disable it: Start button

     Control Panel Folder Options View  Advanced Settings unselect Use

    Simple File Sharing  Apply.

   Install necessary applications and network services. Anti-virus software should

    be installed on each machine.       Enable system logging for troubleshooting,

    recovery, and activities tracking. Windows has logging disabled by default, but it

    can be enabled by Start  Settings  Control Panel  Administrative Policy 

    Local Security Settings  Local Policies  Audit Policy.
                                                  Database Security Policies and Procedures for DMCS 59

              Install and use Security Self-Test. This is a good practice applied to ensure that

               the system meets security standards. This test checks the security state and

               what types of software have been installed.

       Windows operating system is not an exception, and along with other operating systems

has its weaknesses, but “with proper maintenance and configuration, a Windows OS can be

made relatively secure,” (Fogie, 2004). Weaknesses include uneducated users, commercial

system, poor auditing, size/complexity, insecure installation.

       Securing an operating system requires careful administration from installation to

auditing and patching.    Operating system security implementation needs to follow some

guidelines adequate for the type of organization in order to provide secure environment and

prevent security breaches.

                 Disaster Management Communication System Requirements

       To keep the system secure, all of its layers should be properly secured. The memory

layer, being an access point for security violations is a vulnerable part of the system. Potential

memory violations or damages for the DMCS can originate from poorly designed programs,

particularly screens through which data is entered and stored. When the screens are designed,

the location where data is being sent and stored needs to be carefully determined to prevent it

from being lost or unintentionally disclosed.

       For the DMCS, the operating system is used for user authentication.                   Physical

authentication methods control the access of users to the server, computers, and portable

devices used on disaster sites for accessing the system. The access to the server location is
                                                  Database Security Policies and Procedures for DMCS 60

restricted and granted only to personnel responsible for maintenance and system

administrators. A list of individuals with access to the server should be kept. Computers and

portable devices are easily misplaced or stolen, which requires another level of physical

authentication. For this reason, security tokens need to be provided for individuals allowed to

access the system. For the DMCS, synchronous dynamic password tokens should be used as

they offer a high level of security by generating new and unique passwords at a certain time

interval, which only the holder of the token knows. Digital authentication for the DMCS should

utilize secure remote passwords (SRP), which are used to prove the identity of a user to the

server. SRP requires that the user provide a password, which the server needs to recognize in

order to allow access to the system. This type of digital authentication is reliable and does not

require the use of third party authentication protocols.

       The password policy for the DMCS is enforced at all levels of the system and it is created

according to the following requirements:

              prompt users to change their passwords every three months

              do not allow password reuse

              keep the latest four passwords for each account

              instruct users not to share their passwords and how to store them

              passwords should include at least one character of each of the following:

               uppercase letters, lowercase letters, digits, and/or symbols

              passwords should not include user’s first or last name, account, address

              an account is locked after the third unsuccessful logon
                                                   Database Security Policies and Procedures for DMCS 61

       The following screenshots demonstrate some of the steps that were taken to make the

DMCS a safe environment.

                                          Updating the OS

       Updating the operating system and installing the latest patches to keep it up to date and

properly secured. Having the latest security features installed helps keeping the system

protected against new types of threats.

                                       Disabling file sharing
                                                   Database Security Policies and Procedures for DMCS 62

        File sharing should be disabled by default to prevent anonymous file sharing that is

performed without the security of a user account.

                                          Enabling logging

        System logging in Windows needs to be enabled to make troubleshooting, recovery and

activities tracking active.

                For the DMCS to be secure and reliable, the operating system needs to be made

as secure as possible. All employees, and if possible users, need to be familiar with the

system’s security policy to minimize the risk of opening security vulnerabilities. Audit of the

system should be performed regularly to confirm that it is running effectively and there are no

unplanned processes or actions taking place.

        The DMCS is built on IIS (Internet Information Services), which connects the databases

with the ASP.NET pages to create the user interface of the system. IIS provides a reliable and

secure platform and offers a scalable Web Infrastructure. This Web Infrastructure is useful for

adding or removing servers in order to increase or decrease available capacity to meet demand
                                                  Database Security Policies and Procedures for DMCS 63

without interfering with the availability of the application, and therefore contributing to the

overall security of the system.       Error messages generated from the IIS are used by

administrators for easier troubleshooting. The error messages contain detailed information

about a request, what has potentially caused the error, as well as suggestions about possible

fixes. For security reasons, to minimize the risk of exposure and attacks, when IIS is installed

the only feature that is enabled is request handling for static Web pages. All other necessary

features (ASP.NET, scripting) need to be turned on from the IIS Manager under Enabling and

Disabling Dynamic Content in IIS 6.0, or the IIS returns a 404 (page not found) error. “IIS 6.0

includes a variety of Security in ISS 6.0 features and technologies to help ensure the integrity of

your Web and FTP site content, as well as the data transmitted though your sites” (Microsoft,

IIS 6.0 Operations Guide, 2005). Security features that are associated with IIS and implemented

for the DMCS include authentication, access control, encryption, and auditing.

       Administrators of DMCS should utilize the request filtering module of IIS to implement

acceptance policies for the server and therefore increase the security. Another feature of IIS,

which administrators need to employ, is the URL scan, which scans all incoming requests to the

server and stops the potentially harmful ones from reaching applications.

                                Part III Administration of Users

       Administration of users involves creating user accounts, granting privileges, deleting and

modifying accounts. These procedures are an important aspect of database security and need

to be implemented in compliance with the organization’s policies and overall security

regulations. For the Disaster Management Communication System administration of users
                                                 Database Security Policies and Procedures for DMCS 64

needs to include documentation that can be used as a source of procedure guidelines so that

administration is consistent in time. In addition, in a case of a security breach, documentation

serves as a path, which can take a database administrator through the system and discover

where the breach occurred and what caused it.

       User administration documentation incorporates the following elements:

                 Administration policies – a list explaining in detail policies for managing new

                  and terminated employees, managers, system and database administrators,

                  database managers, operating managers, and human resources

                 Security procedures – describes the process of executing administrative tasks

                  according to the requirements of the given organization

                 Procedure implementation scripts/programs – records of all scripts/programs

                  used in the executing of the administrative tasks, including a user’s manual

                  and operational manual

                 Predefined roles description – a description of all roles and their

                  responsibilities, as well as relationship between the roles

                 Administration staff and management – in depth description of manager and

                  administrator positions; and an organizational chart

       When creating user accounts to maintain a secure and dependable system,

documentation guidelines and security policies should be followed. There are two types of

logins – Windows Integrated login, and SQL Server login. The Windows Integrated login

provides users with access to the system without prompting for username and password. It

uses the security features of Windows by encrypting and sending the user’s information
                                                  Database Security Policies and Procedures for DMCS 65

through the browser for authentication. If the authentication of the user fails, then the

browser prompts the user for Windows account and password. The Windows Integrated login

type is more secure and preferred option. A Windows Integrated login can be created in SQL

Server Management Studio by taking the following steps:

               1. Open Object Explorer and expand the server in which you need to create the

                  new login

               2. Right-click on Security folder, go to New, and click on Login

               3. On the General page, fill out the Login name box

               4. Select Windows Authentication

               5. Select Database and Language

               6. Click OK

       After the logins are created, database users can be created. Database users are linked

with the login IDs to make it possible for users to access the database. “You cannot log in to a

SQL Server database without first supplying a valid login ID and password,” (Afyouni, Database

Security and Auditing, 2005, p. 69).

       SQL Server has two default logins: system administrator (SA) login and built-

in/administrator login. The SA login cannot be deleted or modified and it has access to every

database or object. In contrast, the built-in/administrator login, which is an optional login, can

be removed and its permissions can be modified.

       Removing a user account should be done after acquiring a written request approved by

the manager of the account holder. A good practice is to always make a backup of an account

before removing it in order to save objects from the database that are associated with that
                                                  Database Security Policies and Procedures for DMCS 66

account. This gives the database administrator the opportunity to use or to refer to the work of

an already terminated employee, whose account has been removed. To remove a Windows

Integrated login, highlight the login that needs to be removed and click on Delete in the Action

menu. An alternative way for keeping a removed user’s objects is by user schema separation.

When using user schema separation, objects do not belong to the user directly; they belong to

a schema, which in turn belongs to the user. When an object belongs to a user, no one else can

ever own that object even after the user has been removed. The only way to access user

created objects is with the SA account, as this has the highest level of access, which can be used

to repermission user created objects.       What makes schemas different is that “schema

ownership is transferrable,” (Redman, 2008). The use of schemas can be beneficial in several

ways: it allows better access control, and levels of access for the database administrator.

Objects can be transferred from one schema to another, multiple users can share the same

schema, and most importantly, users can be removed without losing the objects they own.

When objects are created, they are created and stored within a schema. For example, creating

a table called MyTable within MySchema schema requires the following code:

               create table MySchema.MyTable (col1, int, col2 int)

       User accounts in the Disaster Management Communication System can be also modified

or locked and passwords or storage quota can be changed. These changes can be done after a

written approval providing arguments about the changes is present. Using Enterprise Manager,

an already existing account can be modified in the following way:

                     Click on Security and then click on Logins.

                     Choose the login to be modified.
                                                     Database Security Policies and Procedures for DMCS 67

                     Click on Properties on the Action menu and make the changes.

       Users can access and manipulate a database while logged onto a different database

through database links, which essentially are connections between databases. Figure 3-1

illustrates database link architecture connecting DB1 with DB2.

                                          Database Link

                            DB1                                       DB2

                                 Figure 3-1: Database link architecture

       With database links, the user is only authenticated in the database he/she is initially

logged onto and the second database does not require authentication.

       The administration of users is not an easy task and needs to meet the terms of database

best practices and security regulations. Best practices that apply to SQL Server and are

recommended by Microsoft, database administrators, and security professionals suggest:

                     Follow the company’s policies and procedures for creating, modifying,

                      and removing database users

                     Replace default passwords with strong passwords and do not save them

                      in a file that is not encrypted or safe

                     Do not share user accounts, particularly DBA accounts

                     Document and create logs for changes and removals of user accounts

                     Do not remove accounts, even if outdated; as an alternative disable

                      connection privileges for different applications
                                                 Database Security Policies and Procedures for DMCS 68

                     Use different logins and passwords for different applications and give

                      access permissions to users only as required

                     Make user administration best practices and company policies and

                      procedures available to users, administrators, and developers

                     Keep up with database technology and database security, considering

                      potential new vulnerabilities which may increase database security risks

                     Make sure that procedures are consistent with the company’s policies

                      and procedures

       Creating, modifying, and removing of database users, as an integral part of database

security, needs to be consistent with the overall security strategy of a company. Giving

privileges and access rights to users should be done only as needed and as a further security

measure, direct access to database tables should be blocked. Data should be accessed through

stored procedures and views. All service packs and security patches for SQL Server and the

Windows server it is running on should be regularly updated.

                Disaster Management Communication System Requirements

       Windows Integrated logins should be used for the DMCS to provide users with access to

the system. With this type of logins, “The current Windows user information on the client is

used for Integrated Windows authentication” (Microsoft, Integrated Windows Authentication).
                                                   Database Security Policies and Procedures for DMCS 69

                               Creating a new Windows Integrated Login

       Users of the DMCS need to have permissions on the SQL Server granted to them in

order to be able to access and manipulate the database. “Permissions can be granted to a user

or role to allow that user or role to perform operations such as selection, insertion, or

modification of data rows” (Chigrik, 2003). In SQL Server, permissions can be granted, denied,

and revoked from a user or a role. According to the role users have in the system, they can be

granted one of three types of permissions: object permissions, statement permissions, or

implied permissions. Database developers of the DMCS are granted object (select, insert,

delete, update, execute, dri commands) and statement (backup database, backup log, create

database, create default, create function, create procedure, create rule, create table, create

view commands) permissions in order to have optimal use of and input to the database.

       For the DMCS, the SA account should not be given to users and its password should be

changed as this account has the highest level of security clearance.
                                                    Database Security Policies and Procedures for DMCS 70

       Another security measure that should be applied to the DMCS is logging all queries ran

on the server, a function provided by the Analysis Services in SQL Server. This will provide

information such as name of the user who ran the query, the time the query began, duration of

query execution, ID of database used in the query. “SQL Server Analysis Services uses query

logs to log statistical information about queries” (Melomed, 2006).

                                   Analysis Services in SQL Server

       In order to keep the system functional and consistent despite the frequent change of

users, user schema separation should be used. This would make it possible for new users

entering into the project to continue working on already existing objects without having to start

from zero. Another action that should be taken to ease the work of DMCS users is using

database links to connect the relational and the warehouse databases in the DMCS. This will

allow users to work on both databases without having to constantly log in and out.

       Administration of users in the DMCS follows the best practices, applicable to SQL Server

explained above, whenever user accounts are created, removed, or modified.                      Default
                                                    Database Security Policies and Procedures for DMCS 71

passwords should be changed and when new ones are created, they need to meet the

requirements of the system, explained in Part IV of the manual. Access to applications should

be granted only to users that need it and all changes and deletions of user accounts in the

system need to be documented for future reference. Each user needs to have an individual

account, this way the SA can follow user activities.

       Administrators of the DMCS should make use of the Activity Monitor in SQL Server. This

tool provides information about processes in progress and their effect on the server. With the

help of Activity Monitor, administrators can follow and control users’ actions and tasks.

                                    Activity Monitor in SQL Server

                       Part IV Profiles, Passwords, Privileges, and Roles

       User administration and security are fundamental parts of data security. Well-organized

user administration that is based on proven practices and is consistent with the organization’s

overall strategy creates an additional layer of security. The four elements that form user

administration are profiles, passwords, privileges, and roles. Each one of these elements needs
                                                 Database Security Policies and Procedures for DMCS 72

to be built upon best practices to provide a strong defense against unauthorized access and

potential security attacks.

       Profiles are used for defining and restricting what and to what extend users can do with

system resources.     The functions of profiles, such as connection control and resource

utilization, in SQL Server are managed at the application level: “Query and connection time-

outs in a SQL server-based application are handled at the application level within OLEDB,”

(Afyouni, Database Security and Auditing, 2005, p. 93).

       Passwords of user accounts need to be designed according to policies that reduce the

probability of hackers breaking them. Breaking a user account password jeopardizes the

database, the network, and the entire the system. Password policies are meant to set rules for

password production that are going to make them stronger and harder to be broken. Aspects

that password policies address are password complexity, password aging, password usage, and

password storage. Password complexity determines what passwords should contain (digits,

symbols) and how long they should be. The length most companies require is eight characters.

Password aging designates the period of time during which a password can be used before it

expires and has to be changed. Usage of passwords determines how many times a certain

password can be used. Password storage is a system for encrypting and storing user account

passwords. In Microsoft SQL Server 2008 the password policy is built into the server. SQL

Server 2008 can validate a password during authentication or during password set and reset by

executing the NetValidatePasswordPolicy() function.            There are five techniques of

authentication in SQL Server 2008 and are supported by Windows (basic, digest, NTLM,

Kerberos, and Integrated authentication). Basic authentication transmits the login credentials
                                                  Database Security Policies and Procedures for DMCS 73

in clear text that is base-64 encoded. The credentials need to match a Windows login in order

for SQL Server to authorize access to the database. Digest authentication does not involve

transmission of credentials across the network; instead they must match a valid Windows

domain account. Using Windows security and having it implement password policies means

that once a user is logged onto a Windows server he/she does not have to provide his/her

password again to access different applications. “It is based on an "access token" which

contains the user's unique security ID or sid, which is then used by the client to gain access to

network resources such as SQL Server without having to supply login credentials again,” (Page,

2007). Both NTLM and Kerberos do not send user account passwords across the network,

which is how attackers are stopped from breaking into the system.               NTLM is based on

challenge/response methodology, meaning that when a user access a resource, the server

sends a request for identity proof and if the user responds correctly, he/she is authenticated to

the server. Figure 4-1 represents the process of a NTLM authentication method.

                                               Message 1

                                            Challenge: 0x0123abc

                                          Response: 0xb45f65df565hhy78

                 Workstation                                                      Server

                                  Figure 4-1: NTLM authentication

       What makes Kerberos different and more secure from NTLM is that “a secret key,

known only to the server and client and unique to the session, is used to encrypt the handshake

data,” (Afyouni, Database Security and Auditing, 2005, p. 98). This requires the server to
                                                        Database Security Policies and Procedures for DMCS 74

authenticate the user as well as the user to authenticate the server, which is done with the help

of a secret key produced form a Key Distribution Center (KDC). Figure 4-2 illustrates the KDC.

                                           Client wants to access a server

                                          KDC issues keys for server and client

                  Workstation                                                               Server

                     Figure 4-2: KDC produces a key and issues a session ticket to client

       Windows integrated authentication is the preferred method of authentication because

it supports encryption whereas SQL Server logons save user names and passwords in

connection strings and are sent across the network in clear text. “With Windows Server 2003

or later, the policy will be implemented via an OS-level call, Net ValidatePasswordPolicy, so that

the administrator can use the same policy for both Windows integrated and SQL Server logins,”

(Beauchemin, Berglund, & Sullivan, SQL Server password policies and credentials, 2005).

Setting the password policies in SQL Server using Windows logons requires the following steps:

           1. Click Start menu  All Programs  Administrative Tools.

           2. Double-click on Local Security Policy.

           3. Expand Account Policies

           4. Click on Password Policies (here we have six policy parameters that we can

               enforce: enforce password history, maximum password age, minimum password

               age, minimum password length, password must meet complexity requirements,

               and store passwords using reversible encryption).
                                                 Database Security Policies and Procedures for DMCS 75

           5. Click on Account Lockout Policy on left. The options here are account lockout

              duration, account lockout threshold, and reset account lockout counter after.

              These options specify how many invalid logons are allowed before the account is

              locked which makes it unusable, as well as how long an account should be


       Privileges are used to grant or permit access to data as well as to permit or deny

operations on databases. In SQL Server, there are four levels of permissions: system/server

level, database level, table (object) level, and column level. Although permissions are on

different levels of the database hierarchy, having higher-level permissions does not grant

lower-level access. Permissions for each level must be granted individually. At the server level,

network endpoints can be secured to control the communication channels into and out of the

server. Server level privileges depend on fixed server roles. Users who are members of the

sysadmin fixed server role have no restrictions and can perform any function in the system.

Serveradmin members have some limitations to their actions on the server level; they can

perform the following functions: add members to the serveradmin role; execute DBCC

FREEPROCCACHE command; execute SP_CONFIGURE system-stored procedure; execute

SP_FULLTEXT_SERVICE system-stored procedure; execute SP_TABLEOPTION system-stored

procedure; execute RECONFIGURE command; and server shut down.                    Members of the

setupadmin role can add members to the setupadmin role; add, drop, and configure linked

servers; and mark a stored procedure as a start-up procedure. Securityadmin role members

can execute the CREATE DATABASE PERMISSIONS statement; read error logs; change

passwords; and manage logons (add, remove, remove links). Processadmin members can
                                                Database Security Policies and Procedures for DMCS 76

manage processes running in the SQL Server (add members to the processadmin role;

terminate a process by executing KILL command). Members of the dbcreator fixed server role

can create, alter, and drop databases, as well as add members to the dbcreator role. Diskadmin

members can manage the disk files for the server and database by adding members to the

diskadmin role; executing DISK_INIT command; executing SP_ADDUMPDEVICE system-stored

procedure; and executing SP_DROPDEVICE system-stored procedure.                 Members of the

bulkadmin role can execute bulk insert operations (add members to the bulkadmin role;

execute BULK INSERT DML statement).

       At the database level, every object that is created can be secured. Database privileges

allow users to operate at the database level and privileges are granted to users in two ways.

Users can be granted with specific permissions individually, or they can be added to fixed

database roles. The fixed database roles come with set permissions. Db_owner members can

perform any functions within the database and have no limitations. Db_accessadmin role

members can add and remove users from the database only through system-stored

procedures; they cannot execute any statements. Db_securityadmin members can change

permissions, object ownership, roles, and role membership; they can execute system-stored

procedures to manage ownership and membership and GRANT, DENY, and REVOKE statements.

Members of the db_ddladmin fixed database role can execute all DDL statements excluding

GRANT, DENY, and REVOKE.       Db_backupoerator members can execute CHECKPOINT, and

BACKUP statements as well as DBCC statements addressing backup.              Db_datareader role

members can execute SELECT and READTEXT statements on tables in the database. Members

of db_datawriter role can respectively execute INSERT, UPDATE, DELETE, and UPDATETEXT
                                                  Database Security Policies and Procedures for DMCS 77

statements. Members of db_denydatareader are denied SELECT and READTEXT permissions on

all of the database tables.     Db_denydatawriter members are denied INSERT, UPDATE,

UPDATETEXT, and DELETE permissions on all tables.

       Aside from the permissions that come with fixed database roles, other privileges, such

as create table, create view, create procedure, create function, create default, create rule,

backup database, and backup log can be granted. In Enterprise Manager, these permissions

can be granted by following these steps:

          1. Open Enterprise Manager and expand server hosting the database in which

              permissions are going to be granted.

          2. Open Properties dialog box.

          3. Click on Permissions and put checkmarks in the desired user or role permission


          4. Click OK.

                                   Granting/denying permissions
                                                 Database Security Policies and Procedures for DMCS 78

       Permissions can be revoked by following the above steps with the difference that

instead of placing checkmarks, they need to be removed. To deny permissions, follow the same

steps used for granting and revoking permissions.

       Privileges for accessing database objects individually can be granted. Table/object

privileges are GRANT, REVOKE, and DENY. These permissions can be granted by taking the

following steps from the User or the Object:

           1. Open Enterprise Manager.

           2. If granting from the User, expand the database in which the user and the object

              reside and select the desired user. If granting from the Object, expand the

              database in which the object and the user reside and select the desired object.

           3. Open Properties dialog box and click on Permissions.

           4. Place checkmarks on the desired object/user.

           5. Click OK.

           6. Click OK gain

       Revoking and denying permissions can be done by repeating the steps for granting

permissions and removing the checkmarks.

       “Microsoft SQL Server also gives you the ability to specify object permissions on

individual columns,” (Afyouni, Database Security and Auditing, 2005, p. 124). This makes it

possible for selected fields of database tables, not the whole table, to be hidden from some

users while available to others. Column-level privileges are granted and revoked by following

the steps for granting/revoking object-level privileges. This feature is useful for the Disaster
                                                   Database Security Policies and Procedures for DMCS 79

Management Communication System, having in mind the nature of information contained in

the database and the number and position of users accessing the database.

       Roles are used for an easier organization and administration of privileges. Instead of

assigning privileges to users, they are assigned to roles, which in turn are assigned to users.

First, a role must be created, then privileges are assigned to it, and then that role is assigned to

selected users. Three types of roles can be created in SQL Server: fixed server, fixed database,

and user defined. User defined role are divided into two categories: standard and application.

Standard roles have members and can be granted or denied permissions. Application roles do

not have members and need to have passwords; they are activated by applications and are

used to verify authorization for these applications. In order to secure a database application

through application roles, the following steps need to be taken: crate the application role;

assign permissions to the role; create a connection to the sever; and activate the application

role. Only members of sysadmin, db_owner, and db_securityadmin can create user-defined

roles by following the steps:

           1. Open Enterprise Manager and expand the desired database

           2. Click on Roles tab

           3. Click on New Database Role from the Action menu

           4. In the Name box, type the name of the role

           5. Click OK
                                                     Database Security Policies and Procedures for DMCS 80

                             Creating a new application role called db_audit

       Securing a system requires protecting it from external as well as internal attacks. “The

security threat of an attack from someone within the company is higher than the threat from

someone outside,” (Afyouni, Database Security and Auditing, 2005, p. 137). With this said, user

administration and the development of its aspects (profiles, passwords, privileges, and roles)

need to follow the suggested best practices:

              Always store passwords encrypted as opposed to in plain text

              Change passwords frequently

              Ensure that passwords are eight characters or longer

              Create passwords that are complex enough to prevent hackers from breaking

               them (including special characters, length, account lockout, reuse)

              Never write down, share, type in an e-mail, or give passwords over the phone

              Avoid granting privileges directly to users, instead use roles

              Always report the compromise or loss of a password

              Always report violations to roles, privileges, profiles, or passwords
                                                   Database Security Policies and Procedures for DMCS 81

              Use Windows Integrated security for securing SQL Server

                 Disaster Management Communication System Requirements

       Windows integrated authentication should be used in the DMCS for granting users with

access to the system because it encrypts user names and passwords before sending them

across the network and therefore provides optimal protection and reliability.

       User passwords should be at least eight characters long, containing a combination of

numerical and alphabetical characters. Accounts should be locked after the third unsuccessful

logon attempt and left locked for thirty minutes. Passwords should be stored encrypted at all

times. Users should be encouraged not to share passwords and not to provide them over the

phone, e-mail, and never to keep them written down especially in their offices. Users should be

obligated to inform administrators whenever their passwords are stolen or lost.

                                   Password policies for the DMCS
                                                   Database Security Policies and Procedures for DMCS 82

                                Account lockout policies for the DMCS

       For maintaining and working with the DMCS, the following types of fixed database roles

should are utilized and all employees and users of the system are added to the appropriate

type. Privileges should be granted to users through roles and not individually.

               Database Role                         Users and privileges
                                       Database creators; giving them the ability to
       Db_owner                        build databases, create, alter, and drop objects.

       Db_securityadmin                Database administrator; enabling them to
                                       manage roles and permissions

       Db_datareader                   Analysts; allowing them to view table contents

       Db_datawriter                   Data entry employees; allowing them to add and
                                       update information

       Db_denydatareader               Clients; preventing them from accessing
                                       confidential information

       Da_denydatawriter               Clients, analysts; preventing them from altering
                                       data in order to keep its integrity intact
                                                Database Security Policies and Procedures for DMCS 83

       Database creators need to be assigned to the db_owner role in order for them to

develop new databases and work with existing ones while having the necessary permissions

(create, alter, and drop). Database administrators should have privileges associated with the

db_securityadmin (grant, deny, revoke) role for them to set up and manage roles and

permissions. Analysts should be assigned to the db_datareader role, which allows them to

execute SELECT and READTEXT statements against tables in the database in order to obtain the

necessary information. db_datawriter privileges are required by data entry employees in order

to be able to execute INSERT, UPDATE, DELETE, and UPDATETEXT statements to update the

database. All clients need to be members of the db_denydatareader role, which will prevent

them from executing SELECT statements on the database and view confidential information. To

avoid unwanted and random changes to the database, both, clients and analysts need to be

assigned to the db_ denydatawriter role that will not allow them to alter or delete any objects

in the database.

       User permissions in the DMCS need to be changed whenever employees’ position and

duties change. For example, a former data entry employee who only had write permissions

becomes a system administrator. The new position requires that the same employee be

granted new permissions to control the server and databases. Object permissions on individual

columns should be implemented for the DMCS, this SQL Server option would come in use when

a certain column from a table (such as social security number) needs to be available to

employees but not clients.      Keeping that information hidden from clients protects its

                                                      Database Security Policies and Procedures for DMCS 84

                      Part V Database Application Security Models
       Database application security models include security modes and various types of

applications that are used for securing data and protect access at the table level. For security

to be effective, all application users need to have their own database account and their own set

of assigned privileges.

       The two main security models used are Access Matrix Model and Access Modes Model.

Table 5-1 illustrates the way in which the access matrix model represents privileges. Examples

of objects include tables, views, and procedures. Subjects represent users, roles, privileges, or

modules. The intersection of rows and columns in the table correspond to the access the

subject has on the object.

                                         Object 1      Object 2      Object 3
                                1       read/write       write        admin
                                2          read           read        NONE

                                  Table 5-1: Access matrix security model

       Access modes model is divided in two modes – static and dynamic. Table 5-2 illustrates

the static access mode and Table 5-3 illustrates the dynamic access mode. The level, in this

case one through four, represents the degree of access. Subjects have access to their own level

of access and all lower levels.
                                                    Database Security Policies and Procedures for DMCS 85

                                  Level                    Description
                       use         1        Use an object without modifying it.
                      read         2        Read the contents of an object.
                     update        3        Modify the contents of an object.
                     create        4        Add instances to an object.
                     delete        4        Remove instances of an object.

                                    Table 5-2: Static access modes

                          Level                            Description
                                    Grant any static access mode to any other
             grant            1     subject.
                                    Revoke a granted static access mode from a
             revoke           1     subject.
            delegate          2     Grant the grant privilege to other subjects.
            abrogate          2     Grant the revoke privilege to other subjects.

                                  Table 5-3: Dynamic access modes

       Applications, programs that perform specific functions, help us determine where to

enforce data security. “Data security enforcement is required where data resides in the

database,” (Afyouni, Database Security and Auditing, 2005, p. 144). Some of the application

types are client/server applications, web applications, and data warehouse applications.

       Client/server applications’ components are user interface, business logic, and data

access. An application can include any one or a combination of these components, which lay

between the client and the server. Client/server applications function in the following way: a

user sends a request to the server using the user interface component (screens, codes, and

reports), next the server responds by sending an answer to the request. In this type of

application, the code and the data reside in one physical and logical place. In a client/server
                                                 Database Security Policies and Procedures for DMCS 86

application, the security module is embedded in the data access module, which retrieves and

manipulates data.

       Web applications are a type of a client/server application with the difference that it uses

the Web, a HTTP protocol, to connect to the server. The components of a web application are

web browser, web server, application server, business logic, and database server. Another

difference between the client/server and the web applications is that all components of the

client/server application reside in one server and the components of the web application are

most often on different machines.        This kind of architecture is projected to improve


       Data warehouse applications include various types of data from different databases that

are used for supporting decision-making of executive management. One database server

contains all application data, which when extracted is transformed into a warehouse model.

The data warehouse is after that accessed by online analytical processing (OLAP) and is used for

producing reports.

       Security models and their different types of applications combine to form an application

security model. Types of application security models are database role based, application role

based, application function based, application role and function based, and application table

based. Database role based security model requires that all end users are assigned database

roles that give them privileges for accessing application tables. The roles determine what

privileges the users have. A proxy user can only activate the assigned roles. Tables used in this

type of security model are application_users, which stores end users and their encrypted

passwords and application_user_roles, which contains all roles and their privileges. In SQL
                                                  Database Security Policies and Procedures for DMCS 87

Server, application roles are created in the database and then activated at the time of

authorization. By default, the application roles are inactive and can be activated by any user by

executing the SP_SETAPPROLE system-stored procedure. After a user activates an application

role, he/she is working in the security context of that role. An application role can be created

using Enterprise Manager by following these steps:

                  Open Enterprise Manager.

                  Expand the desired database, right click in the right pane, and select New

                   Database Role.

                  Type a name in the name box.

                  Under Database role type, select Application Role.

                  Enter a password and clock OK.

       Security models based on application roles is another method for organizing and

managing user privileges. Application roles are specific to business roles. An application role is

assigned to users giving them application privileges to read/write certain modules from the

application. This type of security model does not allow any changes to roles for security

reasons. In SQL Server, a database user executes stored procedures and performs all database

operations. Tables used in a security model based on application roles are application_users,

used for storing and maintaining all users and their encrypted passwords, and

application_roles, used for storing all roles defined by the application and the privileges

associated with them.

       Application function based security model divides the application into functions, which

perform certain tasks. Tables used in this type of security model are application_users that
                                                      Database Security Policies and Procedures for DMCS 88

stores users and their encrypted passwords, application_function used for storing all logical

functions of the application, application_function_privileges containing all function privileges,

and application_user_functions storing all end users and their application function privileges.

       Security model based on application roles and functions combines the role and the

function security models. The application is divided into functions and roles are assigned to

them. This security model offers the greatest flexibility for application security implementation.

The tables used are application_users used for storing all users and their encrypted passwords.

Application_user_roles store roles assigned to users and application_roles contain roles defined

by application. Application_functions are used for storing logical functions of the application.

Application_role_functions      store   functions     and    privileges      assigned   to   each    role.

Application_function_privileges are used for storing all privileges associated with each function.

       Application table based security model is the only model that grants privileges based on

tables. In SQL Server, the end user needs authorization on application functions, which is done

in the database.     Tables included in the security model based on application tables are

application_users     storing    all    users    along      with     their     encrypted      passwords,

application_user_tables used for storing tables that are assigned to users, application_tables

containing all tables belonging to be application, and application_table_privileges that stores all

privileges that specify access to a specific table.

         The feature that all of the described security models have in common is that they all

depend on the application to authenticate users by using a table that contains all end users and

their encrypted passwords.
                                                   Database Security Policies and Procedures for DMCS 89

                  Disaster Management Communication System Requirements

       For the DMCS, database role based application security would be the most appropriate

model because it suggests that all users have database roles assigned, giving them privileges to

read, write, or read/write application tables. Users would be able to utilize any privileges

assigned to their role. This would give the right amount of privileges to each user according to

their job. All users are assigned specific database roles, limiting access to certain pages and

tables to administrators only. Every page of the application can be assigned a security level by

specifying what role is required for accessing the page. This security feature of the .aspx pages

can be implemented to allow access to Adm_Missing.aspx web page to administrators only:


        <location path="Adm_Missing.aspx" >


           <authorization >

           <allow roles="Administrators"/>





       When creating the .aspx pages, all fields should have limited number of characters

allowed, in order to decrease the vulnerability of the application and the possibility of sql

injections. The following figure shows the MaxLength for a certain textbox is limited to a

certain number.
                                                      Database Security Policies and Procedures for DMCS 90

           In the DMCS, regular expressions are used for validating the fields and for text analysis.

“Combining regular expressions with SQL provides many alternative means of processing data.

Using these functions can reduce the amount of time required to add functionality to your

database as well as make the system more maintainable,” (Banister). Verifying date format for

the system is done using the isdate() function. This function checks the format and data type of

the entered information and returns one when the entered information is valid and zero when


           Example of a valid entree:
                                                    Database Security Policies and Procedures for DMCS 91

                       select isdate(‘7/22/2005’)

                       returns: 1

       Examples of invalid entrees:

                       1) select isdate(‘12/4/2005’)

                           returns: 0

                       2) select isdate(‘17/11/2005’)

                           returns: 0

       For authorization and securing the transactions that take place in the system, Hypertext

Transfer Protocol Secure (HTTPS) is used. When HTTPS is used, information is being encrypted

with a digital certificate before it is sent across the network, instead of sending it in plain text.

This is a necessary step for the DMCS, when handling confidential information.

                                Part VI Virtual Private Database

       In today’s business world, different companies share the same vendor’s database. All

companies have unique needs and requirements for the database. For that database to be

efficient and meet these requirements, it has to be adjusted for each company. Privacy and

security for each company individually need to be taken into consideration. When more than

one entity uses the same database, it is important that data belonging to a certain entity is

visible and available only to that particular entity. This is handled by virtual private databases

(VPD), which utilize row-level and column-level security. “VPD controls data access at the row

or column level,” (Afyouni, Database Security and Auditing, 2005, p. 177). Access to the shared
                                                      Database Security Policies and Procedures for DMCS 92

database schema is restricted to only data belonging to a given entity. That data is saved in

tables from the shared database. Each entity can only view and modify their own data and

access to other entities’ data is denied. The two main reasons for using VPD are that the

security requirements of a security policy demand that data access be restricted at the row or

column level and that the same database is used by numerous unrelated entities. Figure 6-1

illustrates VPD.

                           Database Schema

                                                                               User 1

                                                                               User 2

                                   Figure 6-1: Virtual private database

       In SQL Server VPDs are not supported therefore row and column access is implemented

by using the VIEW database object. VIEWS are used to restrict access and manipulation if data

residing in the database tables, VIEWS can hide row and columns from users. For more detail

of row-based security, a combination of application table-based security model and application

function-based model can be implemented. Access levels used are 0 – no access; 1 – select; 2 –

select, insert; 3 – select, insert, update; 4 – select, insert, update, delete, and 5 – administrator

access. For this purpose, a new column needs to be added to each table to capture the access

level of a certain row.

             Requirements for the Disaster Management Communication System
                                                   Database Security Policies and Procedures for DMCS 93

       The Disaster Management Communication System is used as an example to explain how

implementation of VPD is done by referencing the following steps:

                 Alter the CLIENT table to include ACCESS CONTOL column:

                  use disaster_management

                  alter table client

                       add access_level integer


                 Create APPLICATION USERS table, setting the default access level at 0, or no


                  create table app_user_access

                       (username varchar(120) not null primary key,

                       access_level tinyint not null default 0)

                                       Database Security Policies and Procedures for DMCS 94

                       Creating app_user_access table

   Create stored procedures that return data and revoke privileges on tables:

    create procedure Client_sel


         select client_ID, first_name, last_name, street, city, state, zip, phone,

         from client

         where access_level > 0

    and access_level <= (select isnull(access_level, 0)

    from app_user_access where username = user)

                                       Database Security Policies and Procedures for DMCS 95

                    Creating client_sel stored procedure

   Apply privileges to users:

    grant execute on Client_sel to tom

    grant execute on Client_sel to mary


   Create stored procedures to allow insert, update, and delete functions:

    create proc Client_del (@id int)


         declare @level tinyint;
                                       Database Security Policies and Procedures for DMCS 96

    select @level = (select is null(access_level, 0)

    from app_user_access where username = user);

    if @level >= 4 begin

         delete from client where client_id = @id and access_level >= @level;



                  Creating the client_del stored procedure

   Apply privileges to user:
                                                    Database Security Policies and Procedures for DMCS 97

                   grant execute on Client_del to tom

                   grant execute on Client_del to mary


       For the implementation of column-level security in SQL Server, two new tables need to

be created. The first table (app_tables) stores a list of all tables and the second table

(app_columns) stores a list of each column within each table as well as the access level for each


                  Create app_tables table:

                   use disaster_management

                   create table app_tables

                        (table_id int not null primary key,

                        table_name varchar(120) not null)

                                     Database Security Policies and Procedures for DMCS 98

                  Creating the app_tables for the DMCS

   Create app_columns table:

    use disaster_management

    create table app_columns

       (column_id int not null primary key,

       table_id int not null references app_tables(table_id),

       column_name varchar(120) not null,

    access_level tinyint not null default 0)
                                                    Database Security Policies and Procedures for DMCS 99


                              Creating the app_colmns table for the DMCS

                  Implementing column-level security by granting column privileges to a user,

                   in this case allowing Mary to update the client_status column from the Client


                   grant update on client(client_status) to mary

       To verify that access to each column is being controlled, access to tables should be

performed with stored procedures. VPD strengthens the application security because the

access policy is attached to the table and there is no way to go around it. For the Disaster
                                                    Database Security Policies and Procedures for DMCS 100

Management Communication System VPD can be helpful for restricting warehouse employees

from accessing client personal information for example, because this information does not

belong to them and it is irrelevant to their job.

       The use of virtual private data allows the same database to be used by multiple entities

without the risk of one entity viewing or manipulating data belonging to another entity. All

entities are entitled to access only their own data, which is accomplished by implementing row-

and column-level security.

       The following screens show the roles, access, and permissions that the SA of the DMCS

has on the server.
                                               Database Security Policies and Procedures for DMCS 101

       After opening the SQL Server Management Studio, in the Object Explorer expand the

Security folder. Under Logins, click on SA.

       The SA has public and sysadmin roles on the server.
                                               Database Security Policies and Procedures for DMCS 102

       The SA has access to the master, model, msdb, and tempdb databases and is a member

of the public role for the disaster_managenet database.
                                               Database Security Policies and Procedures for DMCS 103

       The SA is granted the permission to connect to the database engine.

                             Part VII Database Auditing Models

       For database security to be complete and effective, database auditing needs to be an

inseparable part of it. Database auditing is a process that ensures that security measures are

applied and working properly according to government regulations and the company’s policy.

It examines documents, processes, data, and activities and validates them according to specific

requirements. “No matter what security measures you put in place, you must plan ahead with
                                                 Database Security Policies and Procedures for DMCS 104

an auditing procedure to understand if your security measures are working or not,” (Afyouni,

Database Security and Auditing, 2005, p. 217). In order to avoid violations, it is always a good

practice to consider database auditing as the last step of database security. The auditing

process needs to be applied to each person, document, and system in order to be effective and


       Database auditing requirements suggest that the audit process should be selective,

comprehensive, and non-invasive. Selective audit means that details only for a specific entity,

which requires auditing, are obtained. Selective audit can be performed based on database,

tables, users, access type, or success/failure. Comprehensive audit captures the entire scenario

of auditable information. This requires the collection of data from different environments, such

as the web and the application server, as well as multiple data access/update types (insert,

delete, update, and select). The audit needs to monitor and capture the state of data before

and after modifications. It needs to record a copy of all data that has been accessed and the

number of row that have been accessed.           Non-invasive database audit entails that the

performance of the system is not affected by the auditing process.

       The components of database auditing are people, objectives, procedures, audited

entities, and database. Each one of the components needs to be included in the auditing

process to ensure completeness and efficiency of the process. The auditing process begins

after the system is implemented and is in production to confirm that it complies with policies,

laws, and standards. The first step of the process is to recognize what the objectives are as well

as plan a procedure for executing the process. Second, according to the objectives review,

verify, and validate the system. If any deviation from the objectives is discovered, they should
                                                 Database Security Policies and Procedures for DMCS 105

be documented. The final step is to document the results of the audit and according to these

results to recommend any changes that should be done to the system.

       Establishing objectives on what the audited entity is measured against should be a part

of the development process of an entity. The reasons for establishing and documenting

objectives are complying, informing, planning, and executing. Database audit inspects all of the

following: data integrity, data confidentiality, data changes, data structure changes, application

users and roles, , access control, change control, physical access, database or application

availability, and auditing reports.

       There are different audit classifications, specific to every industry and business. Internal

audit is performed by a member of the company that is being audited. The goal of this kind of

audit is to make sure that all objectives are met because of a planned audit, in the case of an

internal incident to investigate the system, and to investigate a problem by request from an

external entity. An internal audit assesses risk exposures of the organization related to

              reliability and integrity of financial and operational information

              effectiveness and efficiency of operations

              safeguarding of assets

              compliance with laws, regulations, and contracts

       External audit is always a planned and scheduled audit, and is conducted by someone

outside the audited entity, and usually hired by the government. The purpose of the external

audit is to investigate operational or financial state of a company by request from the

government or because of suspicious activities and to verify that all objectives are met.

Database system audits are most often automatic. The external audit provides an independent
                                                 Database Security Policies and Procedures for DMCS 106

opinion on the organization’s status in a chronological report. An automatic audit is conducted

completely by machines and produces reports and logs, which are later used by administrators

to verify the integrity of the system and discover any suspicious activities. Some systems utilize

artificial intelligence to read these reports and take preventive or corrective actions

accordingly. An audit conducted only by humans is a manual audit. Data for the audit is

collected through interviews, from documents and observation. Finally, an audit that combines

manual and automatic audits is called a hybrid audit, which is the most often used one.

       Different types of audit are conducted according to the business being audited.

Financial audit verifies that all financial transactions are registered and comply with the law. A

security audit checks the security of a system to make sure it is at an appropriate level.

Compliance audit verifies compliance with government regulations, industry standards, and

partner/client policies. The operational audit ensures that operations are working according to

the company’s policies.     An investigative audit verifies the integrity of a system and is

conducted by request or as a response to an incident within the company. Product audit

guarantees that the product meets the standards in the industry by checking how it was

produced.    A preventive audit identifies any possible problems by making sure business

operations are performed according to specifications.

       Using SQL Server Audit is one way to implement auditing. With the built-in features,

auditing can be done at the database level or at the instance level. Another benefit of the built-

in features is that various activities can be audited, such as data modeling language and data

definition language. The activities of the database administrator can also be audited by an

outside entity. The built-in auditing feature of SQL Server allows for select, insert, update,
                                                     Database Security Policies and Procedures for DMCS 107

delete, and execute statements for individual users to be recorded. Another option for auditing

is to build a mechanism for auditing. There are different auditing models, which have the same

process of activities. The main idea of these methods is to check whether the user, action, and

the object from a given action are registered in auditing repository. Figure 7-1 illustrates a

flowchart of data auditing.

                                 action start

                              get user name and

                   N      user registered in audit        Y

                                                      N       action registered for current


                                                              is object on which action is
                                                                 performed registered?


                                                                get previous value and
                                                                 record it in database

                                                                   continue with the


                                    Figure 7-1: Data auditing flowchart

        If a user, action, or the object from a given action is found to be registered, then the

following information is documented: state of the object before the action as well as the time
                                                     Database Security Policies and Procedures for DMCS 108

of the action, full description of the action that was performed, and the name of the user who

performed the action. Simple Auditing Model 1 keeps chronological record of audited entities,

such as users, tables, and columns, along with the actions (DML transaction or logon/logoff

time) performed by or on these entities. Model 1 registers all audited entities in the audit

model repository. That information is stored in a control column and is used by the auditing

process to determine if the user, action, and object need to be audited.

                    Figure 7-2: Data model of a repository for simple auditing model 1

        Simple Auditing Model 2, which is similar to Model 1, only stores the column value

changes and not the action performed on the data. In this way, the amount of data being

stored in the database is decreased.
                                                      Database Security Policies and Procedures for DMCS 109

                     Figure 7-3: Data model of a repository for simple auditing model 2

       Advanced Auditing Model offers a user interface, which makes it more flexible and easy

to use. As opposed to Simple Auditing Model 2, this model stores all entities that can be

audited – users, actions, tables, and columns. Auditing all the entities is a feature provided by

this model, which system built-in feature do not often support. The Advanced Auditing Method

would be the best choice for the Disaster Management Communication System because of the

details and completeness it offers by keeping a record of all auditable entities.
                                                     Database Security Policies and Procedures for DMCS 110

                 Figure 7-4: Data model of the repository for an advances auditing model

       Historical Data Model is a model normally used in financial applications. It stores the

entire row from a table when a DML transaction is performed. The stored state of the row is

from before the change or deletion.

                  Figure 7-5: Data model of a repository for a historical auditing model
                                                      Database Security Policies and Procedures for DMCS 111

       Auditing Application Actions Model stores certain operations and actions that may need

to be audited.

                   Figure 7-6: Data model of a repository for auditing application actions

       Database auditing can be beneficial to any organization for several reasons:

          it enforces company policies and government regulations and laws

          lowers the possibility of security violations, identifies security gaps and


          observes and evaluates operations of the audited entity

          provides a sense of state of security and confidence in the audited entities identifies

           and removes doubts

          makes the audited company more accountable

          develops controls which can be used for purposes other than auditing

       Auditing does have many benefits but when conducted more frequently than necessary

it could have some negative aspects, such as problems in performance because of

preoccupation with the audit, production of too many reports, interruptions of the regular

operations, and consumption of resources including cost from unavailability of the system.
                                                  Database Security Policies and Procedures for DMCS 112

             Requirements for the Disaster Management Communication System

   Objectives of the auditing process for the DMCS are complying, informing, planning, and

executing. The audit needs to indentify regulations, laws, and standards of the industry and the

government, which must be obeyed. All parties involved in the audit need to be aware of these

regulations, laws, and standards. All auditing procedures should be planned and documented.

The auditing entity needs to be evaluated and reviewed.

       Several possible problems can occur for the DMCS during an audit. First, auditors may

not always have full access to the system because of unawareness of user names or restriction

on certain files. Second, when conducting an audit with computers there may be compatibility

problems. When computers are used for auditing, there is a possibility of files being damaged.

Lastly, when audit is conducted manually, auditors have to enter data into computers, which

increase the time span of the auditing process.

       The Disaster Management Communication System would benefit the most from the

automatic audit, which should be performed periodically to produce reports for system

integrity evaluation.

       For the DMCS, an auditing model that provides a detailed report should be

implemented. All auditable entities, such as users, actions, columns, and tables should be

inspected and a record should be kept for keeping the system secure and in compliance. The

audit process should also help administrators be aware of how the system is performing and be

prepared for proactive or corrective actions.
                                                         Database Security Policies and Procedures for DMCS 113

                     Part VIII Application and Database Activities Auditing

        Database auditing in SQL Server is implemented through triggers. There are two

methods for auditing data manipulation language (DML). The first one, DML changes, is usually

used by companies with sensitive data. Figure 8-1 illustrates the DML changes auditing

architecture. This method records all column values before or after the DML statement is

applied to the table. This audit can be executed on two different levels- row level and column

level. The row-level auditing records all values from all columns in a table and the column-level

auditing only records values prior to the modification. This type of auditing would be

appropriate for the Disaster Management Communication System considering the nature of its


                                                            Before                               Table being
                     Application         DML statement                        Peform statement
                                                            trigger                               acted on
                                                              Colomn values

                                                         Row /columns
                                                              are recorded


                                   Figure 8-1: DML changes auditing architecture

        The second method for auditing DML, DML action, tracks all activities performed on a

table and records actions before the DML statement is applied to the table. Figure 8-2 presents

the DML action auditing architecture.
                                                        Database Security Policies and Procedures for DMCS 114

                                                           Before                               Table being
                   Application        DML statement                          Peform statement
                                                           trigger                               acted on

                                                             Record action
                                                         data table

                                  Figure 8-2: DML action auditing architecture

       Whenever a DML statement is executed, trigger mechanisms (stored procedures) are

automatically activated. The purpose of the triggers is to capture what statements are being

executed according to criteria specified in the trigger condition. In SQL Server, triggers are

created using the CREATE TRIGGER DDL statement:

               create trigger audit_client

               on {client}

               [with encryption]


                        {{for | after | instead of} {[insert] [,] [update] [,] [delete]}


                            [{if update (address)

                            [{and | or} update (client_status)]



                                                  Database Security Policies and Procedures for DMCS 115

       Trigger conditions are used to set criteria based on which the trigger executes.

UPDATE() and COLUMN_UPDATED() are the two methods used for determining what columns

are being updated. We can use UPDATE(column) in an IF statement to check whether the

specified column is being updated or inserted into. If the column is being modified the

statement returns TRUE. With COLUMNs_UPDATED() in an IF statement we can determine

which columns are being modified.

       When using triggers we have to create two logical tables to capture data. DELETED

table stores values from before the DML update or delete statements were applied, while the

INSERTED table stores the values, which are result of the DML update or insert statements.

There are certain statements that are not allowed in triggers, such as alter database, create

database, disk init, disk resize, drop database, load database, load log, reconfigure, restore

database, and restore log.

       When a company needs to keep track of modified or deleted rows, the historical model

is used. For this auditing to be implemented, a history table needs to be created:

               use disaster_management

               create table client_history

                       (history_id not null primary key,

                      client_id int,

                       first_name varchar(20),

                       last_name varchar(20),

                       street varchar(20),

                       city varchar(20),
                                                  Database Security Policies and Procedures for DMCS 116

                       state varchar(2),

                       zip int,

                      phone int)


       After creating the HISTORY table, which saves values in a state they were before

modification or deletion, we can create a trigger on the Client table in order to insert the

original values into the HISTORY table:

               create trigger Audit_client

               on client

               for update, delete


                      insert into client_history (client_id, first_name, last_name, street, city,

state, zip, phone)

                      select * from deleted;


       The application actions model audits changes to data as well as actions performed

within the application. A table needs to be created to store all the changes.

       Database activities can be selectively audited and trails of these activities can be created

and kept for further reference. In addition to DML statement auditing, DDL (data definition

language - create, alter, drop commands) statements and database events can be audited as

well. The auditing is performed through triggers. “Triggers would track data changes, grant
                                                   Database Security Policies and Procedures for DMCS 117

privileges, and create database objects,” (Afyouni, Database Security and Auditing, 2005, p.


        SQL Server 2008 includes the Profiler tool that provides a user interface for auditing

events, such as:

                  End user events – SQL commands, login/logout, enabling of application roles

                  DBA events – DDL statements, configuration

                  Security events – grant/revoke/deny, login user/role add/remove/configure

                  Utility events – backup/restore/bulk insert/bcp/dbcc commands

                  Server events – shutdown, pause, start

                  Audit events – add audit, modify audit, stop audit

        The Profiler also provides the opportunity to adjust the audit for each event individually.

We can inspect for any of the following:

                      Date and time of the event

                      User initiating the event

                      Type of event

                      Success or failure of the event

                      Origin of the event

                      Name of object being accessed

                      Text of the SQL statement

        The profiler can be used for auditing only for queries that run while the profiler is

running. It can track how many queries are being executed, how much time they take, and
                                                  Database Security Policies and Procedures for DMCS 118

which database is executing which query. Profiler can perform an audit on the user level or an

audit by query type. The disadvantage of using the SQL Server Profiler is that it can affect query


       Audit needs to be enabled in SQL Server before any security audit activity can be

performed. Security events can be audited on success, failure, or both. To enable audit in

Enterprise Manager:

           1. In Enterprise Manager, expand the desired SQL Server group

           2. Right-click on the desired server

           3. Click on Properties

           4. On the security tab select the desired security level

       To start a new audit trace, open Profiler from the Tools menu in Enterprise Manager and

click on New from the File menu. For the new trace, the following should be provided on the

General tab:

              A name for the new trace

              The server which is going to be audited

              The base template

              Where audit data should be saved (a file or database table)

              A stop time unless the trace needs to run indefinitely

       The events that are going to be audited in the trace, as well as their category, are

specified on the Events tab. For auditing DDL statements, again on the Events tab, from the

Objects category select Object: Created and Object: Deleted. This will audit every CREATE and
                                                  Database Security Policies and Procedures for DMCS 119

DROP statements. Operations performed on the database can be audited by selecting events

from the Database category on the Events tab. SQL Server can audit database errors by

selecting the events from the Errors and Warnings category on the Events tab of the Trace

Properties window.

       Auditing is the final part of database security and ensures that the system is working the

way it is supposed to; and that government regulations and company policies are observed. A

drawback of auditing is the impact it has on performance. Auditing can be resource and time

consuming and it needs to be performed only when needed and with a reasonable number of

audited objects, or the process could have a serious impact on the system performance. It is

suggested that audited information is kept manageable in order to “minimize performance

impact on the execution of audited statements and the size of the audit trail, making it easier to

analyze and understand,” (Huey, 2007). This means that we should weigh every reason for

auditing and then choose an appropriate auditing strategy that is going to audit the minimum

but sufficient number of entities and is not going to be overload for the system. Another

guideline for auditing is to audit suspicious database activities. This might require auditing a

broader area (more users, objects) at first because we do not always know where to focus.

Second, the audit trail should be protected “so that audit information cannot be added,

changed, or deleted without being audited,” (Huey, 2007).

             Requirements for the Disaster Management Communication System

       For auditing changes to data and actions performed within the application, the

application actions model should be used. A table called service_request, which is to store

changes, needs to be created as follows:
                                                    Database Security Policies and Procedures for DMCS 120

                 use disaster_management

                 create table service_request

                        (service_id int not null primary key,

                         client_id int not null,

                         request_date datetime,

                         deadline datetime)


          The next step of implementing the application actions model is to create the audit


                 create table app_audit_actions

                        (action_id int not null primary key,

                         action_des varchar(255))


                 create table app_data_dictionary

                        (class_id int not null primary key,

                         class_desc varchar(128))


                 create table app_audit_trial

                        (audit_trial_id int not null primary key,

                         class_id int not null references app_data_dictionaty (class_id),
                                                 Database Security Policies and Procedures for DMCS 121

                      action_id int not null references app_audit_actions (action_id),

                      object_id int not null,

                      reason varchar(255) not null,

                      CTL_UPD_DTTM datetime not null default getdate(),

                      CTL_UPD_USER varchar(128) not null default user)


       Auditing database activities is critical for the DMCS. It needs to track and record any

changes made to the database. For example, table change events, such as drop table and alter

table can be tracked using the following approach:

              create trigger tr_tableChange

              on disaster_management

              for drop table, alter table



                      print ‘dropping table not allowed’


       Application and database activities audits help system administrators keep track of the

actions performed within the system and monitor for any unauthorized processes being

executed that can potentially harm the integrity of the database. Audits for the DMCS should

be performed regularly in order for the system to remain intact and secure.
                                                    Database Security Policies and Procedures for DMCS 122

Afyouni, H. A. (2005). Database Security and Auditing.

Beauchemin, B., Berglund, N., & Sullivan, D. (2005, June 28). SQL Server password policies and

        credentials. Retrieved June 06, 2010, from searchsqlserver:


Buecker, A., Andres, P., Paisley, S., Reed, B., dos Reis, R. A., & Srihari, P. (2008, July). Enterprise

        Security Architecture using IBM ISS Security Solutions. Retrieved May 20, 2010, from

Byfield, B. (2005, November 22). Nine principles of security architecture. Retrieved May 20,

        2010, from

Fogie, S. (2004, January 01). Security Reference Guide. Retrieved May 25, 2010, from informIT:

How to: Create a SQL Server Login. (n.d.). Retrieved June 02, 2010, from msdn:

Huey, P. (2007, July). 7 Auditing Database Activity. Retrieved July 07, 2010, from


Page, R. (2007, February 20). SQL Server Security Cribsheet. Retrieved June 05, 2010, from



Peterson, G. (2007). Security Architecture Blueprint. Retrieved May 21, 2010, from
                                                Database Security Policies and Procedures for DMCS 123

Redman, M. (2008, November). SQL Server Best Practices – Implementation of Database Object

       Schemas. Retrieved June 03, 2010, from msdn:


Secure Computing: Best Practices for Windows XP. (2009, March 10). Retrieved May 25, 2010,

       from Stanford University:

Microsoft. Security Overview for Database Administrators. (2008, July). Retrieved November 8,

       2010, from Microsoft:


The Interactive Security Manual. (n.d.). Retrieved May 20, 2010, from

Thomas, J., & Catanzano, J. (2005, November). SAP with Microsoft SQL Server 2005: Best

       Practices for High Availability, Maximum Performance, and Scalability. Retrieved May

       04, 2010, from

Oracle. Virtual Private Database. Retrieved June 18, 2010, from Oracle:

                                          Database Security Policies and Procedures for DMCS 124

                                    Appendix B

                           Installation of SQL Server 2008

SQL Server 2008 running.

Shared By: