SQL Server 2000 Security – Part 5 (Application roles)

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