You are the DBA for Acme Corporation. You’re asked to implement a new database server using Microsoft SQL Server. In any implementation, security needs to be a theme woven throughout the project, not an afterthought. Design an implementation that allows you to implement policies. The design should also include a role-based security structure. The business requirements for the database are as follows:
- No user should have delete access to any object.
- Human Resources needs to be able to fully manage employee data.
- Customer Service needs to be able to make changes to existing customers and view order data.
- Customer account managers need to be able to fully manage customer data and orders.
- Sales needs to be able to view customer data and fully manage orders.
- Marketing needs to able to pull quarterly sales numbers to analyze.
Follow these steps to produce a solution:
- Install SQL Server using the Windows Integrated security mode.
- Use a Window user for the service accounts.
- Demote built-in\administrators to security and create a DBA windows group for system administrators.
- Set up password policies based on best practices.
- Use a minimum of eight characters
- Use complex passwords
- Set account lock-out for after three tries
- Keep five passwords in history
- Expire passwords after 30 days
- Set up the roles listed in Tables 4-10 through 4-14.
Table 4-10 Human_Resources
Table 4-11 Customer_Service
Table 4-12 Account_Managers
Table 4-13 Sales
Table 4-14 Marketing