Securing the Product Catalog Database

  • Article

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:

Ee797660.important(en-US,CS.20).gifImportant

  • 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.

Ee797660.note(en-US,CS.20).gifNotes

  • 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

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
  2. In SQL Server Enterprise Manager, expand Security, and then click Logins.
  3. Right-click the SQL login account for the runtime users, and then click Properties.
  4. In the SQL Server Login Properties dialog box, click the Database Access tab.
  5. 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.
  6. 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)		 
CatalogAttributes
 Select 
CatalogDefinitions
 Select 
CatalogDefinitionProperties
 Select 
CatalogenumValues
 Select 
CatalogGlobal
 Select 
CatalogLanguage
 Select 
CatalogLanguageMap
 Select 
CatalogSchemaVersion
 Select 
CatalogSet_Catalogs
 Select 
CatalogSet_Info
 Select 
CatalogStatus
 Select 
CatalogToVendorAssociation
 Select 
CatalogUsedDefinitions
 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)		 
<Catalog Name>_CatalogDeletedProducts
 No Access 
<Catalog Name>_CatalogDependentCatalogs
 Select 
<Catalog Name>_CatalogHierarchy
 Select 
<Catalog Name>_CatalogProducts
 Select 
<Catalog Name>_CatalogRelationships
 Select

For each language in the catalog

Table name ctlg_CatalogReaderRole
(Run-time users)		 
<Catalog Name>_<LANGUAGE>_Catalog
 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)		 
<VirtualCatalog Name>_CatalogDeletedProducts
 No Access 
<VirtualCatalog Name>_CatalogHierarchy
 Select 
<VirtualCatalog Name>_CatalogIncludedProductsAndCategories
 No Access 
<VirtualCatalog Name>_CatalogPricingRules
 No Access 
<VirtualCatalog Name>_CatalogProducts
 Select 
<VirtualCatalog Name>_CatalogRelationships
 Select 
<VirtualCatalog Name>_CatalogVirtualProducts
 Select

For each language in the catalog

Table name ctlg_CatalogReaderRole
(Run-time users)		 
<VirtualCatalog Name>_<LANGUAGE>_Catalog
 Select

Catalog Views

For the global catalog

View ctlg_CatalogReaderRole
(Run-time user)		 
CatalogGlobal_LNG_NEUTRAL
 Select

For each language in the Product Catalog System

View ctlg_CatalogReaderRole
(Run-time user)		 
<CatalogGlobal_<LANGUAGE>
 Select

For each catalog

View ctlg_CatalogReaderRole
(Run-time user)		 
<Catalog Name>_ClassTypes
 Select 
<Catalog Name>_LNG_NEUTRAL
 Select

For each catalog

View ctlg_CatalogReaderRole
(Run-Time user)		 
<Catalog Name>_<LANGUAGE>_RelationsView
 Select 
<Catalog Name>_<LANGUAGE>
 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.

Ee797660.note(en-US,CS.20).gifNote

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 
ctlg_AddAttributeToCatalog
 No access Yes 
ctlg_AddCatalogDefinitionProperty
 No access Yes 
ctlg_AddCatalogLanguage
 No access Yes 
ctlg_AddCatalogLanguageMap
 No access Yes 
ctlg_AddChildCategoryToCategory
 No access Yes 
ctlg_AddColumnsToCatalog
 No access Yes 
ctlg_AddColumnsToRelationships
 No access Yes 
ctlg_AddDefaultToProperty
 No access Yes 
ctlg_AddDisplayNameToCatalog
 No access Yes 
ctlg_AddDisplayNameToProperty
 No access Yes 
ctlg_AddFullTextCatalogForLanguage
 No access Yes 
ctlg_AddParentCategoryToCategory
 No access Yes 
ctlg_AddPricingCategory
 No access Yes 
ctlg_AddProductToCategory
 No access Yes 
ctlg_AddPropertyAttribute
 No access Yes 
