News · Meetings · Jobs · Downloads · Universal Thread articles · Web articles · Conference coverages · Consultants · Blogs · FAQ · User groups · Photos · Videos

Implementing Application Role Security to Secure your SQL Server Data

Dan Jurden, Bvcs
Dan Jurden is an independent software developer located in Brenham, Texas. He is a Microsoft Certified Professional. He co-authored the book Creating Visual FoxPro Applications using Visual FoxExpress with BOb Archer, published by Hentzenwerke Publishing. He has also authored articles published in CoDe Magazine and Universal Thread Magazine dealing with SQL Server and other topics. Dan has presented topics at the German DevCon, Essential Fox, SQL Server Live!, and GLGDW conferences. He has been developing Client-Server applications using SQL Server for several years. Dan can be reached via email at djurden@cebridge.net.

One of the primary reasons that VFP developers make the decision to move their data to SQL Server is the promise of additional security. However, if you don’t take the time to set things up properly, you data can be just as vulnerable as if it were in VFP tables. This article will give an overview of the different SQL Server security models, then cover one of the best (in my opinion) way to secure your data; the application role.

The wanna-be programmer

Every organization has at least one of these. This is the guy in accounting or marketing who has been tinkering with Access or File Maker Pro at home. He has created a couple of little databases; a home inventory system, a contact list, etc. At the coffee machine, he is always talking to you about his latest hobby. Now he thinks he knows enough about developing database applications that he is giving you advise about the latest system you are creating. I’m sure you know the guy I’m talking about.

These are the type of people who are the most dangerous. One day, you finally realize just how dangerous he is. At the water cooler one afternoon he says, “I was looking at the sales table in your application earlier today. I saw a field in there called PK_Sales. It contained a bunch of jibberish and is called a uniqueidentifier. What is that?”


How to keep them out of your application data

One of the most common ways to set up SQL Server security for your applications is to use ODBC to connect to the server using Windows Authentication. So when your application connects to the server using the ODBC DSN, it has access to select from, insert into or update whatever tables it needs. You may have security features in your application to prevent certain users from getting into areas that contain data that they shouldn’t be able to see or change.

The problem with this is that anybody with Access, Excel, File Maker Pro, or any other application that can connect to data via ODBC, can connect to your database also. Once they connect using one of these other applications, you have no control over what they can see or do. Wouldn’t be nice if the users could log on to the server, but have no access rights to anything on it? Then the application could log on and it would have access to everything. This would solve the problem. What I have just described is SQL Server’s Application Role Security model.

SQL Server Security

I am not going to attempt to cover everything there is to know about SQL Server security in this article. It is not possible. There have been several articles, whitepapers and books written on this topic. I will give a quick high-level overview of security so you’ll have an idea of how things work.

Windows Integrated Authentication

This is probably the easiest authentication model to use. If a user can log onto your network, you can grant that user account access to SQL Server. The user does not have to supply a different username and password for access to SQL Server. You can create SQL Server logins for each user of your network or you can use your network security groups.

So, let’s say you have 15 users in the accounting department. On your domain, there is a group called Accounting to which each of these users are assigned. You can add a login to SQL Server for the Accounting group and grant that login access to the accounting database.

Now all 15 users in the Accounting group can log on to SQL Server and use the accounting database.

You can control what databases the individual users or groups will have access to and what entities within the database they will have access to.

SQL Server Authentication

The main difference with SQL Server Authentication is that the users will have to supply a username and password to gain access to the server. Each user can be given an individual login or a group of users can share one. However, it is probably not a good idea to use a shared one, unless your application is logging in automatically. This way the individual users don’t have to know the username and password.

In my opinion, this is a more secure method than Windows Integrated Authentication. Again, your application can login automatically (with the username and password embedded in the application) so the user’s don’t even know it is required. Since the users don’t know the username and password, they would not be able to log into the server using some other application. But, that wanna-be programmer could somehow find out what the username and password is, and get access to everything that login has access to.

Role Based Security

No matter which authentication mode you use to give your users access to the server, you will need to assign the users to a server role. The role defines what access rights to the database objects the members of the role will have. Each database attached or added to the server will have several Standard Fixed roles. These will include db_datareader, db_datawriter, db_owner, etc. Here is a table that shows the role and what this role can do.

db_ownerHas all permissions in the database.
db_accessadminCan add or remove user IDs.
db_securityadminCan manage all permissions, object ownerships, roles and role memberships.
db_ddladminCan issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements.
db_backupoperatorCan issue DBCC, CHECKPOINT, and BACKUP statements.
db_datareaderCan select all data from any user table in the database.
db_datawriterCan select all data from any user table in the database.
db_denydatareaderCannot select any data from any user table in the database.
db_denydatawriterCannot modify any data in any user table in the database

