Controlling Package Access

Controlling package access is about providing a way to prohibit certain users from opening or even seeing packages. The way you do this heavily depends on where the package is stored.

Stored in the Integration Services Server

Many, even advanced and corporate SSIS users, prefer to store their packages as files on the file system rather than in SQL Server. The operating system provides the ability to control access to files so file system security is often overlooked as an alternative for controlling package access. However, for those packages and other files that are stored on the file system, including those packages stored in the Integration Services Stored Packages node, it is a viable and worthwhile option.

Caution

This chapter assumes that you also take all the precautions necessary to protect your systems. For example, you should prevent physical access, place passwords on the BIOS, turn off BIOS bootup sequences that allow for booting from CD or floppy disks, use a firewall, use least-needed privileges, and so on. How to secure a machine could, of course, constitute an entire book in itself. This chapter only discusses Integration Servicesspecific security topics. All other security best practices should be followed.

Simple File Sharing

Simple File Sharing is always turned on in Windows XP Home Editionbased computers and on by default for Windows XP Professionalbased computers that are joined to a workgroup. Windows XP Professionalbased computers that are joined to a domain use only the classic file sharing and security user interface. When you use the Simple File Sharing UI, both share and file permissions are automatically configured. Simple File Sharing is intended to be used mostly on home computers. If Simple File Sharing is turned on, you do not have as much flexibility to modify the file permissions and the Security tab for setting file security is not visible.

If you turn off Simple File Sharing, you have more control over the permissions to individual users and groups. However, you should fully understand what you're doing and should have advanced knowledge of NTFS and share permissions to help keep your folders and files more secure.

To turn Simple File Sharing off on Windows XP Professional and higher edition-based computers, open My Computer and select the Tools, Folder Options menu item. Then select the View tab. Scroll down to the bottom of the list and remove the check mark in the Use Simple File Sharing (Recommended) check box, as shown in Figure 18.1.

Figure 18.1. Turning off Simple File Sharing

To use Windows XP file system security to protect package access, right-click on the folder or package you want to protect, select the Properties menu item, and select the Security tab. Figure 18.2 shows the Security tab for a folder called Packages2 on a sample machine. Folder access permissions are inherited; so any subfolder or file in the Packages2 folder by default has the same permissions as those specified in this dialog box.

Figure 18.2. Setting security options for a folder

Note

The main difference between the Security tab for a folder and a file is that the folder tab has, for obvious reasons, an additional permission option called List Folder Contents.

The Security tab allows you to set access rights for individuals or entire Windows System Groups. When setting the Deny rights as shown in Figure 18.2, user Kirk (DIGITALDARKROOM\Kirk) is unable to read, execute, or enumerate file contents. If Kirk attempts to access a package within that folder, or even enumerate the packages within it, using SQL Server Management Studio, he receives the error shown in Figure 18.3.

Figure 18.3. Attempting to enumerate packages through SQL Server Management Studio in a folder with restricted permissions

Using this method, you can selectively grant and deny access to different users or groups in your domain by simply modifying the parent folder security permissions. Because the Integration Services server can enumerate multiple folders and display them in the Stored Packages node of SQL Server Management Studio, it's possible to segment the package store into folders with different visibility depending on the user trying to access the folders.

Stored in SQL Server

SSIS uses three standard SQL Server roles to control package access to packages stored in SQL Server.

db_dtsoperator

This is the most limited role. By default, users in this role can only enumerate and execute packages, but cannot create or modify packages in SQL Server.

db_dtsltduser

This role lets users create new packages, modify packages they previously created, and enumerate all other existing packages.

db_dtsadmin

This role lets users create, modify, enumerate, and view all packages. Integration Services stored procedures automatically include sysadmins in the db_dtsadmin role.

A user must be in one of these three roles to enumerate SSIS packages stored in SQL Server because these are the only roles that have access to the following stored procedures. You can find the new SSIS roles in the Management Studio MSDB database node under the Security, Roles, Database Roles node. These roles apply only to MSDB and control access to the sysdtspackages90 table through the stored procedures that Table 18.1 shows. You can find the sysdtspackages90 table and the stored procedures for accessing it in MSDB as well. Because roles rely on the SQL Server roles feature, Integration Services roles are only available for packages stored in SQL Server and do not affect access to packages stored on the Integration Services server or on the file system.

sp_dts_getfolder

sp_dts_addfolder

sp_dts_deletefolder

sp_dts_renamefolder

sp_dts_listfolders

sp_dts_getpackage

sp_dts_putpackage

sp_dts_deletepackage

sp_dts_listpackages

sp_dts_getpackageroles

sp_dts_setpackageroles

To open the Package Roles dialog box, right-click a package in the Packages node and select Package Roles. Figure 18.4 shows the default values the dialog box displays when the Reader and Writer roles are null. When the readrolesid column is null, all users in any IS package role can enumerate and read that package. When the writerolesid column is null, only users in the db_dtsadmin role and the creator of the package can modify or delete the package.

Figure 18.4. Default roles for packages stored in SQL Server

Figure 18.5 shows part of the sysdtspackages90 table in MSDB. There are three columns of particular interest to this discussion. The readrolesid column contains the SID for the user or group that has read access to the package. Readers can enumerate, execute, and export a package from SQL Server. The writerolesid column specifies the SID for the user or role that has write access to the package. Writers can delete, rename, edit, and save a package to SQL Server. By default, these two columns are null.

Figure 18.5. Reader and Writer role SIDs in the sysdtspackages90 table

The third column, ownersid, is not visible in Figure 18.5. It contains the SID of the user who created the package and is stored there to ensure that the user who created the package will always have access to it.

Note

A SID is a security identifier and is used within NT/XP. The SID assigned to a user becomes part of an access token, which is then attached to any action attempted or process executed by that user or group. The SID, under normal operation, is unique and identifies an individual object, such as a user, group, or machine.

You can also assign users and roles to the Writer role to let them perform write operations, such as delete and modify, to that package only. To better understand how these roles interact and see which role has what rights, take a look at Table 18.2, which shows the rights assigned to each role and the activities that a user in each role can perform by default.

Table 18.2. The Rights Assigned to the Standard Roles