This article is written by our contributing author Ken Lassesen. His bio can be found here.
I am a standards-based person and prefer to adopt existing best practices. When I work in the roles of Product Manager or Architect on an ISV product, I will ask the question: “If there is a security breach and data is lost, would it have a more severe impact on the firm than the loss of corporate credit cards?” If the answer is yes, then I hand out Payment Card Industry (PCI) Data Security Standard Requirements and Security Assessment Procedures Version 2.0 (PCIDSS) and we proceed to identify what does not apply. What remains becomes part of the best practices recommendations for the product.
Many ISV products require SQL Server standard security or mixed security. These products are the primary focus of this set of posts. SQL Server logins are not rich in features. PCIDSS requirements are organized into four groups which I will address in subsequent posts:
- Login Support
- Limit repeated access attempts by locking out the user ID after not more than six attempts.
- Set the lockout duration to a minimum of 30 minutes or until administrator enables the user ID.
- Remove or disable inactive user accounts at least every 90 days.
- Password Constraints
- Change user passwords at least every 90 days.
- Require a minimum password length of at least seven characters.
- Use passwords containing both numeric and alphabetic characters.
- Do not allow an individual to submit a new password that is the same as any of the last four passwords he or she has used
- The ability to set password complexity.
- Temporal Constraints
- The ability to remove or disable inactive user accounts at least every 90 days.
- The ability to enable accounts used by vendors for remote access only during the time period needed.
- The ability to grant logins a definite access period – typically for ISV consultants or other consultants.
- The ability to restrict logins to working hours.
- Expired Sessions
- If a session has been idle for more than 15 minutes, require the user to re-authenticate to re-activate the session
In the following posts, I will show how Data Definition Language (DDL) triggers can enhance SQL Server to implement the above.