There is also a Public role. This is a special role in every database to which every user belongs. You can assign default permissions to everyone in the database to this role. You do not assign users or groups to this role because everyone belongs to this role by default. Also, by default, this role does not have any specific rights to any user defined objects.

You can also create your own roles. This gives you the flexibility to assign rights to specific tables, views, stored procedures, etc to individual groups and users. It is possible to assign a user or group to more than one role. There are specific rules about which rights will take precedence when there are conflicting permissions. Please refer to the Books-On-Line help file for a good explanation of this.

Application Role

It can be an administrative headache trying to keep track of all the different roles the users and groups are assigned to. Because of this, some developers or administrators will either assign all users to the db_owner role or give the Public role all rights to all the objects in the database. This is very dangerous; especially if you are using Windows Integrated Authentication. You have just given that wanna-be programmer complete access to all data in the database! This is where the Application Role can help.

You probably already have security in your application so that certain users or groups of users cannot get to or see or manipulate sensitive data. However, with the above scenario, anybody with Access will be able to get to and see and manipulate the data outside of your application.

So the solution is to assign all users and/or groups to the Public role in your database. Make sure the Public role does not have select, update, insert, execute, etc permissions on any of your database objects. The Public role should have permissions to the system tables in your database. These will all start “sys”. This will prevent the users from using Access to get to your data. However, it will also prevent them from getting to the data from your application. You will need to create an Application Role and assign appropriate permissions to all the database objects to this role. Your application will then log into this role and will have access to all data it needs.

Creating an Application Role

There are a few ways to create an application role. You can do it interactively using the graphical user interface provided by Enterprise manager or you can do it in a script using T-SQL code. There is sufficient documentation in the Books-On-Line help file describing the necessary T-SQL commands to do this in a script. I’ll leave it up to you to investigate if you want to do it this way.

Here I’ll describe the way to do it using Enterprise Manager. Navigate to the server and database where you want to define the role. Right click on the “Roles” node and select “New Database Role…”. The Database Role Properties window will be displayed see Figure 1.

In the “Name” box, enter the name for your new application role. Select the “Application role” option in the “Database role type:” option group and enter a password. Now click on the “OK” button. This will add your new application role to the list of database roles. Now you need to assign the permissions to this role.

Assigning Permissions

To assign permissions to your new role, right click on it in the Roles list and select “Properties”. Now click on the “Permissions” button. A window similar to Figure 2 will be displayed allowing you to assign permissions to your database object to the selected role.

Once you have assigned all the appropriate permissions for your database objects, click on the “OK” button. Now your application role is set up and ready to be used.

Logging on to an Application Role in your Applications

Now that you are convinced that you want to use Application Role security, you are probably wondering how to use it in your applications. Depending on what method you are using, the technique will be a little different. Here we will show how to do this using SQL Passthrough and VFP 8’s new Cursor Adapter class.

NOTE: This technique will not work with remote views, or at least I haven’t found a way. It does work with ADO. The logic is very similar to the SQL Passthrough technique.

Logging onto an Application Role

There are a few important things you need to remember about using an application role.

1) There is a system stored procedure that is called to activate (or log into) an application role. The stored procedure is sp_setapprole. The syntax for this command is:

sp_setapprole ‘role name’, ‘password’[, ‘encrypt style’]

The first two parameters are self explanatory. The third parameter is optional and specifies the encryption style to be used by the password. This can be either “None” (which is the default) or “Odbc”. Refer to Table 2 for a description of these two options.

NoneThe password is not encrypted and is passed as plaintext.
ODBCThe password is encrypted using the ODBC Encrypt function before being sent to the server. You can only use this if you are connecting using ODBC or the OLE DB provider for SQL Server.

2) The set_approle stored procedure can be executed only by direct T-SQL statements. It cannot be executed from another stored procedure or UDF. 3) Once set, the application role applies to the current connection only. You will need to activate the application role for each connection to the server. 4) If the application role is already set on the current connection, trying to set the application role again will cause an error. I will show how to detect if the application role is set on the current connection later.

Detecting if the Application Role is already set

The best way to detect if the application role is already set is to check the permissions to a table in the database. You can do this using the T-SQL PERMISSIONS function. The PERMISSIONS function will return a value containing a bitmap that indicates the permissions for the current user. It takes an Object Identification Number as the first parameter and an optional column name as the second parameter. In our case, we are not concerned with the second parameter. There is also a T-SQL function that will give you the Object Identification Number for an object name. This function is OBJECT_ID. You pass in the name of the object. So you would do something like this:

select PERMISSIONS(OBJECT_ID(‘your_table_name’))

