Often applications that connect to database servers (SQL Server)
authenticate users by using a user-name and password. It is
also an often practice that these users are created in SQL
Server as logins and have varying degree of permissions on
the database and its objects. For example, the managing director
of the company potentially has more access to information
(tables, views, stored procedures etc) than a data entry clerk,
who might have access to a few objects only. Other than this
default access mechanism, it might be sometimes required by
your application to uniformly provide security to a set of
objects for the application to all users, irrespective of
the security that they inherit by virtue of logging into the
application.
For example, assume that you have a function
called calculatePay. Let's assume that each
department in an organization has a different method of
calculation, therefore you provide a hook in your application
for each department to implement the calculatePay
function so that you can just call the function. This sort
of an implementation opens up a lot of issues. First of all,
what is a guarantee that we have that the calculatePay
function calculates only the pay?? What if the function decides
to drop a table?? How do you protect yourself from such dangers??
The answer, application roles.
Using application roles,
you can create a role called calculate pay
and then give access to this role to only access 2-3
tables and when you execute your calculatePay
function, you enable this role and then call the function.
Enabling the role bypasses all the security that is currently
set and makes the security context of the application
role active. This ensures that the calculatePay
function does not do anything malicious. After the function
has finished its work and you get control back to the application,
you disable the application role and return everything to
normal!! Note that an application role cannot be deactivated
once enabled. The user will need to re-connect to the database.
Sounds interesting?? It is and the usage of application roles
are vast. Here are some properties of application roles:
Application
roles contain no members. Microsoft Windows NT®
4.0 or Windows® 2000 groups, users, and roles cannot
be added to application roles; the permissions of the
application role are gained when the application role
is activated for the user's connection through a specific
application or applications. A user's association with
an application role is due to his ability to run an application
that activates the role, rather than his being a member
of the role.
Application
roles are inactive by default and require a password to
be activated.
Application
roles bypass standard permissions. When an application
role is activated for a connection by the application,
the connection permanently loses all permissions applied
to the login, user account, or other groups or database
roles in all databases for the duration of the connection.
The connection gains the permissions associated with the
application role for the database in which the application
role exists.
The
permissions the user gained from the application role
remain in effect until the connection logs out of an instance
of SQL Server. To ensure that all the functions
of the application can be performed, a connection must
lose default permissions applied to the login and user
account or other groups or database roles in all databases
for the duration of the connection and gain the permissions
associated with the application role. For example, if
a user is usually denied access to a table that the application
must access, then the denied access should be revoked
so the user can use the application successfully.
Application
roles overcome any conflicts with user's default
permissions by temporarily suspending the user's default
permissions and assigning them only the permissions of
the application role.
In essence, application roles
allow the application, rather than SQL Server, to take over
the responsibility of user authentication. However, because
SQL Server still must authenticate the application when it accesses
databases, the application must provide a password because there
is no other way to authenticate an application. If ad hoc access
to a database is not required, users and Windows NT 4.0 or Windows
2000 groups do not need to be granted any permissions because
all permissions can be assigned by the applications they use
to access the database. In such an environment, standardizing
on one system-wide password assigned to an application role
is possible, assuming access to the applications is secure.
Creating an Application
Role
Creating an application role is a multi-step process and its
very easy.
Expand
the database where you want to create the application
role.
Right click
the [Roles] node and choose [New
Database Role...].
In the resultant
dialog box, type in a name for the role and choose the
[Application Role] radio button. This
enables the password box.
Type in a password
for the application role and click on the OK button.
This creates
the application role and you can see it along with the
list of roles.
Once the role
has been created, select the role, right-click the same
and choose the [Properties] option.
This opens
the same dialog box that you saw when creating the role,
but this time, you will see a new [Permissions]
button in the dialog box.
Clicking on
the [Permissions] button, you see a dialog
box that lets you set the permissions for the role.
Choose the
relevant objects on which you want the permissions and
also the kind of permissions required and click on the
[Ok] button.
If you select a table object
in the permissions dialog box, the [Columns]
button will be enabled that will allow you to set appropriate
permissions on the columns of a table. This allows for more
granular control of permissions.
Note that you can also use the system stored procedure called
sp_addapprole. Please refer to the SQL Server
books online for more information.
Testing the Application
Role
Now that we have seen how to create an application role and
set its permissions, let's see it in action.
Create
an application role called testRole with
a password of password.
Give Select
rights to only the authors table.
Save the role.
The following
figure shows the application role dialog box, after creation
and the permissions dialog box.
Now, type in
the following T-SQL block into Query Analyzer.
BEGIN
SELECT
* FROM authors
--
Simulate a malicious access!!
SELECT
* FROM titleauthor
END
This T-SQL
block simulates a scenario like the one described above.
The access to the titleauthor table is
considered to be malicious code.
If you execute
this code, all will be fine and results from both the
tables are displayed.
Now, let's
try the same example, but with the application role that
we created above. Type in the following T-SQL.
BEGIN
EXEC
sp_setapprole
@rolename
= 'testRole',
@password
= 'password'
SELECT
* FROM authors
--
Simulate a malicious access!!
SELECT
* FROM titleauthor
END
When you execute
this code, you will now see the following message from
SQL Server, indicating that the permissions that we have
set for the application role are in effect.
Deleting Application
Roles
Once you no longer need an application role, you can select
the application role in Enterprise Manager, right-click and
choose Delete or use the system stored
procedure sp_dropapprole to delete the application
role.
Conclusion
In this article, we have seen how application roles can be
used to effectively implement universal security for an application
and how to maintain them. Application roles are a very powerful
concept and when used carefully, can provide very good security
for your application functions. For more information about
application roles and some extra details and options about
the parameters for application roles, see the SQL Server Books
Online for more information.
