Export (0) Print
Expand All

Securing the Product Catalog Database

Commerce Server 2002

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).gif 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.

Ee797660.note(en-US,CS.20).gif 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

  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 namectlg_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 namectlg_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 namectlg_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 namectlg_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 namectlg_CatalogReaderRole
(Run-time users)
<VirtualCatalog Name>_<LANGUAGE>_Catalog
Select

Catalog Views

For the global catalog

Viewctlg_CatalogReaderRole
(Run-time user)
CatalogGlobal_LNG_NEUTRAL
Select

For each language in the Product Catalog System

Viewctlg_CatalogReaderRole
(Run-time user)
<CatalogGlobal_<LANGUAGE>
Select

For each catalog

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

For each catalog

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

Catalog-Specific Stored Procedures

Stored procedures for each catalog

Stored proceduresctlg_CatalogReaderRole
(Runtime user)
Business Desk user
ctlg_DeleteCategory_for_<Catalog Name>
No accessYes
ctlg_DeleteProduct_for_<Catalog Name>
No accessYes
ctlg_GetRelationships_for_<Catalog Name>
YesYes
ctlg_GetResults_for_AllColumns_<Catalog Name>
YesYes

Stored procedures per catalog language

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

Functions

Function Name

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

Copyright © 2005 Microsoft Corporation.
All rights reserved.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft