Operational Problems Due to Poorly Designed Database - DOC by xpp14690


Operational Problems Due to Poorly Designed Database document sample

More Info
									Database Security in the Real World: Part One
Nick Wienholt

In Part One of Database Security in the Real World, data security was examined from a
high-level viewpoint, and the threats that a typical commercial data store faces were
examined. In Part Two, threat modelling of database attacks is completed, followed by a
detailed drill down into design and implementation details that effect data security.

Threat Modelling
Threat modelling involves defining the possible attacks that can be launched against a
particular system. The STRIDE technique of threat modelling divides threats down into
six distinct categories: Spoofing, Tampering, Repudiation, Information disclosure, Denial
of service and Elevation of privilege. In Part One (which appeared in the January 2004
issue), the first three categories where covered. This section covers the remaining three
threat categories.

Information Disclosure
For most commercial data stores, information disclosure is the top threat. Good
operating procedure can overcome data loss or modification, but inappropriate data
disclosure is much harder and more expensive to deal with after it has occurred. The
consequences of data disclosure can result in legal action, loss of competitive advantage,
loss of customer confidence, and negative media publicity. The successful capture of
personal and financial data by a hacker can create a media feeding-frenzy, and, regardless
of the actual harm that the disclosure causes, consumer furry and indignation is the
inevitable consequence of this type of incident.
           The threats and countermeasure for the main flow of data through a system are
generally well-defined, and protected by appropriate technologies like SSL. A customer
logging into an e-commerce site, selecting items, and then entering a credit card number
to purchase the items has threats like eavesdropping that are well known, and the counter
measures are also well understood and implemented. Though information disclosure
along these normal data channels is still possible, and doubtlessly occurs within poorly
designed and operated systems, the greatest vulnerability to attack comes once data
leaves this well-understood channel.
           Log and audit records are a prime example of data that is often less secured that
the main data store. While the details of the successful attack on CD Universe that
resulted in the capture of 350000 credit card number have not been fully disclosed, it is
believed that logging information stored in plain text that contained credit card data was
the source of the breach.
           Logs often contain sensitive data, and even if the normal logging mode of a
system contains data that is not confidential or sensitive, a registry switch or
configuration file setting can often turn verbose logging on, and this can be used by a
malicious insider to capture data they would not otherwise have access to. The
destination of log files also has security implications – if an attacker can configure log
files to be written to a known location in the file system location from which web pages
are served, sensitive data can be recovered, or database configuration settings can be
exposed, and this can be used to launch further attacks.
           Error reports also represent a potential point of attack against a system. If an
attacker can cause an error to occur, such as by disabling client-side scripting and
submitting invalid data that would normally be caught by client-side checks, the error
information returned to the client or written to an unsecured log file can disclose vital
information about the configuration of the database.
           Reporting represents another avenue of information disclosure that is rarely
considered. Many systems contain reporting functionality that displays aggregate data
related to individual records that are confidential. The aggregate data, which may contain
such statistics as average salary or the count of the number of people with a particular
health issue, is generally considered safe because the individual records are assumed to
be unrecoverable from the summary data. Various statistical techniques exist to allow the
original records to be recovered, and this risk is increased if reporting application allows
user defined filtering and pivot-table functionality. The obvious attack for reports that
allow user-defined filters is to filter the data using know attributes of a record so that the
aggregate data displayed related to a single record. To provide a concrete example of
this, if the average salary of employees can be filtered on location, educational
qualifications and tenure of employment, determining the salary of any particular
employee can be accomplished by finding out publicly-available pieces of data and then
using them as filter clauses.
           Even if a reporting application does not allow user-defined filtering, the ability
to modify some attributes of a data record can allow other, more secure attributes of a
record to be determined. Using the average salary example again, if a report shows
average salary broken down by geographical area, a user who has permission to view and
modify addresses, but does not have permission to view and modify salary information,
determining a particular person‟s salary can be achieved by moving the person of interest
in and out of particular geographical areas contained in the report. This form of attack
requires some reasonable competent statistical skills, but if the attacker is determined
enough, determining the data of interest is a matter of time and patience.
Dealing with data and audit files is reasonably straight forward. These files need to be
secured to the same level as the primary data source, and should not sit on the hard-disk
of front-end servers in clear text. The configuration settings that control the level and
destination of log output also require appropriate security settings. Logging in
production systems is a complex tasks – in addition to the security considerations, log
archiving and clean-up needs to be catered for, logging usually needs to be done
asynchronously to avoid performance impacts, and multiple systems can need to be write
to a log at the same time. Given these requirements, logging is a system feature that
requires proper design and requirements, and should not be thrown into a system at the
last moment. For system using the .NET Framework, the Microsoft Enterprise
Instrumentation Framework (EIF), which was covered in Issue 11, and the new Logging
Application Block, which sits on top of EIF, is a good solution to the logging problem.
           Controlling data leakage through reporting is a harder problem to solve.
