Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 8, 2004

SQL Server 2000 Security - Part 5 (Application roles)

By Marcin Policht

In our previous article, we started our discussion about SQL Server 2000 authorization, focusing initially on permissions assigned to standard fixed server and database roles. We also explained that on the database level, SQL Server 2000 provides more flexibility, since it allows you to define your own custom roles, with the help of the sp_addrole stored procedure (or via graphical interface of SQL Server Enterprise Manager). In addition to these standard roles, which control access to SQL Server and database objects on per-login and per-user basis respectively, you also have available database-level application roles, which control access to database objects and operations on per-application basis. This is most commonly used in client-server scenarios, where a client application is invoked by multiple clients and direct database access (outside of the application) by the same clients needs to be prevented.

Similar to the standard database roles previously described, application roles are used to secure access rights on the database level, and therefore they have to be created separately for each database in which they are needed. What makes them unique is that their membership is not explicitly specified. Instead, after initial login to the SQL server, a client who wants to use a specific application role must connect to the target database and activate the role by running sp_setapprole stored procedure (this client is typically an application itself, with SQL login, connection to a database, and invocation of the stored procedure included in its code). At that point, all user permissions to the database are replaced with permissions granted to the application role. This remains in effect as long as the role remains active (until the client connection closes). Note that, if during this time, access to another database is required, it can only be granted via a guest user account (assuming, of course, that this account exists in another database). To prevent unauthorized use of application roles, they are secured with a password assigned during their creation, which needs to be provided every time sp_setapprole is invoked. Even though you have an option of obfuscating this password, the algorithm used in such cases is identical to the one used to secure login credentials via SQL Server authentication, and as such, it can be fairly easily decrypted with utilities downloadable from the Internet (for details, refer to one of our previous articles). In order to protect credentials from being discovered while being transferred over a network, you should use Secure Sockets Layer or built-in encryption (depending on Net Library selected). You should also try to avoid hardcoding passwords into applications, since this introduces a potential vulnerability and makes it difficult to alter them, (it is a good practice to change passwords on regular basis). One way to accomplish this is to store the encrypted password outside of an application (e.g. in the registry of SQL Server computer) and use a decryption mechanism, activated every time the application is launched.

To create an application role, connect to a target database and launch sp_addrole stored procedure. sp_addrole has two parameters - @rolename and @password, intended to contain the name and password of the role, respectively. Alternatively, from SQL Server Enterprise Manager, bring-up the context sensitive menu of the Roles node of the target database and select New Role option. In the Database Role Properties dialog box, fill out the Name and Password text boxes.

To activate an application role, execute sp_setapprole stored procedure. sp_setapprole takes three parameters - @rolename, @password, and @encrypt, where the last one indicates whether encryption should take place (by default, this is not the case). If you decide to use encryption, set the @encrypt parameter to the string 'Odbc' and @password parameter to {Encrypt N'password'}. This converts application password ('password' in this example) to Unicode and calls the Encrypt function to encrypt it. Note that this function cannot be used when connecting to SQL Server 2000 via DB-Library, but only via ODBC client or OLE DB Provider.

Now that we have an understanding of different authorization mechanisms available in SQL Server 2000, let's look into recommendations regarding their use and management:

  • Monitor existing SQL Server logins and database users by analyzing mappings between them. This task can be simplified with the help of sp_change_users_login stored procedure, which outcome depends on the @Action input parameter, which can take one of the following values:
    • Auto_Fix - fixes missing mappings by comparing entries in the sysxlogins master database table and sususers table in the current database. Since this can potentially grant an unintended level of database access to existing logins, simply based on a coincidental match between the two, it should be used with caution.
    • Report - provides a listing of all users in the current database with no mapping to a SQL Server login. This might happen when transferring databases between SQL Servers with attach/detach operations or as a result of running sp_grantdbaccess stored procedure (graphical interface in SQL Server Enterprise Manager has built-in safety feature, which prevents creation of user accounts without linking them at the same time to existing SQL Server login).
    • Update_One - creates a mapping between a user within the current database to a SQL Server login (provided as @UserNamePattern and @LoginName input parameters).
  • Assign permissions to and ownership of database objects using database roles, rather than database users (the same way Windows groups are used to control the level of privileges). This simplifies maintenance tasks when one user needs to be replaced by another or when a user needs to be dropped.
  • Keep track of membership in privileged fixed server and database roles. Ensure that you keep track of individuals who are their members. This might involve checking membership of Windows groups, if they (instead of individual Windows users) are mapped to SQL Logins or database users assigned to the roles.
  • Make sure you fully understand set of privileges granted to fixed server and database roles (and keep the record of permissions granted to your custom database roles). Take advantage of sp_dbfixedrolepermission stored procedure, which provides comprehensive listing of permissions for all fixed database roles.
  • Be aware of intricacies involving security context of database access with Windows authentication. In particular, when a Windows user is allowed to login to SQL Server based on the group membership (i.e. user's Windows group, rather than user's Windows account, is listed in the sysxlogins master database table), connection to a target database will be established using one of the following methods:
    • with a database user account that is mapped directly to a Windows user account (rather than the Windows group that this user is a member of), providing that such database user account exists. Note that creating database users without having corresponding SQL Server logins defined (for Windows accounts) is not possible using SQL Server Enterprise Manager, however you can accomplish it by running sp_grantdbaccess stored procedure (and specify an appropriate Windows account as its only parameter).
    • if a database user account matching a Windows user account does not exist, SQL Server searches for a database user account mapped to the corresponding Windows group login. Providing that such mapping exists, a Windows user is connected with this user account to the database.
    • finally, if both methods fail (i.e. the target database does not contain a database user mapped to a Windows user or group account), then connection is possible only if the guest account exists in the target database.

    Note that this situation is even more complex if there are multiple Windows groups through which login to SQL Server is permitted (i.e. there are multiple SQL Server logins mapped to Windows groups that a Windows user is a member of). In this case, if there is no defined database user account matching the Windows user account, but both Windows groups have explicit database access (each have a corresponding user account in a target database), a database user account that will be used to connect to the database is difficult to predict, since it depends purely on the order in which SQL Server reads entries in the Windows access token, associated with the Windows user.

  • Avoid granting permissions to public group (the same way you should avoid granting permissions to Windows built-in Everyone group), since it contains all database users, including the guest account.
  • Limit to an absolute minimum the use of guest accounts in your databases. Note, however, that, by default, this account exists in all system databases with the exception of model.

In our next article, we will discuss permissions of individual database objects and operations.

» See All Articles by Columnist Marcin Policht

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM