SQL Server 2000 Security – Part 5 (Application roles)

In our previous
, 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

  • 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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.

Latest Articles