ctlg_AddPropertyForCSVImport
 No access Yes 
ctlg_AddPropertyToDefinition
 No access Yes 
ctlg_AddPropertyValue
 No access Yes 
ctlg_AddRelationshipToCategory
 No access Yes 
ctlg_AddRelationshipToProduct
 No access Yes 
ctlg_AlterClassTypesToProduct
 No access Yes 
ctlg_AlterClassTypesToProductFamily
 No access Yes 
ctlg_AlterColumnInTable
 No access Yes 
ctlg_AlterFullTextColumns
 No access Yes 
ctlg_AlterPropertyInFullTextCatalogs
 No access Yes 
ctlg_AlterPropertyLength
 No access Yes 
ctlg_CheckCatalog
 Yes Yes 
ctlg_CompleteCatalogImport
 No access Yes 
ctlg_CreateCatalogClassTypesView
 No access Yes 
ctlg_CreateCatalogLanguageView
 No access Yes 
ctlg_CreateCatalogLanguageViews
 No access Yes 
ctlg_CreateCatalogProcedures
 No access Yes 
ctlg_CreateCatalogSystem_LanguageView
 No access Yes 
ctlg_CreateCatalogSystem_LanguageViews
 No access Yes 
ctlg_CreateCategory
 No access Yes 
ctlg_CreateCategoryDefinition
 No access Yes 
ctlg_CreateFullTextCatalog
 No access Yes 
ctlg_CreateProduct
 No access Yes 
ctlg_CreateProductDefinition
 No access Yes 
ctlg_CreateProductVariant
 No access Yes 
ctlg_CreateProperty
 No access Yes 
ctlg_CreateTriggers
 No access Yes 
ctlg_DeleteCatalog
 No access Yes 
ctlg_DeleteCatalogDefinition
 No access Yes 
ctlg_DeleteImportProduct
 No access Yes 
ctlg_DeleteProductsFromDeletedTable
 No access Yes 
ctlg_DeleteProperty
 No access Yes 
ctlg_DeleteVariant
 No access Yes 
ctlg_DropCatalogLanguageView
 No access Yes 
ctlg_DropCatalogObjectFromDatabase
 No access Yes 
ctlg_DropCatalogProcedures
 No access Yes 
ctlg_DropCatalogProceduresAndViews
 No access Yes 
ctlg_DropFullTextCatalog
 No access Yes 
ctlg_DropViewOnCatalogGlobal
 No access Yes 
ctlg_EnsurePropTableMapExists
 Yes Yes 
ctlg_GetBaseCatalogName
 Yes Yes 
ctlg_GetCatalogDefinitionProperties
 Yes Yes 
ctlg_GetCatalogGlobalProperties
 Yes Yes 
ctlg_GetCatalogLanguages
 Yes Yes 
ctlg_GetCatalogLanguagesForExport
 Yes Yes 
ctlg_GetCatalogProperties
 Yes Yes 
ctlg_GetCatalogPropertiesForExport
 Yes Yes 
ctlg_GetCatalogPropertiesForUpdate
 No access Yes 
ctlg_GetCatalogRelationships
 Yes Yes 
ctlg_GetCatalogType
 Yes No access 
ctlg_GetCatalogs
 Yes Yes 
ctlg_GetCatalogsInCatalogSet
 Yes Yes 
ctlg_GetCategoryOID
 Yes Yes 
ctlg_GetCategoryProperties
 Yes Yes 
ctlg_GetDefinedCatalogAttributes
 Yes Yes 
ctlg_GetDefinedPropertyAttributes
 Yes Yes 
ctlg_GetDefinitionProperties
 Yes Yes 
ctlg_GetDefinitions
 Yes Yes 
ctlg_GetDefnPropertiesForCatalogImport
 No access Yes 
ctlg_GetDefnPropertiesForCSVImport
 No access Yes 
ctlg_GetDeletedProducts
 Yes Yes 
