To minimize the risk of hacking, it is essential for a DBA to minimize (if not completely eliminate) the vulnerabilities of SQL Server. In this section, we will be looking at three different vulnerabilities and how stored procedures can be used to minimize them. We will look at:
Misconfiguration of SQL Servers, particularly regarding permissions on objects
SQL injection attacks
SQL buffer overflows
This can be broken down into two categories – problems arising due to a weak setup and problems
By far, the most damage that can ever be done to your IT enterprise is by a rogue DBA, so judge wisely!
Security Tip #1: This is the first step in SQL Server security. You have to trust your DBA. If you can't, find another DBA. There is no alternative.
Even though we have said that there is no alternative, in reality, you could implement C2 security tracing. However note that the overhead is extraordinarily high in terms of resource (CPU, disk, memory, and so on) usage. Additionally, it is quite hard to configure a system where the trace occurs without the DBA having access to it.
For more details on setting up a C2-level auditing you can refer to SQL Server Books Online.
Apart from a rogue DBA, the first real threat of unauthorized data access happens when the SQL Server security infrastructure is not properly configured. By this, we mean that a DBA doesn't know what they are doing and inadvertently end up leaving a hole in their server configuration. Closely related to this is the improper assignment of permissions in an application, such that a
The security settings of an out-of-the-box SQL Server 2000 installation are
At the same time, we need to configure SQL Server properly. Numerous articles have been written about the setting up SQL Server properly. We will not cover these steps; instead we recommend that you refer to the Microsoft SQL Server 2000 Security white paper, it's available at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_security2000.asp
This document will give you the basic steps for securing your SQL Server. This said, we must ensure the following:
Do not allow a SQL Server to be exposed directly through port 1433 from the Internet; block port 1433 at the firewall. If you expose port 1433, you will be port-scanned, time and again, for known vulnerabilities. Even if you are up to date in applying the service
Replace the SQL Server's default listen-on port for TCP/IP sockets (port 1433) with some other port and re direct your
To change the server's settings, perform the following steps. Run the Server Network Utility to connect to the server. Then, highlight the TCP/IP entry and click on the Properties button. Change the port number in the pop-up window and click on OK :
After making the changes to the server, we need to configure the client. This can be done by running the Client Network Utility. In the
tab, click the
button. In the pop -up window, against
Get the service packs, patches, and hoxfixes run through a development server and move into your production environment. This should be one of your highest priorities.
If at all possible, use integrated security instead of mixed-mode security for your applications.
login's (that is, the one with SID of
) password extremely hard and store it away securely. Only use it when it's
A script for making such random passwords is included in our code download; you can get it from http://www.apress.com.
If you use applications that require the use of the
login, audit the use by
EXECUTE msdb.dbo.sp_add_alert @name = N'SA non-trusted login', @message_id = 18454, @severity = 0, @enabled = 1, @delay_between_responses = 8, @include_event_description_in = 5, @event_description_keyword = N'''sa''', @job_name = N'SA login email notify', @category_name = N'[Uncategorized]'
This snippet fires a job which queries
es for Windows 2000 login ID and application name, logs the usage in a table, and e-
We also suggest that all users having access to the sa login use a named pipes connection rather than the usual TCP/IP sockets connection. By making your users use named pipes, you will be able to positively identify their NT account with their connection. There is a small performance trade-off for the connection, but the vast majority of applications and servers will not suffer. In this way, you can also ensure that the operating system approves the security credentials of the users, before they get to SQL Server.
Now, let's look at how a person gets access to the database objects, in terms of the following elements — Windows Users and Groups, SQL login IDs, database objects, database users, database roles, object ownership, and object permissions.
These are the user accounts and groups that users are assigned to in the operating system. Internally, a binary number, called Security ID or SID, identifies a user or a group. When a user logs into a Windows 2000 computer, they get a security token for their login session. A user in a group will have the SID of that
The user and group accounts can be either at the domain level or at the local computer level;
These logins are present at the SQL Server level, with the data supporting them residing in the sysxlogins table of the master database. They can either be integrated logins with the operating system or standard logins that have no direct relationship with the operating system. If they are integrated, the 28-byte SID in SQL Server is obtained from either the domain or local computer by using the undocumented GET_SID function (we have discussed this function in Chapter 5). This SID directly corresponds to the SID of a particular user or group.
When a user logs into the SQL Server, with an integrated login, they present their token with their SID collection to the SQL Server for comparison with sysxlogins . They can have multiple permission paths since they could be identified with multiple SIDS. A stored procedure, xp_logininfo , shows all the permission paths for an integrated login.
If it is a standard login, the SID in
is a self-generated 16-byte binary number. In this case, the password for the login is also stored in an encrypted form, in the
column of the
table. Normally, by default the
table and its
column for standard logins is only readable to
You should never let normal users access this column, they might download these encrypted passwords and run an offline brute force attack on them by using the undocumented PWDCOMPARE() function.
When users log in with a standard login, they have to present their password for comparison with the encrypted password stored in
. An undocumented function,
Security Tip #2: Get a SSL certificate for your server, so that standard login passwords are encrypted automatically.
For more details refer to the section on Installing a SSL Certificate.
Apart from the little bit of configuration data that SQL Server stores in the registry, virtually all data is stored in tables. SQL Server has two types of tables – system tables and user tables. Other objects in the database allow customized viewing (through objects called views), improved sorting (through indexes), customized processing (through stored procedures and functions), limiting table usage (through constraints and defaults), and defining custom data formats (through user-defined data types). Three types of objects are used for customized processing – stored procedures, extended stored procedures, and functions. We'll refer to these collectively as procedures.
The basic record for each object is stored in a system table called sysobjects . The definitions for each procedure are stored in one or more records of the syscomments system table, in binary format. We'll talk more about the syscomments table later on in the section on Encrypting Stored Procedures.
Security Tip #3: The best practice is to only allow indirect access to table data through stored procedures and, in limited cases, by select rights on views. Doing so
Database User IDs are how Windows login SIDs mapped down into the database level. The database user ID can be identified with a SID of a standard login, integrated user, or integrated group. The SID in the
table of the database is the same as the SID of the
table in the
database. Usually the
There are two special kinds of user IDs – the database owner or DBO and the guest user ID. The DBO has maximum and irrevocable rights in the database and will always be identified with one of the SID s in sysxlogins . In order for a login to have access to objects in a database, they must be assigned to a user ID in the database. However, there is one exception. If there is a guest user ID in the database, all server logins that do not have an explicit user ID, inherit the permissions assigned to the guest user ID.
Security Tip #4: Delete the guest user ID in a database. Always explicitly assign logins to a database if they need to access objects in the database. Note that you cannot delete the guest user ID from the master database.
There are two types of permissions in a database – statement permissions and object permissions. Statement permissions allow you to do things like creating objects and backup databases. In this chapter, we will focus on object permissions. As the name implies they control permissions to use objects.
Security Tip #5: You really need to understand the terminology of action, permission, object, and sysusers record when talking about the application of permissions to objects.
In T-SQL, a command to setup permissions has the following syntactical structure:
Action Permission ON object TO SYSUSER_Rec
In the following example, we use the GRANT action to allow the SELECT permission on the sysobjects object to a sysusers record named JERRY:
GRANT SELECT ON sysobjects TO JERRY
As we mentioned, object permissions include SELECT , UPDATE , INSERT , DELETE , EXECUTE , REFERENCES , and ALL . ALL includes every one of the others. Only the EXECUTE permission applies to stored procedures. If you use ALL for a stored procedure permission statement, it will apply the EXECUTE permission only. EXECUTE and REFERENCES apply to scalar valued functions.
There are three actions for managing permissions –
allows you to exercise a particular permission on an object, while
keeps you from exercising it.
Coming back to our above example of Jerry and the sysobjects table, if Jerry is a member of a group called Accounting with an Accounting group login and a corresponding user ID in the database, we can issue the following command:
DENY SELECT ON sysobjects TO ACCOUNTING
As a result, Jerry will not be allowed to execute
table because the
action override for the Accounting group user ID
The sysuser record can be either a user ID whose SID is mapped to a SQL Server login or a database role.
table in a database stores user IDs and roles. We have already talked about user IDs. Roles
SELECT * FROM dbo.sysusers WHERE UID > 16383
Alternatively, you execute the system stored procedure sp_helprole :
Roles are also different from user IDs in the sense that roles are
Microsoft designed security in SQL Server 7.0 and SQL Server 2000 with the idea that permissions would be assigned to roles and not to user IDs. By assigning permissions to a role, we can tremendously increase the flexibility of our database security structure. You can find more on this in the Putting it all Together section.
Security Tip #6: Apply permissions to database roles and not to user IDs.
There is a special role called PUBLIC with UID 0. Every user ID in a database is automatically a member of the PUBLIC role, even without being defined in sysmembers . Finally, roles are different from user IDs; the SID column in sysusers for a user ID maps back to a SQL Server login in the sysxlogins table of the master database, the SID column in sysusers for a role is NULL .
Security Tip #7: Remove PUBLIC role permissions from all objects in a database. An exception is the master database. Generally, the permission granted to the PUBLIC role in the model database, from which all databases start, are not necessary for an application to run. Rely only on setting permissions explicitly for a role. You will want to try it out in a development environment before implementing it in a production environment, as it may break poorly implemented applications that rely on public permissions instead of explicit role-based permissions.
For removing all public permissions, we can use the following script:
SET NOCOUNT ON -- We will loop through one record at a time SET ROWCOUNT 1 DECLARE @i INT, @cmd VARCHAR(1000) SELECT @i = 1 WHILE @i > 0 BEGIN SELECT @cmd = NULL -- u.name is the owner of object o.name SELECT @cmd = u.name+'.'+o.name FROM sysobjects o JOIN syspermissions p ON o.id = p.id JOIN sysusers u ON o.uid = u.uid WHERE p.grantee = 0 -- public grantee UID is 0 SELECT @i = @@rowcount -- the last time through @cmd IS NULL IF @cmd IS NOT NULL BEGIN SELECT 'Revoking PUBLIC perm on '+@cmd EXEC ('revoke all on '+@cmd+' to public') END END
A record in the sysusers table, either a user ID or a role, always owns tables, stored procedures, views, and other objects in the sysobjects table. You can change an object's owner by using the sp_changeobjectowner procedure.
An fully named object consists of four
If there are no objects with the specified name owned by the current user, the DBO owner is assumed. If there are no objects owned by the current user or the DBO with the object name, an invalid object name error is returned. Generally around 97% of databases have all their objects owned by the DBO user ID.
If the owner name is left off, it's assumed to be same as the currently logged in user. This may lend a great deal of convenience, as the owner doesn't have to be explicitly named in an object call, but it is also a security hole.
If the database name is left out, the object is assumed to be in the current database.
If the server name is left off, the current server is assumed. This would normally be left off, except in cross-server calls.
A fully-qualified name generally has only the first three parts, for example, master.dbo.sysobject . If the database name is left out, the object is assumed to be in the current database.
Security Tip #8: Create a new role and have all your objects explicitly owned by this role. We commonly create a role called APP in the model database, when we first set up a server, to own all our user objects.
However note that, this is a highly
Let's look at an example. First, create a TESTER login (with a blank password), we will use it to run a cross-database view. Before you go any further, make sure that the pubs and Northwind databases are owned by the same login. In our case, it is the sa login with a SID of 0x01 .
Look at the sysdatabases table and ensure that the SID s for the two databases are the same. In the first instance, the DBO will be the owner of both objects. In the second instance, the owner of both objects will be the APP role. First, log in through Query Analyzer, as a user with sysadmin rights for the server. Then run the following script to set up the test environment:
EXEC master.DBO.sp_addlogin tester EXEC pubs.DBO.sp adduser tester EXEC northwind.DBO.sp_adduser tester EXEC sp_defaultdb tester, northwind EXEC pubs.DBO.sp_addrole app EXEC northwind.DBO.sp_addrole app SELECT * INTO pubs.app.authors FROM pubs.DBO.authors USE northwind GO CREATE VIEW app.vw_authors AS SELECT * FROM pubs.app.authors GO CREATE VIEW DBO.vw_authors AS SELECT * FROM pubs.DBO.authors GO GRANT SELECT ON app.vw authors TO tester GRANT SELECT ON DBO.vw_authors TO tester
SELECT * FROM DBO.vw_authors
You will get the record set of the DBO.authors table in the pubs database. This illustrates the potential cross-database security hole of always using the DBO user ID, as an object owner. Now execute the following query, as tester , in the Northwind database:
SELECT * FROM app.vw_authors
You will get a
We need two databases with a common login as owner
In the first database, the login must be a user with very limited permissions
In the second database, the same login must be a user
CREATE VIEW statement permission
CREATE PROCEDURE statement permission
member of the db_ddladmin fixed database role
member of the DB_OWNER fixed database role
The login creates a
-owned procedure or view in the second database that
Incidentally, in an out-of-the-box installation, the pubs database meets these conditions as the ‘second database’, such that any login in the server could carry out this hack on a database with the same owner as the pubs database. In a default install, right-click the pubs database in SQL Enterprise Manager and click Properties . Look at the Permissions tab, and you will see that the guest user ID has CREATE VIEW and CREATE PROCEDURE permissions.
Security Tip #9: Delete the pubs and Northwind databases from a production server, or, at a minimum, delete the guest user ID from pubs and Northwind databases. For good measure, delete the guest user ID from the msdb database, also.
WARNING: Remember, that there may be poorly developed applications that make use of these guest security holes. Make sure you test it on a development server prior to doing this on a production instance. Better still, follow SecurityTtip #8 .
We have a feeling that Microsoft is really addressing this problem with its system tables, due to poorly implemented applications. After all, even if you make the user objects owned by the APP role in your databases, a hacker can still get a good picture of your schema through this security hole by querying cross-database the system tables. If you run the following query in SQL 2000 having Service Pack 1 or later installed, you will see what we mean:
SELECT [DESCRIPTION] FROM MASTER.DBO.SYSMESSAGES WHERE ERROR = 15355
This query will produce the following output:
"'sys' will be a reserved user or role name in next version of SQL Server."
Now that you know what a login, user ID, role, object, object ownership, and permissions are, let's look into the correct way for setting up a permission chain for an ideal application. The following diagram shows this:
From the diagram, we have the following sequence:
Note first that the application uses integrated security.
Set up either a domain global group in Windows NT 4.0 domain SAM, or a global or universal group in a Windows 2000 Active Directory. Universal security groups are only available in Active Directory
Add the relevant user accounts to the group that you created in step 2.
Run SP_GRANTLOGIN to give a SQL Server login to the group you created in step 2. We prefer to script everything out and save them; you never know when they'll come handy for disaster recovery or when you'll have to use them in another server. Plus, they serve as handy documentation. Keep the domain/groupname part for the login name. It keeps all the groups from a domain group together in the GUI.
Run SP_ADDUSER to give the login access to the relevant database. We usually strip off the domain names here and make the username just the groupname. Thus, almost all the usernames are SQL plus the "meaningful name without spaces".
Add a role to the database using SP_ADDROLE . The role name is the username minus the SQL prefix, that is, just the "meaningful name without spaces".
Add a role named APP to the databases if it didn't inherit such a role from your model database.
Create all your objects under the ownership of this newly defined role (that is, APP ).
Grant the minimum permissions necessary for the proper operation of the application to the role created in step 6. To make the application secure, all the data access should be through stored procedures.
If, for some reason, you have super users who need to control permission on your APP role objects then give them membership in the APP role. This would be the ONLY exception when we would give non- DBO users membership in the APP role.
This sequence will give you good indirection between users and roles. To quickly remove a role from an application, just remove the user IDs, representing groups, from the role membership. In this way, the assignments of the permissions to the role
When you move a database to another server, either by backing up and restoring or detaching, copying, and reattaching, the permissions will automatically get synchronized if logins are present for the relevant groups. If you install Service Pack 2 (Client Tools module) for SQL 2000, in SQL Enterprise Manager you can readily see orphaned User IDs from databases that don't have a corresponding login with the same SID .
Contrary to Microsoft ‘recommended method’, we don't make use of local groups on the operating system of the SQL Server to indirect the domain global groups. If we do this, then when we move a database from a development to production instance or vice-versa,
synchronization is a nightmare. Microsoft made this recommendation because it gives a valid one-to-many relationship solution between the local group and multiple domain global groups. We rarely
This sequence applies only to
SQL injection attack is the single biggest threat faced by SQL-based applications today,
The attack happens when an application processes user input that forms part of a SQL command. If the application builds executable SQL strings by using
We'll use the
"SELECT au_lname, au_fname FROM authors WHERE au_id = '" & _ InputBox.Text & "'"
"EXEC pubs.dbo.AuthorLogin '" & InputBox.Text & "'"
We'll go through the first example in detail; however, the second is an equally valid attack.
Let's set up the scenario. Imagine that you have a web application. The web application or COM+ proxy account under which SQL code is executed has broad rights in the
database. In the worst conditions, the proxy account has
rights in the SQL Server. Drill down in the object browser and
In a default pubs instance, we will see that the table has 14 jobs. To confirm this, run the following SQL query:
SELECT * FROM jobs
Now, we will simulate a normal user of your web application by executing the following code in the Query Analyzer, on the pubs database:
-- Set quoted identifiers OFF so that double-quote work like in VB or -- VBScript code SET QUOTED_IDENTIFIER OFF DECLARE @InputText VARCHAR(100), @command VARCHAR( 1000) -- Imagine that the application is capturing the InputText.Text -- variable SELECT @InputText = "527-72-3246" SELECT @InputText AS 'InputText.Text' -- Now, imagine that app is VB or VBScript and piecing together a SQL -- command. The final command is represented by @command SELECT @command = "select au_fname, au_lname from authors WHERE au_id = '" + @InputText + "'" SELECT @command AS 'Executable SQL Command' -- Imagine that we pass the command to SQL: EXEC (@command)
If this query works, as intended, we will get Morningstar Greene as the output.
Now, we will simulate a malicious user's input into the @InputText variable. In the above example, instead of using 527-72-3246 , change the line to:
SELECT @InputText = "' ; INSERT INTO jobs (job_desc, min_lvl, max_lvl) VALUES ('Important Job',25,100) -- "
Notice how we first
Substitution of the SQL injection code will make our sample code looks like (we'll leave out the previous comments for brevity):
SET QUOTED_IDENTIFIER OFF DECLARE @InputText VARCHAR(100), @command VARCHAR(1000) SELECT @InputText = "' ; INSERT INTO jobs (job_desc, min_lvl, max_lvl) VALUES ('Important Job',25,100) -- " SELECT @InputText AS 'InputText.Text' SELECT @command = "select au_fname, au_lname FROM authors WHERE au_id = '" + @InputText + "'" SELECT @command AS 'Executable SQL Command' EXEC (@command)
After executing this code, right-click and open the jobs table in pubs . The malicious job, which we have added through injection attack, is Important Job .
Let's look at one more malicious example. Before running this, first make a new table in pubs by running the following command:
CREATE TABLE ATEST (testcol INT)
Now, change InputText to select a name from sysobjects . The top portion of your code will look like:
SET QUOTED_IDENTIFIER OFF DECLARE @InputText VARCHAR(100), @command VARCHAR(1000) SELECT @InputText = "' ; SELECT TOP 1 NAME FROM SYSOBJECTS WHERE TYPE = 'U' ORDER BY NAME -- "
For this example, consider that your web application returns a name. In our case, it is the
table we just created. The hacker may need to pad the number of
SET QUOTED_IDENTIFIER OFF DECLARE @InputText VARCHAR(100), @command VARCHAR(1000) SELECT @InputText ="';DROP TABLE ATEST -- "
In this case it's really nasty – they are dropping a table! Remember that, earlier we had mentioned that usually most of the objects in a database are owned by DBO . If you have your objects owned instead by a non-default role (for example, the APP role ), this injection fails to delete the table, as the object owner was not explicitly called.
What really makes this injection attack possible, though, is not that you relied upon the
as the implicit owner, but that the proxy account coming from the web application was all too powerful. Here we made use of
privileges. Imagine the consequences of mistakenly granting that proxy account
privileges. All too often, poor coding techniques on the front end of an application which allow SQL injection are
Remember, SQL injection is not a SQL Server problem; it is a vulnerability inherent in front-end applications.
Now we've seen the problems, let's tackle the solutions.
For example, let's take VB.NET. In VB.NET, when you build a SQL Statement, instead of building a straight command, use the Parameters (syntactically SqlParameter ) collection to define an @au_id parameter.
For a stored procedure running through VB.NET, explicitly define the command string type, as a CommandType to StoredProcedure . Then, again, use the Parameters collection to define the input parameters for the stored procedure.
When you use the Parameters collection in VB.NET, as in this case, no matter what malicious input the user provides, it is always treated as a literal string. Also, by using the Parameters collection, data type and length checks are more easily enforced.
What happens under the covers, when the Parameters collection is used? VB.NET roles together a fully defined SP_EXECUTESQL command. Imagine that our InputText.Text box was sent to a parameter called @au_id , which was VARCHAR(100) . This is how our SQL sample code would model the change:
SET QUOTED_IDENTIFIER OFF DECLARE @InputText VARCHAR(100), @command VARCHAR(1000) SELECT @InputText = "527-72-3246" SELECT @InputText = "' ; INSERT INTO jobs (job_desc, min_lvl, max_lvl) VALUES ('Important Job',25,100) -- " SELECT @InputText AS 'InputText.Text' EXECUTE sp_executesql N'select au_fname,au_lname FROM authors WHERE au_id = @au_id', N'@au_id VARCHAR(100)', @au_id = @InputText
In the above example, we can alternatively comment out the two @InputText variable assignments of the good input and the malicious input. No matter which one fires, you will not have a malicious result because in this syntactical structure InputText.Text is always treated as a literal.
Another method of foiling SQL injection attacks is to filter the user input for SQL characters. Use the REPLACE function to replace any apostrophe (single quotation mark to SQL) with an additional apostrophe. Within a SQL string, two consecutive single quotation marks are treated as an instance of the apostrophe character within the string, rather than as the unexpected delimiters.
In our Query Analyzer test scenario, notice the line with the REPLACE function that we inserted:
SET QUOTED_IDENTIFIER OFF DECLARE @InputText varchar(100), @command varchar(1000) SELECT @InputText = "' ; INSERT INTO jobs (job_desc, min_lvl, max_lvl) VALUES ('Important Job',25,100) --" SELECT @InputText = REPLACE(@InputText,"'",""") SELECT @InputText AS 'InputText.Text' SELECT @command = "select au_fname,au_lname from authors WHERE au_id = '" + @InputText + "'" SELECT @command AS 'Executable SQL Command' EXEC (@command)
There are other escape characters that, in addition to the single quotation mark, needs to be routinely
' becomes ‘'
REPLACE( "’", "''" ).
[ becomes [[ ]
REPLACE ( "[", "[[ ]")
% becomes [%]
REPLACE ( "%", "[%]")
_ becomes [_]
REPLACE ( "_", "[_]")
On the other hand, when your VB programmer uses the Parameters collection, only one instance of the string will reside in the memory space of the application. Additionally, as we pointed out earlier, using the Parameters collection for ad-hoc SQL statements in our call to SQL causes the use of the SP_EXECUTESQL system extended stored procedure. This procedure is designed to cache query plans where possible, giving improved performance.
There are two more best practices for preventing SQL injection attacks. Remember the injection sample that caused the dropping of our
table? This was possible only because the proxy account of the web application, or COM+ middle layer, had permissions that were too broad. This
Security Tip #10: Always run SQL code with a least possible privileged account.
In fact, it is worth repeating here another one of our earlier security tips.
Security Tip #3: Allow only indirect access to table data through stored procedures and, in limited cases, select rights on views.
If the proxy account is capable of only executing stored procedures, all of our previous SQL injection attempts will fail. Generally, a hacker will not have the sophistication to
Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication
s white paper issued by Microsoft on front-end application security is a must read for all DBAs and .NET developers. It is available in the online MSDN library