What is responsible for determining what users can do with the database?

Operation of data management utilities

DBAs spend a lot of time and energy planning for and executing data management utilities. These include database reorganization, backing up data files, updating statistics, building or rebuilding indexes, checking constraints, and rebinding plans.

Keeping a large amount of inactive data in operational databases will obviously increase the execution time for these functions by a proportionately linear amount. The presence of this data may trigger the need for execution of these utilities more often, since the DBMS cannot manage file allocations, free space management, or perform other functions as effectively.

Dual Accounts

When the DBA needs access to the server’s console, it is recommended that a second account be set up and used to access the server’s console. This is recommended for a couple of reasons. First, this allows the systems administrators to selectively allow the DBA to have access to the SQL Server’s console as needed. Second, it will prevent the DBA’s roaming profile from being loaded onto the server’s system drive. We want to keep user profiles off of the SQL Server’s hard drive so that the amount of extra data sitting on the server’s OS drive is minimal, and to keep the risk of any viruses on the server to the minimal. This does add a layer of complexity to the system as the DBA now needs to keep track of two usernames and passwords and the DBA will need to ensure that this admin account has the needed rights to both the database and the server so that the needed operations can be performed.

Story time

Best Practices Only Work When They Are Actually Followed

One particular company that I worked for, whose name shall be protected to protect the stupid, took security to the extreme. It also took efforts to bypass security to the extreme as the security made getting any work done within the limits of the security next to impossible. Every person within the IT Department had two logins – their normal login, which was first initial followed by last name, and a second login – the same as the first with an “-a” after the username. The employees used the normal account to log into their workstations and the “-a” account to log into the servers. Normal accounts were not allowed to access into servers, and the “-a” account was not allowed to access the workstations. Normal accounts were not permitted to be admins on workstations, and all software had to be done by the help desk. On paper this security looks great, providing total separation between the servers and the workstations.

In practice, however, a few things were done to make it a little easier. The first thing done was, at some point in the past, the domain account that the SQL Server Services was run under was added to the “Domain Admins” group. This was done so that the SQL Server’s would also have admin rights on their servers and so that jobs that needed to access remote machines would always have the network access they needed.

When I started working there, after I got my workstation, I was given the username and password for the SQL Server, and I was told that the account had domain admin rights, so I could use it to give myself admin rights on my workstation and install whatever I needed. I could then also give my “-a” account admin rights to my workstation so that I could connect to the admin share on my workstation from the server to get scripts and stuff to and from my workstation, effectively bypassing the security boundary that had been drawn between the workstations and the servers.

Because of this separation in the accounts, the database developers had a hard time getting things done. They liked to use the debugging features of Microsoft SQL Server 2000’s Query Analyzer that requires having admin rights on both the workstation and some higher end rights on the server as well. But in order for them to connect to the database engine they had to launch Query Analyzer under their “-a” account which did not have admin rights on the workstation, and because they were database developers they did not have sysadmin rights on the database server either. As they were database developers and not DBAs, they did not have the password for the SQL Service account and so they did not have a way to get the permissions they needed.

Because of the security, getting anything done in any sort of timely manner was basically impossible. Everyone in the DBA group used the SQL Service account to give themselves access to get done what they needed to get done, effectively making the security next to useless.

What made the security at this company the hardest to deal with was the fact that it followed a white paper from Microsoft to the letter on how domain security should be set up. This would normally be fine, except that there was no consideration taken as to how this security would affect the way the company needed to conduct its business. When designing a security policy, a balance needs to be found between a locked down environment and the employees’ needs to get their jobs done. Security should protect with as little interference as possible.

Constraints and Referential Integrity

DBAs also configure databases with constraints, which are machine-applied rules governing the values that can be placed in the data tables. Some constraints, such as UNIQUE or NOT-NULL, operate on a single record. Other constraints are checks on values between tables. A common example of a multi-table constraint is a foreign key constraint, which ensures that a record will not be loaded in a table if the values in the foreign key column(s) cannot be found in the primary key column(s) of the parent table.

DBMS-enforced constraints consume processing power and therefore can slow down warehouse data loads considerably. For this reason, many DW/BI teams do not employ DBMS-enforced constraints in their databases, relying instead on the ETL to ensure that child records are not inserted without corresponding parent records. When the records in a database completely agree with the mandatory primary/foreign key constraints specified by the data modeler, the tables are said to have referential integrity.

Data Control Language

Database administrators should be familiar with the DCL aspect of SQL. The GRANT, REVOKE, and DENY statements make up DCL. SELECT, UPDATE, INSERT, and DELETE permissions can be specified on views and tables. You can get more granular and determine permissions on a column-by-column basis as well. EXECUTE permissions on stored procedures and functions can be modified using these statements as well. DCL operations such as CREATE VIEW and ALTER TABLE can also be granted and revoked to certain users.

Let’s say we don’t want to give all users access to personal employee information such as home addresses. Unknown users connect through an anonymous guest account in the public role of the Northwind database. We can revoke access from the public group on the Employee table. Make sure you are connected to the database as a database owner:

REVOKE ALL ON Employees TO public

