MTS provides a flexible security model. As with most software products, flexibility can lead to complexity. This exploration of MTS security includes setting permissions on components and packages along with the relationship between MTS security and SQL Server 2000 security.
MTS security relies on the configuration of users in the NT domain. Users and groups within an NT domain can be assigned to roles in MTS. These roles are in turn granted permissions on MTS objects.
Package Security Options
Only a couple of steps are necessary to implement security within a package. If you look back at Figure 43.8, the Bank.Account component shows that this component has Authorization Checking enabled, and it was noted that this component didn't have a role set up for it. What this means is that the component will use the security set up for the package.
Roles are mechanisms in MTS to which permissions are assigned. Roles consist of groups or users from an NT domain. Roles are defined on a per-package basis. In other words, each package might have a different set of roles with which to work. The following example creates a new role called Execs for the Sample Bank package:
Setting Role Membership for a Component
Each component within a package can have its own security settings. The Role Membership folder is the mechanism for designating which roles have access to which components. The following example grants the Execs role access to the Bank.Account component only:
How Do MTS and SQL Server Security Relate?
You can combine MTS security and SQL Server 2000 security in several ways. This section addresses two primary scenarios. The first scenario is when you want to grant access to the database on a per-user basis, and the second scenario is when you want to grant access on a per-package basis. Both of these scenarios assume that SQL Server is using SQL Server and Windows NT security (Mixed Mode Authentication).
Scenario 1: Per User
In this situation, you should build each MTS component to accept database usernames and passwords as parameters. The component can then use these parameters to open a connection. To set up this scenario, you must give access to the MTS component to each user through roles, and you must also give each user a login for the database.
Scenario 2: Per Package
In this scenario, all components in a package call the database under one database login. MTS can validate individual users by checking a component's role membership. After MTS validates the users, the users then perform all database activities under a single database login. This security scheme enables effective use of connection pooling.
Other Security Considerations
You must keep several other issues in mind regarding MTS security: