Securing the Transactions 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.

To help you secure the Transactions database, Commerce Server includes two security scripts: TransactionsReaderRole.sql and TransactionsWriterRole.sql. These scripts are located in the Program Files\Microsoft Commerce Server\Support folder**.**

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

  • TransactionsReaderRole. Assign run-time users to this role.
  • TransactionsWriterRole. Assign design-time users to this role.

To create and run the TransactionsReaderRole and TransactionsWriterRole

  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 Transactions 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 TransactionsReaderRole.

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

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

  7. Repeat these steps to run the TransactionsWriterRole 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 TransactionsReaderRole and TransactionsWriterRole scripts create the two roles and grant permissions on the following Transactions tables and stored procedures.

Table name TransactionsReaderRole
(Run-time users)		 TransactionsWriter
Role
(Business Desk users)		 
BasketGroup
 Select
Insert
Update
Delete		 Select
Delete		 
CatalogProducts_AuctionBid
 Select
Insert
Update
Delete		 Select
Insert
Update
Delete		 
CatalogProducts_AuctionItem
 No access Select
Insert
Update
Delete		 
Counters
 Select
Insert
Update		 No access 
OrderFormHeader
 Select
Insert
Update		 Select
Delete		 
OrderFormLineItems
 Select
Insert
Update		 Select
Delete		 
OrderGroup
 Select
Insert
Update		 Select
Delete		 
OrderGroupAddresses
 Select
Insert
Update		 Select
Delete		 
TransCategory
 No access No access 
*TransDimension
 No access No access 
UserFilter
 Select
Insert
Update
Delete		 No access

*TransDimension is used by the Transaction data import Data Transformation Services (DTS) task. For more information, see ETL Process for Transaction Data Import DTS Task.

Stored Procedure

There is only one Transactions stored procedure: OrderSummaryProc. Neither run-time users nor design-time users require access. This stored procedure is called by the OrderGroupManager.GetOrderSummary script.

Copyright © 2005 Microsoft Corporation.
All rights reserved.