If this returns a 0 (zero), the current connection does not have any permissions to the object. If it returns any other value, the connection has permissions. You could use additional code to check to see what the permissions are. But in our situation, this is not necessary.

SQL Passthrough

Here is some sample code that shows how to set an application role using SQL Passthrough.

LOCAL liHandle as Integer, liResult as Integer

** get a connection handle
liHandle = SQLCONNECT('Northwind')

** check permissions
liResult = SQLEXEC(liHandle, ;
  "select permissions(object_id('customers')) as iPermissions", "PermissionCheck")

IF liResult > 0	
  IF PermissionCheck.iPermissions = 0
    ** need to activate app role
    liResult = SQLEXEC(liHandle, "exec sp_setapprole 'MyAppRole', 'MyAppRolePassword'")

    IF liResult < 0
      ** error occurred while setting app role
  ** error occurred while checking permissions

** Now we can continue with appropriate permissions
SQLEXEC(liHandle, 'select top 10 * from customers')

CursorAdapter class

With the CursorAdapter class, we can get a little more sophisticated. We can create our own CursorAdapter class by sub-classing VFP’s CursorAdapter class and adding additional methods to check and set the application role. Here is my sub-class.

DEFINE CLASS MyCursorAdapter as CursorAdapter 

  FUNCTION BeforeCursorFill( ;
    tlUseCursorSchema as Logical, tlNoDataOnLoad as Logical, tcSelectCmd) as Logical

    ** overrides the BeforeCursorFill method to activate the application role
    LOCAL llRetVal as Logical 
    llRetVal = DODEFAULT(tlUseCursorSchema, tlNoDataOnLoad, tcSelectCmd)

    IF llRetVal
      ** log onto the app role
      llRetVal = This.LogOnToAppRole()
    RETURN llRetVal	
  PROTECTED FUNCTION LogOnToAppRole() as Logical 
    ** logs onto the app role and returns a logical value stating if we were successful
    LOCAL liHandle as Integer, llRetVal as Logical 
    ** get the connection handle
    liHandle = This.DataSource
    IF liHandle > 0
      ** check to see if the app role is already set
      IF !This.AppRoleSet()
        ** it is not, so we set it
        IF SQLEXEC(liHandle, "exec sp_setapprole 'MyAppRole', 'MyAppRolePassword'") > 0
          llRetVal = .t.
          llRetVal = .f.
        ** app role was already set
        llRetVal = .t.
      ** we don't have a valid connection handle
      llRetVal = .f.
    RETURN llRetVal		
  PROTECTED FUNCTION AppRoleSet() as Logical
    ** checks to see if the app role is set
    LOCAL llRetVal as Logical, lcAlias as Character 
    ** store current alias
    lcAlias = ALIAS()
    ** check permissions on table
    IF SQLEXEC(This.DataSource, ;
      "select permissions(OBJECT_ID('" + This.Alias + "')) as iPermissions", ;
      'approlepermission') > 0

      ** if permissions returns 0, app role is not set
      llRetVal = (AppRolePermission.iPermissions <> 0)
      llRetVal = .f.
    ** clean up
    IF USED('AppRolePermission')
      USE IN AppRolePermission
    IF !EMPTY(lcAlias)
      SELECT (lcAlias)

    RETURN llRetVal

You can see that I have overridden the BeforeCursorFill method so that we can log into the application role. I have added two new methods; LogOnToAppRole and AppRoleSet. The AppRoleSet checks to see if the current connection has permissions. The LogOnToAppRole calls this method and if necessary, logs onto the application role.

To use this class we would do something like this:

LOCAL loCustomers as CursorAdapter
LOCAL liHandle as Integer

** create a connection to the database
liHandle = SQLCONNECT('Northwind')

IF liHandle > 0
  ** ok, we have a connection
  ** create a reference to my cursor adapter class
  loCustomers = NEWOBJECT('MyCursorAdapter', 'MyCursorAdapter.prg')

  ** set some properties, including my connection handle
  WITH loCustomers
    .DataSource = liHandle
    .DataSourceType = 'ODBC'
    .SelectCmd = 'Select * from customers'
    .Alias = 'Customers'

  ** fill the cursor
  IF loCustomers.CursorFill()


** disconnect the handle

As I stated earlier, just moving your data to SQL Server is not enough to ensure that your data is secure. There are still ways unauthorized people can get to and view your data. Using Application Role security is one way to make it a little more difficult for these people to get to your data. It takes a little set up on the server and a little more code to implement, but the extra security makes the time invested well worth it.

More articles from this author

1.SQL Server 2000: Virtual Fields and FunctionsNovember 2001

Copyright © 1993 - 2015 Level Extreme Inc., All Rights Reserved · Telephone: 506-547-9500 Email: info@universalthread.com · Privacy & Security · Copyright · Terms & Conditions