MORE INFORMATION
The security system in Microsoft SQL Server is implemented
at the level of the database. This is the best and most robust method for
controlling user activities regardless of the application used to communicate
with SQL Server. However, there may be situations requiring custom security
controls to accommodate the special requirements of an individual application.
This is especially true when dealing with complex databases and databases with
large tables.
The fundamental differences between standard and
application roles are:
- Application roles contain no members. Users, Microsoft
Windows NT(r) groups, 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(s). A user's
association with an application role is due to being capable of running an
application that activates the role, rather than being a member of the role.
- Application roles are inactive by default and require a
password to be activated by using the sp_setapprole system stored procedure.
The password can be provided by the user, for example, through an application
prompt, but it is more likely that the password is incorporated within the
application. The password can be encrypted as it is sent to SQL Server.
- 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. Because application roles are applicable only to the database in which
they exist, the connection can gain access to another database only by virtue
of permissions granted to the guest user account in the other database.
Therefore, if the guest user account does not exist in a database, the
connection cannot gain access to that database. If the guest user account does
exist in the database but permissions to access an object are not explicitly
granted to guest, the connection cannot access that object regardless of who
created the object. The permissions the user gained from the application role
remain in effect until the connection logs out of SQL Server.
The sp_addapprole system stored procedure is used to implement
an application role. The Transact SQL syntax for sp_addapprole is:
sp_addapprole [@rolename =] 'role',
[@password =] 'StrongPassword'
The @rolename argument is a character string for the name of the role.
The role is a sysname with no default, that must be a valid identifier that
does not already exist in the database.
@password is a character
string password that must be passed to the sp_setapprole stored procedure and
is required to activate the application role.
Only members of the
db_owner and db_securityadmin fixed database roles can execute the
sp_addapprole system stored procedure.
The application role is
activated from an application with the sp_setapprole stored procedure. The
Transact SQL syntax for sp_setapprole is:
sp_setapprole [@rolename =] 'role' ,
[@password =] {Encrypt N 'StrongPassword'} | 'StrongPassword'
[,[@encrypt =] 'encrypt_style']
The @rolename argument is the name of the application role that is
defined in the current database.
The @password argument is the
password that is required to activate the application role. When using the
Encrypt function, the password must be converted to a Unicode string by
preceding the password with N.
The @encrypt argument specifies the
encryption style used by password.
Any user can execute the
sp_setapprole system stored procedure by providing the correct password for the
role.
The following code example describes how to programmatically
create and activate an application role from Visual FoxPro.
Note You must make sure that the user has the appropriate rights
PUBL gcSQLServer
LOCAL lcSQLDataPath, lcConnStr
*!* Insert your SQL Server name and DATA path here.
gcSQLServer = "sphinxsql"
lcSQLDataPath = "d:\mssql7\data\"
lcConnStr = "DRIVER=SQL Server;SERVER=" + gcSQLServer + ";DATABASE=MASTER;uid=UserNamesa;pwd=StrongPassword"
gnConnHandle = SQLSTRINGCONNECT(lcConnStr)
*!* Select the Master Database.
Use_Master=sqlexec(gnConnHandle,'USE MASTER')
IF Use_Master>0
*!* Determine whether or not the database exists.
*!* If not, create it.
sqlcommand="SELECT * FROM SYSDATABASES WHERE NAME = 'TESTROLE'"
nCheck_Db=sqlexec(gnConnHandle,sqlcommand,'check_db')
IF RECCOUNT('check_db')<1
*!* Create a 1 MB database with a 1 MB log file.
sqlcommand="create database testrole "+ CHR(13) + ;
" on primary "+ CHR(13) + ;
" (name = 'appdata', "+ CHR(13) + ;
" filename = '" + lcSQLDataPath + "testrole.mdf', "+ CHR(13) + ;
" size = 1, "+ CHR(13) + ;
" maxsize = 2, "+ CHR(13) + ;
" filegrowth = 512 kb) "+ CHR(13) + ;
" log on (name = 'applog', "+ CHR(13) + ;
" filename ='" + lcSQLDataPath + "testrole.ldf', "+ CHR(13) + ;
" size = 1, "+ CHR(13) + ;
" maxsize = 3, "+ CHR(13) + ;
" filegrowth = 512 kb) "
nmake_data_file=sqlexec(gnConnHandle,sqlcommand)
ENDIF
*!* Use the database.
nuse_testrole=sqlexec(gnConnHandle,'USE TESTROLE')
*!* Determine whether or not the testing table exists.
sqlcommand="SELECT * FROM SYSOBJECTS WHERE NAME='TESTING'"
ncheck_tbl=sqlexec(gnConnHandle,sqlcommand,'systables')
IF RECCOUNT('systables')=0
*!* The table doesn't exist, so create it.
sqlcommand="CREATE TABLE TESTING (CHARFLD CHAR(10) NULL) "
nmake_table=sqlexec(gnConnHandle,sqlcommand)
ENDIF
*!* Add an application role.
sqlcommand="SELECT * FROM SYSUSERS WHERE NAME = 'APPTEST'"
nCheck_role=sqlexec(gnConnHandle,sqlcommand,'approles')
IF RECCOUNT('approles')=0
c_role_string="sp_addapprole @rolename = 'apptest', "+ CHR(13) + ;
"@password = 'app_pwd'"
check_role=sqlexec(gnConnHandle,c_role_string)
ENDIF
*!* Open a new connection to SQL Server and try to insert into the table.
=check_app()
*!* Grant All privileges on the testing table to the application role.
c_grant_string="GRANT ALL ON TESTING TO apptest"
check_grant=sqlexec(gnConnHandle,c_grant_string)
*!* Open a new connection to SQL Server and try to insert into the table.
=check_app()
ENDIF
=sqldisconn(gnConnHandle)
PROCEDURE check_app
*!* The application role is only activated in this procedure because
*!* this procedure has a separate connection to the server.
*!* Create a New connection to SQL Server.
lcConnStr = "DRIVER=SQL Server;SERVER=" + gcSQLServer + ";DATABASE=MASTER;uid=UserName;pwd=StrongPassword"
gnconnhandleb=SQLSTRINGCONNECT(lcConnStr)
*!* Use the database.
gnselectdatabase=sqlexec(gnconnhandleb,'USE TESTROLE')
IF gnselectdatabase>0
*!* Activate the application role.
gnsetrole=sqlexec(gnconnhandleb,"sp_setapprole 'apptest','app_pwd'")
IF gnsetrole>0
*!* Attempt to insert into the testing table.
gninsert=sqlexec(gnconnhandleb,"INSERT INTO TESTING VALUES ('TEST')")
IF gninsert>0
WAIT WINDOW "The application has privileges to insert data into this table."
ELSE
WAIT WINDOW "The application was UNABLE to insert data into this table."
ENDIF
ENDIF
ENDIF
=sqldisconn(gnconnhandleb)
When the code is executed and check_app is called before the
application role has been granted object permissions on the Testing table, a
Wait window with the following message displays:
The
application was unable to insert data into this table.
When the
check_app procedure is called after the application role has been granted
object permissions on the Testing table, a Wait window with the following
message displays:
The application has privileges to
insert data into this table.