ctlg_GetDistinctPropertiesIncatalog
 Yes Yes 
ctlg_GetFTSColumnLCID
 No access Yes 
ctlg_GetFTSQuery
 Yes Yes 
ctlg_gethierarchy
 Yes Yes 
ctlg_gethierarchy2
 Yes Yes 
ctlg_GetHierarchy_For_VC
 Yes Yes 
ctlg_GetJoinType
 Yes Yes 
ctlg_GetParentCategories
 Yes Yes 
ctlg_GetPricingCategory
 Yes Yes 
ctlg_GetPricingCategoryOIDs
 Yes Yes 
ctlg_GetPrimaryParentCategory
 Yes Yes 
ctlg_GetProductCategory_UniqueID
 Yes Yes 
ctlg_GetProductOID
 Yes Yes 
ctlg_GetProductOIDAndType
 Yes Yes 
ctlg_GetProductProperties
 Yes Yes 
ctlg_GetProductsForExport
 No access Yes 
ctlg_GetProductVariantProperties
 Yes Yes 
ctlg_GetProperties
 Yes Yes 
ctlg_GetPropertiesForCatalog
 Yes Yes 
ctlg_GetPropertiesInCatalog
 Yes Yes 
ctlg_GetPropertiesInDefinitions
 Yes Yes 
ctlg_GetPropertiesUsedInCatalogs
 Yes Yes 
ctlg_GetPropertyAttributes
 Yes Yes 
ctlg_GetPropertyAttributesForDefinition
 Yes Yes 
ctlg_GetPropertyValues
 Yes Yes 
ctlg_GetRelationships_For_VC
 Yes Yes 
ctlg_GetResults
 Yes Yes 
ctlg_GetSearchableCategories
 Yes Yes 
ctlg_GetSmallestFulltextcatalog
 No access Yes 
ctlg_GetSourceCatalogsForExport
 No access Yes 
ctlg_GetSpecsearchableProps
 Yes Yes 
ctlg_GetSQLQuery
 Yes Yes 
ctlg_GetValidLanguage
 Yes Yes 
ctlg_ImportRelationship
 No access Yes 
ctlg_InsertCatalogGlobal
 No access Yes 
ctlg_InsertCatalogsBeingExportedOrImported
 No access Yes 
ctlg_InsertCatalogStatus
 No access Yes 
ctlg_InsertCSVCatalog
 No access Yes 
ctlg_InsertDefinition
 No access Yes 
ctlg_InsertDefinitionProperties
 No access Yes 
ctlg_InsertEnumValues
 No access Yes 
ctlg_InsertHierarchy
 No access Yes 
ctlg_InsertHierarchy2
 No access Yes 
ctlg_InsertUsedDefinition
 No access Yes 
ctlg_IsCatalogPropertyRemovable
 No access Yes 
ctlg_IsCatalogValidForFTSearch
 Yes Yes 
ctlg_IsCategorySpecSearchable
 Yes Yes 
ctlg_IsDefinitionPresent
 Yes Yes 
ctlg_IsDefinitionPropPresent
 Yes Yes 
ctlg_IsDefinitionUsedInCatalog
 Yes Yes 
ctlg_IsEnumPresent
 Yes Yes 
ctlg_IsProductvalid
 Yes Yes 
ctlg_IsPropertyPresentInDefinition
 Yes Yes 
ctlg_JoinQueryWithTable
 Yes Yes 
ctlg_PersistTT_EnsureExists
 Yes Yes 
ctlg_PersistTT_GetName
 Yes Yes 
ctlg_PersistTT_Purge
 Yes Yes 
ctlg_PersistTT_PurgeTablesUsingProperty
 Yes Yes 
ctlg_PurgeCatalog
 No access Yes 
ctlg_RecordCatalogExport
 No access Yes 
ctlg_RemoveAllCatalogLanguages
 No access Yes 
ctlg_RemoveCatalogAttribute
 No access Yes 