We still want users to obtain a list of employees, so we create a view listing only public information:

CREATE VIEW EmpShort

AS

SELECT EmployeeID, FirstName, LastName, Title

FROM Employees

SELECT access must be granted to the public group on the new EmpShort view. This will allow members of that role to query the view but not modify it:

GRANT SELECT ON EmpShort TO public

In order to test this security, we can create a couple logins and add them to the public role. You should be logged in as a database owner and defaulted to the Northwind database in Query Analyzer. The sp_addlogin stored procedure creates a login account on the SQL Server. The first parameter is the username and the second is the password, followed by the default database. The login does not have access to any databases yet. It needs to be granted access to the Northwind database. This is accomplished with the sp_adduser stored procedure, which takes the login name that was specified in the sp_addlogin statement. The next parameter is the login name that is specific to the Northwind database. You could have a login for Northwind different from the server login. Most of the time, the two will be the same. The last parameter is the role the user will play in the database:

EXEC sp_addlogin ‘mike’, ‘mikepass’, ‘Northwind’

EXEC sp_adduser ‘mike’, ‘mike’, ‘public’

EXEC sp_addlogin ‘chris’, ‘chrispass’, ‘Northwind’

EXEC sp_adduser ‘chris’, ‘chris’, ‘public’

Now you can log in as mike in Query Analyzer and attempt a SELECT against the Employees table. An error will be generated, reporting that the SELECT permission has been revoked. Instead, try using a SELECT statement against the EmpShort view. This will return a result set.

This works well if the all members of the role have uniform access. There could be a case in which you’ll want to deny access to a certain member of the role without restricting access to the rest. The DENY statement fulfills this need:

DENY SELECT ON EmpShort TO chris

The user with the login chris is still a member of the public role but does not have the same level of access. Try logging in as chris and using a SELECT statement against the EmpShort table. An error will be displayed, showing that permissions have been revoked. This is particularly useful if you grant security permissions to an NT group and you want to deny access to a specific member of that group. The DENY statement does not require that the user be removed from the NT group in order to deny access to a specific member of that group.

Dual Accounts

When the DBA needs access to the server’s console, it is recommended that a second account be set up and used to access the servers console. This is recommended for a couple of reasons. First, this allows the systems administrators to selectively allow the DBA to have access to the SQL Server’s console as needed. Second, it will prevent the DBA’s roaming profile from being loaded onto the server’s system drive. This does add a layer of complexity to the system as the DBA now needs to keep track of two usernames and passwords and the DBA will need to ensure that this admin account has the needed rights to both the database and the server so that the needed operations can be performed.

Story Time

OS Rights Needed by the DBA

Technically a database administrator (DBA) doesn’t need many rights to the SQL Servers operating system. This is because the DBA should not be managing the operating system that runs the Microsoft SQL Server Service. The management and patching of the base operating system should be handled by the members of the systems administration team and not by the DBA team.

The DBA may, however, from time to time need rights to the Windows OS, which will allow viewing performance metrics. If the company has a performance monitoring solution such as Microsoft Operations Manager (MOM), the DBAs can grant the Microsoft Operations Manager rights to view the captured performance data. However, at times the DBAs may need the ability to collect real-time performance data directly from the performance monitor. The right to use a performance monitor remotely can be granted by adding the DBAs to the “Profile system performance” local system right. This local system right grants users that have the right the ability to connect to the system remotely and gather performance monitor data.

Many of these performance metrics can be accessed via the “sys.dm_os_performance_counters” dynamic management view from within the SQL Server instance. This allows the DBA to access the performance monitor data and to log it into a table or simply view the record set by querying the needed information from the dynamic management view without having any special operating system level permissions.

15.5.3 Post-Extract Functions

Every experienced database administrator knows that after you have deleted a bunch of data from a database, you probably need to reorganize the operational database and the indexes on them, generate image copies of the database files reorganized, and, based on the DBMS, run database statistics and application plan rebind. This can add a lot of processing to the weekly archive extractor execution.

Clearly this should be done after the initial extractor execution up to the point of stable volumes. After that, the argument can be made that it is not necessary.

The argument goes that you have only deleted data and thereby created free space throughout the database files. Your application programs will not run more slowly if you do not reorganize. They might run faster for a while. However, you expect new data to be inserted over the next time interval (for example, the next week) that will use up the free space you just created. The volume coming in each period is expected to be about the same as the volume going out. If you simply leave the databases alone, the performance should stay about the same.

If you choose not to do reorganization after a run, it might be helpful to at least do an image copy. These can usually be run while online activity is going on. They will shorten any subsequent recovery operations considerably.

It is important that you never do a recovery to a point in time prior to the extractor run. If you do that, the data moved to the archive will reappear in the operational database and be in both places at the same time. You do not want this situation to occur.

Another important point to make is that executing utility functions to clean up after an extractor execution does not need to happen immediately after the extraction. The database can remain in online status and the functions executed at any time in the future.

Databases and Requirement 8.5.16

