Securing the Profiles 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 levels of access to your databases.

Systems administrators also require access to the Profiles database and should be assigned to the db_owner role.

To help you secure the Profiles database, Commerce Server includes two security scripts: ProfileReaderRole.sql and ProfileWriterRole.sql. These scripts are located in the Program Files\Microsoft Commerce Server\Support folder.

These scripts create two roles on the Profiles database, and assign the necessary permissions to the tables and stored procedures:

  • ProfileReaderRole. Assign run-time users to this role.
  • ProfileWriterRole. Assign design-time users to this role.

To create the ProfileReaderRole and the ProfileWriterRole

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click SQL Query Analyzer.

  2. In the Connect to SQL Server dialog box, specify the appropriate SQL server.

  3. In Query Analyzer, in the database drop-down box, select the Profiles database.

  4. Click File, and then click Open.

  5. Navigate to the scripts located in the Program Files\Microsoft Commerce Server\Support folder, and select ProfileReaderRole.

    The script opens and the code appears in the Query Analyzer window.

  6. On the toolbar, click Run to run the script against the Profiles database.

  7. Repeat these steps to run the ProfileWriterRole script.

  8. After you create the roles, assign the anonymous run-time user account and the Business Desk group account to the appropriate roles. For instructions, see Assigning SQL Server Database Roles.

The scripts create the two roles and grant permissions on the following Profiles tables and stored procedures.

Table name ProfileReaderRole
(Run-time users)		 ProfileWriterRole
(Business Desk users)		 
Addresses
 Select Select
Insert
Delete
Update		 
BlanketPOs
 Select Select
Insert
Delete
Update		 
ClassDef
 Select Select
Update		 
ClsAttrib
 Select Select 
ColDef
 Select Select 
CommerceServerCatalogs
 Select Select 
ES_ExprExprDeps
 Select Select
Update
Insert
Delete		 
ES_ExprInfo
 Select Select
Update
Insert
Delete		 
ES_ExprProfDeps
 Select Select
Update
Insert
Delete		 
GroupMem
 Select Select
Update
Insert		 
MemAttrib
 Select Select
Update
Insert
Delete		 
MemberDef
 Select Select
Update
Insert
Delete		 
OrganizationObject
 Select
Insert
Update		 Select
Insert
Delete
Update		 
RelDef
 Select Select 
SourceAttrib
 Select Select

Insert

Delete

Update

 
SourceDef
 Select Select

Insert

Delete

Update

 
TableDef
 Select Select
Insert
Delete
Update		 
UserObject
 Select
Insert
Update		 Select
Insert
Delete
Update

Profiles Stored Procedures

To secure the Profiles stored procedures, the scripts grant permissions to the ProfileReaderRole and ProfileWriterRole scripts as shown in the following table.

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

  • In the Catalogs database, you must grant the CatalogSet_Info table Select permissions for the Business Desk user account. Otherwise, catalog sets used by the Organization and Profile modules will fail to load.
Profiles stored procedures ProfileReaderRole
(Run-Time users)		 ProfileWriterRole
(Business Desk users)		 
sp_CheckDeleteExpr
 No Yes 
sp_CheckExprExprDeps
 No Yes 
sp_CheckExprStore
 Yes Yes 
sp_DelProfile
 No Yes 
sp_DelProfileCatalog
 No Yes 
sp_DelProfileGroup
 No Yes 
sp_DelProfileGroupHelper
 No Yes 
sp_DelProfileHelper
 No Yes 
Sp_DelProfileProperty
 No Yes 
sp_DelProfilePropHelper
 No Yes 
sp_DelDataMember
 No Yes 
sp_DelDataObject
 No Yes 
sp_DelDataSource
 No Yes 
sp_EnsureEmptyStore
 No Yes 
sp_ExprIDFromName
 Yes Yes 
sp_ExprNameFromID
 Yes Yes 
sp_GetProfileCustomProps
 No Yes 
sp_GetDataSourceDepends
 No Yes 
sp_GetDataSourceInfo
 Yes Yes 
sp_GetProfileCatalogInfo
 Yes Yes 
sp_GetProfileCatalogs
 No Yes 
sp_GetProfileDomains
 No Yes 
sp_GetProfileInfo
 Yes Yes 
sp_GetProfileMemAttrs
 No Yes 
sp_GetProfileMember
 No Yes 
sp_GetProfileProps
 Yes Yes 
sp_GetProfileSources
 No Yes 
sp_ImportExprInfo
 No Yes 
sp_UpdateExprModDate
 No Yes

See Also

Credentials for an Active Directory Profile Data Source

Copyright © 2005 Microsoft Corporation.
All rights reserved.