ctlg_RemoveCatalogLanguage
 No access Yes 
ctlg_RemoveChildCategoryForCategory
 No access Yes 
ctlg_RemoveColumnsFromRelationships
 No access Yes 
ctlg_RemoveDefinitionFromCatalog
 No access Yes 
ctlg_RemoveFullTextCatalogForLanguage
 No access Yes 
ctlg_RemoveParentCategoryForCategory
 No access Yes 
ctlg_RemoveProductFromCategory
 No access Yes 
ctlg_RemovePropertyAttribute
 No access Yes 
ctlg_RemovePropertyFromDefinition
 No access Yes 
ctlg_RemovePropertyValue
 No access Yes 
ctlg_RemoveRelationship
 No access Yes 
ctlg_RenameCatalogDefinition
 No access Yes 
ctlg_RenameCatalogProperty
 No access Yes 
ctlg_SetPricingCategory
 No access Yes 
ctlg_SetPrimaryParentCategory
 No access Yes 
ctlg_TableExistsInFullTextCatalog
 No access Yes 
ctlg_UpdateCategoryDisplayNames
 No access Yes 
ctlg_UpdateCategoryRelationships
 No access Yes 
ctlg_UpdateParentOids
 No access Yes 
ctlg_UpdateProductRelationships
 No access Yes 
ctlg_ValidateCatalogForCSVImport
 No access Yes 
ctlg_ValidateJoinTableInfo
 Yes Yes 
ctlg_VC_AddHierarchy
 No access Yes 
ctlg_VC_AddPriceRule
 No access Yes 
ctlg_VC_AddVCRule
 No access Yes 
ctlg_VC_ApplyCatalogLevelPriceRule
 No access Yes 
ctlg_VC_ApplyPriceRule
 No access Yes 
ctlg_VC_ApplyPriceRuleToDescendents
 No access Yes 
ctlg_VC_BC_GetDependentCatalogs
 Yes Yes 
ctlg_VC_DoesPriceRuleExist
 No access Yes 
ctlg_VC_DoesProdOrCategExist
 Yes Yes 
ctlg_VC_DoesVCRuleExist
 No access Yes 
ctlg_VC_EvaluateVCRule
 No access Yes 
ctlg_VC_ExcludeCatalog
 No access Yes 
ctlg_VC_ExcludeCategory
 No access Yes 
ctlg_VC_ExcludeCategoryDescendents
 No access Yes 
ctlg_VC_ExcludeProduct
 No access Yes 
ctlg_VC_ExcludeVariant
 No access Yes 
ctlg_VC_FTS_GetResults
 Yes Yes 
ctlg_VC_GetPriceRules
 Yes Yes 
sp_VC_GetProductOrVariantOrCategoryOID
 Yes Yes 
ctlg_VC_GetRelationshipDescColumns
 Yes Yes 
ctlg_VC_GetSourceCatalogs
 Yes Yes 
ctlg_VC_GetUserDefinedBCProperties
 No access Yes 
ctlg_VC_GetUserDefinedVCProperties
 No access Yes 
ctlg_VC_GetVCRules
 No access Yes 
ctlg_VC_ImportPriceRule
 No access Yes 
ctlg_VC_ImportRelationship
 No access Yes 
ctlg_VC_ImportRule
 No access Yes 
ctlg_VC_IncludeCatalog
 No access Yes 
ctlg_VC_IncludeCategory
 No access Yes 
ctlg_VC_IncludeCategoryDescendents
 No access Yes 
ctlg_VC_IncludeExcludeCatalog
 No access Yes 
ctlg_VC_IncludeProduct
 No access Yes 
ctlg_VC_IncludeVariant
 No access Yes 
ctlg_VC_IsVirtualCatalog
 Yes Yes 
ctlg_VC_MaterializeVCViews
 No access Yes 
ctlg_VC_PropagateBCChanges
 No access Yes 
