Securing the Product Catalog Database
It is strongly recommended that you use Windows Authentication for access to your databases. When you configure your database connection strings for Windows Authentication, you must assign Business Desk users and run-time users (who use an anonymous domain account) the appropriate level of access to your databases.
This topic explains the permissions that the Business Desk user and run-time users require to access the Catalog database.
Business Desk Users
To enable Business Desk users to edit catalogs (for example, edit the catalog definition, rebuild the catalog, refresh the full-text index and publish the catalog), you must assign them to the db_owner role.
Carefully consider the trustworthiness of the Business Desk user that you assign to the db_owner role. When assigning a Business Desk user to this role, you must consider the following security risk:
Important
- A Business Desk user assigned to the db_owner role could potentially delete a database. To mitigate this risk, you must create a firewall to prevent direct connection from the Business Desk client to the SQL Server database that contains the catalogs. This is the recommended secure configuration. For detailed instructions, see Deploying a Secure Site.
If you are using BizTalk Server to exchange catalogs with trading partners, the CatalogToVendorAssociation.GetVendorList() API requires access to the BizTalk Messaging Management database (InterchangeBTM). On the InterchangeBTM database, you must grant Execute permission on btsint_organizations_selectvalidvendors stored procedure. Without this permission, the CatalogToVendorAssociation.GetVendorList() API will fail.
Run-Time Users
When using Windows authentication, you must assign run time users who visit your Web site the appropriate level of access to your catalogs. A script will be made available in Commerce Server Service Pack 2 that will configure the appropriate access.
Notes
- Members of the ctlg_CatalogReaderRole must have db_reader permissions on the Administration database. This is required so they can retrieve the connection string to the Product Catalog database and view your catalogs.
- If you are using join keys to link to external tables, you must grant members of the ctlg_CatalogReaderRole Select permissions on those tables as well.
For enhanced security, remove the runtime catalog users in the MSCS_CatalogScratch database from the db_owner role, and add them to the db_ddladmin role. Add any new runtime users to the db_ddladmin role; do not add new users to the db_owner.
The following procedures assume you have already created a SQL login account for the runtime users.
To add runtime users to the db_ddladmin role
- Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
- In SQL Server Enterprise Manager, expand Security, and then click Logins.
- Right-click the SQL login account for the runtime users, and then click Properties.
- In the SQL Server Login Properties dialog box, click the Database Access tab.
- In the top box, select the MSCS_CatalogScratch database, and then in the bottom box, uncheck the db_owner check box, and specify the db_ddladmin role.
- Click OK to save your changes.
Global Catalog Tables
The CatalogSecurityRoles.sql script grants Select access to the ctlg_CatalogReaderRole for the following Product Catalog tables:
Table name | ctlg_CatalogReaderRole (Run-time users) |
---|---|
|
Select |
|
Select |
|
Select |
|
Select |
|
Select |
|
Select |
|
Select |
|
Select |
|
Select |
|
Select |
|
Select |
|
Select |
|
Select |
Base Catalog Tables
The following tables list the base catalog tables for each base catalog.
For each base catalog
Table name | ctlg_CatalogReaderRole (Run-time users) |
---|---|
|
No Access |
|
Select |
|
Select |
|
Select |
|
Select |
For each language in the catalog
Table name | ctlg_CatalogReaderRole (Run-time users) |
---|---|
|
Select |
Virtual Catalog Tables
The following tables list the virtual catalog tables for each base catalog.
For each virtual catalog
Table name | ctlg_CatalogReaderRole (Run-time users) |
---|---|
|
No Access |
|
Select |
|
No Access |
|
No Access |
|
Select |
|
Select |
|
Select |
For each language in the catalog
Table name | ctlg_CatalogReaderRole (Run-time users) |
---|---|
|
Select |
Catalog Views
For the global catalog
View | ctlg_CatalogReaderRole (Run-time user) |
---|---|
|
Select |
For each language in the Product Catalog System
View | ctlg_CatalogReaderRole (Run-time user) |
---|---|
|
Select |
For each catalog
View | ctlg_CatalogReaderRole (Run-time user) |
---|---|
|
Select |
|
Select |
For each catalog
View | ctlg_CatalogReaderRole (Run-Time user) |
---|---|
|
Select |
|
Select |
Catalog Stored Procedures
The CatalogSecurityRoles.sql script grants the appropriate access to the following stored procedures. Also listed is the access that the design-time user account should have.
Note
There is a hotfix tool available for you so that you can apply updates to stored procedures for resources and sites that you specify. You use the hotfix update tool to do the following:
- Display information about available hotfixes. You can list all the available hotfixes, or only the hotfixes for a specific Commerce Server resource, or you can display information for a specific hotfix for a given the number. The tool lists only those hotfixes that need stored procedure updates. The description includes the following information:
- The resource to which the hotfix applies.
- The hotfix number.
- The stored procedures updated by the hotfix.
- A brief description of the hotfix.
- Apply all the hot fixes for a specified resource or apply a hotfix by specifying the hotfix number. You can apply the hotfix to a specific site by specifying the site name, or you can apply the hotfix to the appropriate database by specifying the connection string.
Each time you run the hotfix tool, it appends new entries to the end of the log files and StoredProcedureBackupText file. The new entries are separated by a date and time stamp.
After you install a hotfix, always check the contents of the log files.
Catalog stored procedures | ctlg_CatalogReaderRole (Run-Time user) |
Business Desk user |
---|---|---|
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
Yes | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
No access | Yes |
|
Yes | Yes |
|
Yes | No access |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
No access | Yes |
|
No access | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
No access | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
No access | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
No access | Yes |
|
No access | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
Yes | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
Yes | Yes |
|
No access | Yes |
|
Yes | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
Yes | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
|
Yes | Yes |
|
No access | Yes |
|
No access | Yes |
|
No access | Yes |
Catalog-Specific Stored Procedures
Stored procedures for each catalog
Stored procedures | ctlg_CatalogReaderRole (Runtime user) |
Business Desk user |
---|---|---|
|
No access | Yes |
|
No access | Yes |
|
Yes | Yes |
|
Yes | Yes |
Stored procedures per catalog language
Stored procedures | ctlg_CatalogReaderRole (Run-Time user) |
Business Desk user |
---|---|---|
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
Functions
Function Name |
ctlg_CatalogReaderRole (Run-Time user) |
Business Desk user |
---|---|---|
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
|
Yes | Yes |
Copyright © 2005 Microsoft Corporation.
All rights reserved.