August 30, 2010 | 
Author In last two articles, we have seen what is commerce server staging and how it works. Check below links for more details.
http://microsoftblog.co.in/commerceserver/introduction-to-commerce-server-staging-2/
http://microsoftblog.co.in/commerceserver/how-commerce-server-staging-works/
In this article, we will see what are the security (access privileges ) consideration we should consider to staging service accounts. Microsoft CS team has provided some recommendations on this. I have seen in couple of implementations, the entire stating is executed on the service account which has admin privileges, which is not recommended. Here are the set of privileges a staging Service account should have.
Database
|
SQL Server Roles
|
|
MSCS_Admin |
admin_reader_role |
|
MSCS_CatalogScratch |
db_datareader, db_datawriter, db_ddladmin |
|
<site>_Marketing |
db_ddladmin, mktg_staging_role |
|
<site>_MarketingLists |
db_datareader |
|
<site>_ProductCatalog |
ctlg_CatalogWriterRole, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, Inventory_ReaderRole, Inventory_WriterRole |
Here are the set of privileges, AD groups (css administrators and css operators accounts) should have -
Database
|
SQL Server Roles
|
|
MSCS_Admin |
db_datareader |
|
MSCS_CatalogScratch |
db_datareader, db_datawriter, db_ddladmin |
|
<site>_ProductCatalog |
ctlg_CatalogReaderRole, Inventory_ReaderRole |
Note: Since staging doesn’t do any operations on <site>_transactions, there is no need of giving access to staging service account to this database.
To make developer life easier, I have created SQL scripts which provides proper access to the service accounts.
|
/* Replace ‘MSCS_Admin’ with your admin DB. Replace ‘MSCS_CatalogScratch’ with your catalog scratch DB. Replace ‘StarterSite_ProductCatalog’ with your catalog db. Replace ‘StarterSite_Profiles’ with your profile db. Replace ‘StarterSite_TransactionConfig’ with your transaction config db. Replace ‘StarterSite_Marketing’ with your marketing db. Replace ‘StarterSite_Marketing_Lists’ with your marketing config db. Replace ‘DOMAIN_NAME\stagingsvc’ with staging service account. Replace ‘DOMAIN_NAME\stagingadmin’ with staging admin account. Replace ‘DOMAIN_NAME\stagingoperator’ with staging operator account. */ PRINT ‘———– UPDATING ADMIN DB ————————–’ USE [MSCS_Admin] CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] CREATE USER [DOMAIN_NAME\stagingadmin] FOR LOGIN [DOMAIN_NAME\stagingadmin] WITH DEFAULT_SCHEMA=[dbo] CREATE USER [DOMAIN_NAME\stagingoperator] FOR LOGIN [DOMAIN_NAME\stagingoperator] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘admin_reader_role’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_datareader’ , [DOMAIN_NAME\stagingadmin] EXEC sp_addrolemember ‘db_datareader’ , [DOMAIN_NAME\stagingoperator] PRINT ‘———– UPDATING COMMERCE SCRATCH ————————–’ USE MSCS_CatalogScratch CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘db_datareader’, [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_datawriter’, [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_ddladmin’, [DOMAIN_NAME\stagingsvc] CREATE USER [DOMAIN_NAME\stagingadmin] FOR LOGIN [DOMAIN_NAME\stagingadmin] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘db_ddladmin’ , [DOMAIN_NAME\stagingadmin] EXEC sp_addrolemember ‘db_datareader’, [DOMAIN_NAME\stagingadmin] EXEC sp_addrolemember ‘db_datawriter’, [DOMAIN_NAME\stagingadmin] CREATE USER [DOMAIN_NAME\stagingoperator] FOR LOGIN [DOMAIN_NAME\stagingoperator] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘db_ddladmin’ , [DOMAIN_NAME\stagingoperator] EXEC sp_addrolemember ‘db_datareader’, [DOMAIN_NAME\stagingoperator] EXEC sp_addrolemember ‘db_datawriter’, [DOMAIN_NAME\stagingoperator] PRINT ‘———– UPDATING CATALOG DB ————————–’ USE [StarterSite_ProductCatalog] CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘ctlg_CatalogWriterRole’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_datareader’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_datawriter’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_ddladmin’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_securityadmin’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘Inventory_ReaderRole’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘Inventory_WriterRole’ , [DOMAIN_NAME\stagingsvc] CREATE USER [DOMAIN_NAME\stagingadmin] FOR LOGIN [DOMAIN_NAME\stagingadmin] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘ctlg_catalogReaderRole’ , [DOMAIN_NAME\stagingadmin] EXEC sp_addrolemember ‘Inventory_ReaderRole’ , [DOMAIN_NAME\stagingadmin] CREATE USER [DOMAIN_NAME\stagingoperator] FOR LOGIN [DOMAIN_NAME\stagingoperator] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘ctlg_catalogReaderRole’ , [DOMAIN_NAME\stagingoperator] EXEC sp_addrolemember ‘Inventory_ReaderRole’ , [DOMAIN_NAME\stagingoperator] PRINT ‘———– UPDATING MARKETING DB ————————–’ USE [StarterSite_Marketing] CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘db_ddladmin’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘mktg_staging_role’ , [DOMAIN_NAME\stagingsvc] PRINT ‘———– UPDATING MARKETING CONFIG DB ————————–’ USE [StarterSite_Marketing_Lists] CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘db_datareader’ , [DOMAIN_NAME\stagingsvc] PRINT ‘———– UPDATING PROFILES DB ————————–’ USE [StarterSite_Profiles] CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘Profile_Schema_Manager’ , [DOMAIN_NAME\stagingsvc] PRINT ‘———– UPDATING ORDER CONFIG DB ————————–’ USE [StarterSite_TransactionConfig] CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘Orders_Management’ , [DOMAIN_NAME\stagingsvc] |
If you like this post, please click on our sponsor advertisement.
Posted in 
Tags: 