ctlg_VC_PropagateBCChanges_DeleteCatalog
 No access Yes 
ctlg_VC_PropagateBCChanges_Hierarchy
 No access Yes 
ctlg_VC_PropagateBCChanges_PrimaryParentCategory
 No access Yes 
ctlg_VC_PropagateBCChanges_ProductCategory
 No access Yes 
ctlg_VC_PropagateBCChanges_Relationship
 No access Yes 
ctlg_VC_PropagateBCChanges_Variant
 No access Yes 
ctlg_VC_PropagateDependentCatalogChanges
 No access Yes 
ctlg_VC_PropagateVCChanges_DeleteCatalog
 No access Yes 
ctlg_VC_RebuildVirtualCatalog
 No access Yes 
ctlg_VC_ReEvaluateAllPricingRules
 No access Yes 
ctlg_VC_ReEvaluateAllVCInclusionOrExclusionRules
 No access Yes 
ctlg_VC_ReEvaluateAllVCRules
 No access Yes 
ctlg_VC_RemovePriceRule
 No access Yes 
ctlg_VC_RemoveVCRule
 No access Yes 
ctlg_VC_Reset_Evaluated_Tables
 No access Yes 
ctlg_VC_ResetUserDefinedLinks
 No access Yes 
ctlg_VC_SetVCDirtyFlag
 No access Yes 
ctlg_VC_SetVCRebuildStatus
 No access Yes 
ctlg_VC_SetVCStatus
 No access Yes 
ctlg_VC_SplitVCIdentifierName
 Yes Yes 
ctlg_VC_UpdateDependentVCs_DeletedProductsTables
 No access Yes 
ctlg_VC_UpdateVCs_Dependent_On_DefinitionName
 No access Yes 
ctlg_VC_UpdateVCs_Dependent_On_PropertyName
 No access Yes

Catalog-Specific Stored Procedures

Stored procedures for each catalog

Stored procedures ctlg_CatalogReaderRole
(Runtime user)		 Business Desk user 
ctlg_DeleteCategory_for_<Catalog Name>
 No access Yes 
ctlg_DeleteProduct_for_<Catalog Name>
 No access Yes 
ctlg_GetRelationships_for_<Catalog Name>
 Yes Yes 
ctlg_GetResults_for_AllColumns_<Catalog Name>
 Yes Yes

Stored procedures per catalog language

Stored procedures ctlg_CatalogReaderRole
(Run-Time user)		 Business Desk user 
ctlg_AncestorCategories_for_<Catalog Name>_<LANGUAGE>
 Yes Yes 
ctlg_GetCategoryResults_For_<Catalog Name>_<LANGUAGE>
 Yes Yes 
ctlg_GetChildCategories_for_<Catalog Name>_<LANGUAGE>
 Yes Yes 
ctlg_GetDescProds_for_<Catalog Name>_<LANGUAGE>
 Yes Yes 
ctlg_GetDescProdsOnly_for_<Catalog Name>_<LANGUAGE>
 Yes Yes 
ctlg_GetProductsFor_<Catalog Name>_<LANGUAGE>
 Yes Yes 
ctlg_GetRootCategories_for_<Catalog Name>_<LANGUAGE>
 Yes Yes 
ctlg_GetRootProducts_for_<Catalog Name>_<LANGUAGE>
 Yes Yes 
ctlg_Specsearch_for_<Catalog Name>_<LANGUAGE>
 Yes Yes

Functions

Function Name

 ctlg_CatalogReaderRole
(Run-Time user)		 Business Desk user 
fn_AddTablePrefixToColumnList
 Yes Yes 
fn_CS_AppendClause
 Yes Yes 
fn_IsSingleLanguageCatalog
 Yes Yes 
fn_VC_GetMungedIdentifier
 Yes Yes 
GetSQLDataType
 Yes Yes

Copyright © 2005 Microsoft Corporation.
All rights reserved.