Inference control is the formal name given to the prevention of information disclosure
from statistics, and is an active area of academic and commercial research. Material on
inference control typically assumes a tertiary-level knowledge of statistics, and can be
difficult to consume for the average architect or developer charged with building a secure
reporting tool. The range of techniques that can be used to infer data about an individual
from a aggregate are complex and varied, which means the skill required by the attacker
are considerable, but also means that there is no simple check that can be done to
determine if a particular aggregate is safe. For sensitive data, the safest course of action
is to exclude the data from reports, particularly if the report allows user-defined filtering.
           Techniques like limiting the display of aggregates that apply to groups smaller
than a particular size, and calculating aggregate based on a random sample of the source
data reduce the possibility that data can be inferred from aggregate sources. For
commercial applications, going to these lengths may not be warranted, but for some
situations do warrant these measures. The National Census, which is conducted by the
Australian Bureau of Statistics (ABS) every five years, collects and reports on sensitive
data on all Australians, and has obvious requirements of inference control. One of the
statistical products produced based on census data is the Household Sample File (HSF),
which provides detailed data that can be purchased on CD-ROM.
           The steps taken to prevent the identification of individuals in the HSF give an
indication of the lengths that can be taken. A full report details the sampling techniques
used in the product of the HSF is available from the ABS web-site (www.abs.gov.au),
and details the following steps that were taken in the report‟s production:
                The data only covers one percent of households, so that probability that
                  a particular person will be used to derive the aggregate figures is very
                The data used for inclusion was randomly selected.
                Some data fields such as occupation are collapsed to less detailed
                All geographical regions in the data must have a minimum population if
                  250,000 residents.
                Data within certain records was modified.
                Certain fields where removed.
                Conditions of release of the data explicitly forbid attempts to “identify
                  particular persons or organisations” or “match the information with any
                  other unit level list of persons.”

Denial of Service
Denial of service (DOS) attacks prevents other users from using or accessing a system.
Though more of a threat to front-end machines likes web servers, DOS attacks are
possible against a data store. Possible attacks include filling a data store to capacity,
convincing a system that it is under attack so automated counter measures shut down
access, activating account lock-out feature by deliberately executing bad logon requests,
physical attacks on the hardware stores the data, and executing a query on the database
that uses an excessive amount of memory, processor or locks.
          Dealing with DOS attacks can be achieved by throttling the amount of work that
a particular user can schedule on the database and use timeouts to deal with requests that
take much longer than expected. System shutdown during a detected attack is harder to
deal with and is a balancing attack between the other threats. If it is too easy to trip the
defence mechanisms, DOS attacks become easy to launch, while a system that is too
conservative can be bypassed by hackers.

Elevation of Privilege
Elevation of privilege is a class of attack in which an attacker attempts to convince the
system that they are authorized to perform an action that they should not normally be able
to perform. In terms of database systems, there is typically an application defined
security model and an operating system-level security model. The application defined
security model relies on application code and SQL statements for enforcement, and bugs
in these components can be used be attackers to slip into a role with greater privileges.
SQL injection attacks (discussed further below) are the main vulnerability that can be
used to subvert application defined security models, and a dedicated hacker can
methodically try each user input field attempting to inject SQL statements that subvert
the expected output.
        The process identity under which the database server executes can often have
greater privileges than the identity of the user than is leveraging the database. If the
database user can get the database to execute operating-system level commands,
elevation of privilege attacks are possible. SQL Server has an extended stored procedure
called xp_cmdshell that allows operating system commands to be run under the identity
of the service's user account if the database user is a member of the sysadmin fixed server
role. For applications that do not employ the principle of least privilege and connect with
the „sa‟ account, this attack can be launched if dynamic SQL is sent to the database for