Databases contain lots of information valuable to a hacker, yet the security around databases is sometimes the worst in the entire enterprise. Many compromises occur because of administrator-level accounts with blank passwords. Requirement 8.5.16 has two testing procedures. Procedure 8.5.16a requires assessors to verify that all users are authenticated prior to being granted access to the database, direct user interactions with the database are done through programmatic methods such as stored procedures, and that direct queries to the databases are restricted to administrators only. If you have power users that log into your database directly instead of going through an application, take any common actions they may perform and put them into stored procedures or functions, and then restrict their access to those elements. Better yet, code these actions into the application and force users to use that method instead.

Procedure 8.5.16b requires that assessors verify that application IDs and their passwords can only be used by the authorized applications and not by individual users or other processes (typically meaning that the accounts do not allow for interactive login and you avoid using passwords over keys and certificates). This can be challenging depending on your infrastructure. Older versions of database servers may not be able to sufficiently distinguish users from applications. Consider the following example.

Diana is a Database Administrator (DBA) and manages two main locations where enterprise data is stored. Her business critical information is stored in various locations on a mainframe. The security added to the mainframe allows batch processes to operate under noninteractive login credentials, thus preventing those credentials from being used for an interactive session with the data. Diana’s Web farm for her e-commerce site pulls its data from a PostgreSQL database. In her pga_hba.conf, she set an Internet Protocol (IP)-based restriction on the application’s ID by adding in the source IPs that are valid from her application servers. She has four different ones in her enterprise, so all four of the IPs are in her pga_hba.conf, and the application IDs can only be used from those machines which are considerably locked down.

Tools

Here is a sample pg_hba.conf with IP-based limitations. Assume that the database is called “CommWebsite” and the ID used for access is “CommUser.” Your pg_hba.conf would look like this:

# TYPE DATABASE USER CIDR-ADDRESS METHOD host CommSite CommUsr 10.4.30.0/29 password

Databases and requirement 8.7

Databases contain lots of information valuable to a hacker, yet the security around databases is sometimes the worst in the entire enterprise. Many compromises occur because of administrator-level accounts with blank passwords. Requirement 8.7 has four testing procedures. Procedure 8.7.a requires assessors to verify that all users are authenticated prior to being granted access to the database, 8.7.b and 8.7.c require direct user interactions with the database to be done through programmatic methods such as stored procedures, and that direct queries to the databases are restricted. If you have power users that log into your database directly instead of going through an application, take any common actions they may perform and put them into stored procedures or functions, and then restrict their access to those elements. Better yet, code these actions into the application and force users to use that method instead.

Procedure 8.7.d requires that assessors verify that application IDs and their passwords can only be used by the authorized applications and not by individual users or other processes (typically meaning that the accounts do not allow for interactive login and you avoid using passwords over keys and certificates). This can be challenging depending on your infrastructure. Older versions of database servers may not be able to sufficiently distinguish users from applications. Consider the following example.

Diana is a Database Administrator (DBA) and manages two main locations where enterprise data is stored. Her business critical information is stored in various locations on a mainframe. The security added to the mainframe allows batch processes to operate under noninteractive login credentials, thus preventing those credentials from being used for an interactive session with the data. Diana’s Web farm for her e-commerce site pulls its data from a PostgreSQL database. In her pga_hba.conf, she set an Internet Protocol (IP)-based restriction on the application’s ID by adding in the source IPs that are valid from her application servers. She has four different ones in her enterprise, so all four of the IPs are in her pga_hba.conf, and the application IDs can only be used from those machines which are considerably locked down.

Tools

Here is a sample pg_hba.conf with IP-based limitations. Assume that the database is called “CommWebsite” and the ID used for access is “CommUser.” Your pg_hba.conf would look like this:

Tools

Want to see how strong your passwords are? Mandylion Research Labs (www.mandylionlabs.com) created a fantastic brute force calculator that you can download (www.mandylionlabs.com/documents/BFTCalc.xls) and test to see how long it would theoretically take to break a password or key. Plugging in the elements of the password above (Newuser1), it would take the average computer just over 2½ h to break that password. Let’s say that you didn’t know that the password contained one uppercase letter, six lowercase letters, and one number and assumed an eight-character random mix of uppercase and lowercase letters and numbers. If you made this assumption, the average computer would take a little more than 6300 h using a brute force attack to crack the password (an effective key strength of 236). Adding special characters in it would take over 117,000 h with an effective key strength of 252. This is where user education is important.

Tip

In Windows (especially prior to 2008) it is a best practice to rename the RID = 500 account and set an extremely long passphrase. Activity using this account should not ever occur as administrators should use their own unique accounts. Events from the RID/SID = 500 account can be monitored with a log management system.

This is relatively easy when you have infrastructure such as LDAP or Active Directory deployed, but it can be a challenge when you have machines that stand alone, not as part of a formal policy enforcement process.

Note

Why do operating system manufacturers insist on continuing the trend of providing a root or administrator user account that has access to the whole system for whatever it wants to do? Poorly developed software with global administrator privileges will surely lead to a root-level compromise, whereby a system is then “pwned” by an attacker. Software that needs elevated privileges must be limited to sandboxes on the servers and should never require administrators to run it under the root-level administrator account. If your vendor tells you it is a requirement, tell them you will be taking your business elsewhere.