SQL Server 7.0, as well as SQL Server 2000, support several technological feature enhancements that allow for a faster time to completion for database and application development. Application roles exist as SQL Server database objects that allow developers and database administrators to assign SQL Server database permissions to applications instead of individuals. Instead of database administrators tracking the creation, removal and changes of SQL Server users and their associated permissions, administration for a user’s security context is simplified through the ability to assign an application database rights and permissions.
The Value of Application Roles
Business organizations may require several separate applications ranging in function and purpose to support the day-to-day operations of the enterprise. Similarly, many organizations may require a small group or a single application that is based in an architecture that will require application development to scale with business growth. In many of these instances, there are typically several functional requirements that an application must fulfill. As a result, an n-tier environment with SQL Server participating in or functioning as the data tier must allow for database security to be maintained, while granting the application the ability to select, insert, update and delete from database objects. In many instances business management may wish to deny the direct access of certain tables and database objects from users. Simultaneously, those same users may need to perform operations on and view those objects through an application.
The requirement of a business to deny users direct access to data through programs such as the Microsoft SQL Server Query Analyzer or Microsoft Excel while allowing those users to access SQL Server data through a designated application can be easily and flexibly accommodated through the implementation of SQL Server applications roles. Application roles instantiate a predefined security context once the user launches the application and a database connection is required. The application, upon connection to the database, activates the application role which immediately rescinds all SQL Server permissions associated with the authenticated user, thereby allowing the application to function within the parameters granted to it by the application role.
Application Role Characteristics
Application roles exist as separate database objects in SQL Server. Administrators create application roles and assign permissions in a similar manner to creating users and granting permissions. Once the application role is established, application developers write code to connect to the database and call the SQL Server system stored procedure sp_setapprole. This stored procedure expects two parameters indicating the name of the application role along with the application role password. This interchange of sensitive information can be encrypted using the multiprotocol configuration utility found in SQL Server 7.0/2000 (client and server network utilities).
Application Role Details
- Application Role State
- Application roles by default are inactive and require a calling application to activate the role for the connection session. Passing the application role name activates the application role and password through the sp_setapprole system stored procedure subsequent to connection.
- Application roles exist as memberless objects. In other words, Microsoft NT groups, users and roles are not applied to the application role.
- Application Role Connection Sequence
- When an application requires a database connection, the authenticated user’s NT credentials are used to gain connectivity to the server and the database.
- All users using the application must have the right to access the SQL Server via NT account or, more preferably, NT group membership.
- Subsequently, the application then calls the sp_setapprole system stored procedure, passing the application role name and the password (often encrypted). The procedure then accepts these parameters and then activates the application role for the user’s current session.
- Upon activation, all authenticated user permissions, or lack thereof, are revoked. The application then assumes the abilities associated with the application role.
- Relevant Application Role Issues
- Individual user auditing is still possible due to the authenticated user’s NT credentials. The application role does prevent the persistence of the user’s NT identifications. This allows tables which capture default information such as suser_sname() to continue to capture individual user information.
- Once an application activates an application role, the user’s permissions are rescinded for the length of the connection. SQL Server permissions associated with an application user’s NT credentials cannot be restored or altered within a session.
- Application roles may require that the role name and password be incorporated (hard coded) directly into the application. There are solutions in order to prevent hard coding of application role names discussed later in this document.
Implementation Scenarios
Because of the flexibility granted to application developers by application roles, there are differing scenarios in which application roles can be utilized.
- Leave SQL Server Permissions Unassigned
- If direct database access for NT users is not required, SQL Server user permissions can be revoked, or remain unassigned.
- In this manner users will be able to authenticate with SQL Server, but not gain access to database resources with any tools except the application that calls the appropriate application role.
- This approach will ensure security and database integrity and relieve the concentration of erroneous modifications of tables by independent users. Database operations will be strictly limited to those sanctioned by the applications.
- Manage existing SQL Server user permissions
- If direct access to the database is required for users with other applications, such as Microsoft Excel or Microsoft Access for reporting, maintenance of user NT groups that are granted database permissions is valid.
- Database administrators will be required to simultaneously administer privileges to NT groups as well as application roles.
Invariably, both these strategies can be utilized simultaneously. A database administrator, in conjunction with a network administrator can coordinate to create NT groups which will require direct database access, as well as groups which will be assigned to SQL Server permissions. Application roles can be created as necessary in order to fulfill their business-defined role.
Managing Application Roles in an n-tier Architecture
The Value of COM+
In an n-tier environment that utilizes WindowsDNA2000 architecture, an application (rich or thin client) must communicate with a database tier. One method to achieve this function is to have the application query the database directly. The application maintains a thread into the database upon connection instantiation and is responsible for activating the application role. Connection management and thread pooling to maximize resources cannot be implemented in this type of environment. As a result, this type of application’s performance cannot scale with the business’s needs and performance may actually suffer without a tier to manage application connectivity.
COM+ (also known as Microsoft Transaction Server – MTS in NT 4.0) serves as the intermediary layer to handle business logic and complex application operations in the context of database interactivity. In other words, the application will communicate directly with the COM+ layer in order to initiate business level operations. The COM+ layer, on the application’s behalf, will initiate communication with the database, activate the application role and complete business transactions. The COM+ layer contains the ability to group operations in logical business objects and manage threads and connectivity to the database in an efficient manner. As a result, maximization of network resources in relation to application to database communication is achieved.
Also, the development of applications can be simplified by the reuse of existing COM+ objects. COM+ also contains a load-balancing feature that ensures the maximization of application responsiveness.
The client should be designed to call COM+ objects which then instantiate a managed connection to the database. Creating COM+ objects in the middle tier that performs functionally different tasks can further diversify application development. For example, a COM+ object can reference another object whose sole purpose is to manage database connectivity.
Application Roles, although solely SQL Server 7.0/2000 database tier objects, will interact exclusively with the COM+ object that manages database connectivity. The client application will call for the activation of the application role through the COM+ object and the object will subsequently activate the application role. Subsequently, all operations conducted by the user through the application will be formally sanctioned by the application role.
Managing Multiple Application Roles
In an environment where varying levels of database access are required (direct and application based access), the creation of multiple application roles to accommodate for those business variations in information access is sanctioned. For example, if an application role exists with administrative purposes while an application role exists for users with minimum database privileges, a combination of those permissions may need to exist in a hybrid fashion to meet new business needs. As a result, database administrators should then create a new application role to meet those needs.
Once new application roles are created, developers must accommodate for these new role names in the application in order for them to be utilized. In many cases, the simplest solution for developers is to incorporate the application role name and password directly into the code of the client. Unfortunately, if business needs call for the creation of hybrid application roles, developers will be required to revise these roles coded into the application for each group of users which require a certain level of access. As a result, multiple versions of the same application may occur within the organization, each activating a different application role.
An alternative approach would be to dynamically manage the application role deployment among client applications. One strategy would be to maintain a list of NT user names/groups mapped to an existing application role in a SQL Server table. A SQL Server login (not an NT login) can then be created to provide the connecting application access to this application role mapping table only. The application will be coded with this SQL Server login.
Subsequently, when a user activates an application, the application will log into SQL Server using this dedicated SQL Server login and lookup the current user (using the NT username identity of the current user stored in a variable) and determine which associated application role to activate. The application role name and password are stored within the role mapping table and protected by SQL Server security. The application role and password can then be returned to the application/COM+ object and stored in variable form. The connection with the database is severed and a new trusted connection with the SQL Server must be established. Once the user authenticates, the application will then pass the application name and password to the SQL Server in order to activate the appropriate application role.
This arrangement allows for database administrators to dynamically alter the names of application roles for specific NT users or NT user groups or create and assign new ones as business demands change.
Application Role functionality is often overlooked as developers strive to attain equal functionality by engineering a process to attain similar goals. The use of application roles in application development and deployment prevent developers from having to “reinvent the wheel” by programming for existing functionality inherent in the SQL Server 7.0/2000 product. Application roles are indeed an essential component in the design and deployment of contemporary n-tier applications that are created to scale to meet business requirements.