Application Design
The choice of whether to propagate user identity from the front- and middle-tiers of an
application through to the database server is the most fundamental security design
decision for modern n-tier applications. If user identity is propagated through to the
database server, each unique user to the system will have their own distinct database
connection which is based on their operating system identity. In contrast, the alternate
security model involves authenticating the user at the entry-point to the system, and using
a generic database connection identity that is constant for the entire application. The two
different database connection models are shown in Figure 1.
Figure 1. Database connection models

        Before focussing on the security implications of either model, it is worth briefly
covering the non-security consequences of both designs. The Constant Identity model
generally results in the requirement to write more code and complete more upfront
design, as the security checks and auditing that is off-loaded to the operating system in
the Identity Propagation model need to be manually coded.
        In contrast to the Constant Identity drawbacks, which are rather minor, the non-
security drawbacks of the Identity Propagation model are much more significant. The
drawbacks are of Identity Propagation occurs in a number of areas, particularly
administration and security. The administrative problems are almost exclusively on the
network administration side, so developers may not be initially concerned with the
difficulties. Dealing with angry operational staff during the rollout of the application can
change this concern level, and may result in application changes to simplify deployment.
        The administrative complexity comes from the need to have every system users
associated with a valid operating system account, and the requirement to flow these
operating system account credentials between the tiers of an application, which may cross
firewall and organisational boundaries. For intranet applications, the operating system
account issue will not be as severe, as a homogenous client operating system environment
will be more common, and users are likely to be behind the firewall, or tunnelled through
it using a VPN. If the application is exposed on the internet, the account issue becomes
much more difficult to deal with. On the Windows platform, IIS can be configured to
require a valid Windows account, or to map a client-side digital certificate to Windows
account, but both these options require user accounts to be assigned to users who would
not normally be given a logon onto a particular domain. Integrated Windows
authentication in IIS also requires additional ports to be open on a firewall, which is an
action that many network administrators are hesitant to undertake.
        The user account issues are somewhat mirrored at the database server, where an
account needs to be created that corresponds to a particular operating system account. A
database account based on operating system groups can alleviate some of the
administrative hassles at the database side, but if the full benefit of the Identity
Propagation model is to be realised, the database must be able to identify a particular
system user to provide individualised authorization, auditing and data management.
        Analysing the security merits of the two different identity propagation models is
an interesting exercise. The Identity Propagation models feels more secure, and employs
a number of security best practices like using the mature operating system and database
security model to handle authorization and authentication tasks that have to be otherwise
dealt with in application code. If the performance and operational issues of Identity
Propagation are acceptable, it‟s use as the most secure alternative looks like the obvious
decision to make.
        The Identity Propagation has a significant security flaw that arises if a server is
compromised. With Constant Identity, data stores are accessed with an account of know
permissions, and these permissions should be the minimum set required to fulfil the tasks
of the system. This is not the case with Identity Propagation, where systems may be
accessed using highly privileged accounts. If the CEO of a company is accessing an
application that uses Identity Propagation, the identity that is propagated through the
system is that of the CEO. If the application has been compromised, the CEO‟s identity
can be used to access other services such as the payroll and HR system, and complete
highly privileged tasks like authoring pay rises. Windows has a range of security settings
that can be used to control access to operating-system level functionality during
delegation, as shown in Figure 2, but these options have not be previously available to
control application-level accessibility.
Figure 2. Delegation control wizard

Windows Server 2003 offers a constrained delegation model that allows a particular
computers delegation authority to be confined to a specified group of services.
Constrained delegation only worker on Windows Server 2003 domains, and the per-
machine configuration granularity will be insufficient for many applications. Even with
these advances, the delegation/ impersonation model still poses a number of
vulnerabilities, and the administrative overhead will make it an unattractive option for
most systems.

Security Risks in Implementation
The focus up to this point has been on issues related to operational and design issues. In
this section, security issues that arise due to implementation decisions are examined, and
techniques for avoiding or minimising the risk are covered.

SQL Injection
Structured Query Language (SQL) injection attacks are one of the most serious and
common security vulnerabilities that can be introduced during the implementation of a
system that uses a database. SQL injection involves appending or modifying SQL
textural commands using data from user-input fields. Consider the following C# code
which prompts a user to enter their user ID and then uses this ID to perform a database
Console.WriteLine("Please enter your user id.");
string sql = "select [Name] from [Users] where [ID] = "
 + Console.ReadLine();
