Implementing Application Role Security to Secure your SQL Server Data
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_owner||Has all permissions in the database.|
|db_accessadmin||Can add or remove user IDs.|
|db_securityadmin||Can manage all permissions, object ownerships, roles and role memberships.|
|db_ddladmin||Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements.|
|db_backupoperator||Can issue DBCC, CHECKPOINT, and BACKUP statements.|
|db_datareader||Can select all data from any user table in the database.|
|db_datawriter||Can select all data from any user table in the database.|
|db_denydatareader||Cannot select any data from any user table in the database.|
|db_denydatawriter||Cannot 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.
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.
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.
|None||The password is not encrypted and is passed as plaintext.|
|ODBC||The 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:
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.
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')
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)
** log onto the app role
llRetVal = This.LogOnToAppRole()
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
** 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.
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
USE IN AppRolePermission
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
.DataSource = liHandle
.DataSourceType = 'ODBC'
.SelectCmd = 'Select * from customers'
.Alias = 'Customers'
** fill the cursor
** 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.