//Execute query and display name or error message
//if ID does not exist

The code works fine if the user enters a numeric value, and will result in an exception if
data that does not correspond to a valid ID is entered. Assuming the exception is caught,
and the user if prompted to enter the information again, the code could be consider
reasonably robust and secure. Serious security problems occur if the user enters input
that is a valid SQL command. Entering “0 or 1=1” will result in the SQL statement being
executed by the database being “select [Name] from [Users] where [ID] = 0 or 1=1”,
which will return the name of every user in the table, as the final statement in the where
clause (1=1) will be true for every row. If the return of a record is used to determine if a
login attempt is successful, injecting a SQL statement into the where clause that will
always be true can be used to successfully login without valid credentials.
         The damage that can be done by SQL injection is not confined to modifying
existing dynamic SQL statements. Multiple SQL statements can be executed at the same
time, and the damage that can be done by these dynamic statements is only limited by the
rights of the account that is being used to connect to the database. If the connection to
the database has been made with a highly privileged administrator account, dynamically
injected SQL can drop tables, databases, or even call out to operating system commands
to perform tasks like creating new users. For the user lookup code shown above, entering
“0 EXEC master..xp_cmdshell 'net user test testpass /add'”
followed by “0 EXEC master..xp_cmdshell 'net localgroup /add
Administrators test'” will result in a new Windows administrator account
called „test‟ with a password of „testpass‟ being created. The 0 at the start of the
commands simply completes the original SQL command, and the xp_cmdshell executes
the command-line statement in the single quotes, which adds the local user in the first
statement and makes it a member of the administrators group in the second statement.
         Combating SQL injection can be accomplished in three ways: manually checking
the input data, using parameterised dynamic SQL, or by using stored procedures. Manual
checks are the least robust technique, and require a complex regular expression
evaluation to differentiate between valid text and embedded SQL commands. A poor
implementation can result in false positives on legal text like O‟‟Reily, where the use of
the two consecutive single quotes allows a single quote to be used in a character-literal,
and Jones-Smith, where the hyphen in the surname can often be mistakenly rejected as an
attempt to begin a SQL comment. Missed positives are even more damaging then false
positives, and can be hard to detect without building a full SQL parsing engine.
Replacing all occurrences of single quotes with double quotes can stop many SQL
injection attacks, but this technique is not entirely effective. Allowing single quotes in a
text field that is used by an application that employs dynamic SQL can result in second-
order SQL injection attacks, where SQL commands are deliberately inserted into text
fields in the hope that will be reused latter without the use of special handling. A more
detailed explanation of this attack can be found in “Advanced SQL Injection In SQL
Server Applications” by Chris Anley
        Parameterised SQL is an ADO.NET feature that allows dynamic SQL to use the
features of stored procedures by explicitly separating field data from SQL statements. A
re-write of the dynamic SQL shown earlier in to use parameterised SQL looks like:

Console.WriteLine("Please enter your user id.");
string sql = "select [Name] from [Users] where [ID] =
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Parameters.Add("@id", SqlDbType.Int, 0);
cmd.Parameters[0].Value = Console.ReadLine();
//Execute query and display name

ADO.NET will actually offload the resolution of the parameterised query down to the
actual database if possible, which means that the SQL statement becomes a temporary
stored procedure in effect.
         Using traditional stored procedures brings the greatest security when dealing with
the threat of SQL injection attacks. Not only do stored procedures have the benefit of the
parameterised SQL approach, but by maintaining a stricter separation between an
application and the database, the security settings of the connection can be increased. To
execute a stored procedure in SQL Server, the database connection only requires execute
permission on the stored procedure, and does not need any rights on the underlying tables
that the stored procedure uses. By applying the principle of least privilege and removing
all rights of the underlying tables, the damage that can be done by an application that
does contain security vulnerabilities is limited.
         Stored procedure do not entirely remove the possibility of SQL injection attacks,
and if a stored procedure uses string operations to dynamically build and execute SQL
commands, the possibility of SQL injection remains.
         In contrast to most implementation techniques that improve security, using stored
procedures also has a significant benefit for performance and maintainability. Stored
procedures enforce a clean separation between application code and the underlying
database schema that allows either element to be optimised or upgraded independently.
The traditional performance benefits of stored procedure pre-compilation no longer apply
with SQL Server 2000, but the use of stored procedures allows a DBA to fully tune a
database using indexes, de-normalisation and partitions without being concerned about
breaking application code.

Database Connections
SQL Server supports two different forms of connection authentication – SQL Server
authentication and Windows authentication. Windows authentication relies on the
Windows identity of thread that is making the connection call, while SQL Server
authentication relies on a username/ password combination. Windows authentication is
the most secure option for database connection, but SQL Server authentication is
generally simpler to deal with from an administrative viewpoint. The security shortfalls
of SQL Server authentication come from the need to store and transmit the password used
to connect to the database, and the lack of password security measures like account lock-
out, password expiration and password strength control that inbuilt Windows accounts
offer. Network security measures like IPSec allow the secure transmission of SQL
Server connection details, and the strength of the SQL Server passwords can be manually
set to the desired level, but the secure storage of the SQL Server password represents a
problem for which there is no single solution that is globally superior.
           The use of the Data Protection API (DPAPI) to generate an encrypted version of
the SQL connection string that can only be decrypted using the same machine or user
account is a reasonable option for protecting the username and password if SQL Server
authentication is used. DPAPI functionality is not exposed by types in the
System.Security.Cryptography namespace, but a managed wrapper is available at
us/dnnetsec/html/SecNetHT07.asp. Using this wrapper, a connection string encrypted
using the machine store can be generated by the following function.

public static string EncyptUsingMachineStore(
  string plainText){
  DataProtector dp = new
  UTF8Encoding utf8 = new UTF8Encoding();
  byte[] cipher = dp.Encrypt(
    utf8.GetBytes(plainText), null);
  return Convert.ToBase64String(cipher);

Decrypting the connection string is accomplished using the reverse procedure:

public static string DecyptUsingMachineStore(
  string cipherText){
  DataProtector dp = new
  byte[] plainTextEncoded =
  UTF8Encoding utf8 = new UTF8Encoding();
  return utf8.GetString(plainTextEncoded);

        The same-machine or same–user requirement of DPAPI means that it is not an
appropriate technology for storing sensitive long-term data like credit card numbers, but
DPAPI can play a vital role in this process. The main challenge for keep encrypted data
stored in the database is managing the key that can decrypt the data. DPAPI can be used
to encrypt the key used to decrypt the persistent data, which adds a further layer of
security to the encryption process.
        Given the extra effort required to manage the username and password associated
with a connection string that uses SQL Server authentication, it is worth considering why
it would be preferred over Windows authentication. In a number of deployment
scenarios, Windows authentication will simply not work. If there is a firewall between
the database and application that blocks Windows authentication, SQL Server
authentication is the only viable option. Even if Windows authentication is technically
possible, setting up the correct accounts so that a server process like ASP.NET can
connect using Windows authentication can be an administrative headache. By default
ASP.NET runs under a local user account with an auto-generated password. To use this
account to connect to SQL Server, there are a number of options:
             Change the ASP.NET account password to a known value, and create a
                mirrored account with the same password on the database server.
             Modify the ASP.NET configuration so that it runs under a custom local
                account that is mirrored on the database server.
             Modify the ASP.NET configuration so that it runs under a custom domain
             Use Service Components (COM+), and run under an identity that can be
                authenticated on the database server.
In a large corporate environment with stable staffing and with resources dedicated to
maintaining software systems, setting up the correct accounts for Windows authentication
is not a problem. Many clients will not exhibit the stability and staffing levels necessary
to maintain these settings, which can make Windows authentication fragile in
deployment. Organisations often lack a formal process for re-building and re-configuring
server machines, and can often unknowingly break an application using Windows
authentication. Deciding whether it is appropriate to live with a less secure solution to
accommodate these issues is left to the reader, and it is recommended that a case-by-case
evaluation of the client‟s competence take place before committing to Windows

Data security is a large and complex topic. Non-technical issues like staff training and
organisational attitude is as significant as coding techniques to avoid SQL injection
attacks, and security needs to be treated in a holistic manner to be effective. The key to
secure data management system applications is being conscious of security issues,
incorporation of security principle during the design, development and deployment of an
application, and effectively monitoring a system to provide timely notice of attacks
against the system.


To top