This blog will focus on the approach to be taken to audit Access Control in a Microsoft SQL Server environment.
1. Control: Processes should exist to ensure that access granted to both general and privileged users is appropriate and is removed when no longer needed.
1.1 Review processes for granting, updating, and terminating user access.
1.2 Obtain a list or file containing information on current database users and administrators, and their roles.
1.3 Obtain a list or file containing information on current local Windows Server groups and the members of each group.
1.4 If privileges to access objects or statements are assigned directly to users –
- Obtain a list or file containing information on users with individual privileges and the related objects to which they have access.
- Evaluate the access based upon job descriptions and the access granted.
- Examine related access requests for reasonableness and proper authorization.
- Discuss possible excessive access rights with the DBAs, Information Security and the Business Owners as dictated by the circumstances.
1.5 Review privileges assigned to users and roles. Discuss with the DBA and data owner any privileges directly assigned to users rather than to roles.
1.6 Select a sample of user access requests and verify that access is approved by the appropriate data owners.
1.7 Review the roles and privileges assigned to a sample of users. Ensure that the users’ access is commensurate with their job responsibilities.
1.8 Obtain a list or file of terminated employees from HR. Compare the terminated employee list or file to the list or a table of database users to ensure that accounts are terminated in a timely manner.
1.9 Review the assignment of fixed server roles (sysadmin, serveradmin, etc.) to provide reasonable assurance that these roles are used only in support of DBA activity.
2.0 Review any roles and/or user accounts that are assigned CREATE, ALTER or DROP privileges. Discuss the business requirements for these types of highly privileged access with the DBA and other affected manager(s).
2.1 Review accounts that are assigned highly-privileged roles such as sysadmin. Discuss the requirement for this type of access with the DBA and Information Security Officer. Assess each of these accounts for possible segregation of duties issues.
2.2 Verify that the GUEST user is removed from or disabled in all databases. Discuss any exceptions with the DBA.
2.3 If the public or GUEST USERIDs remain in the database and are enabled, verify that these ids are not granted access to any objects.
2.4 Verify that the database owner (dbo) owns all user-created database schemas.
2.5 Review the security over access to OS executables (cmd.exe, explorer.exe, etc) on the SQL Server and the SQL Server install directories. Ensure that users’ unique or group permissions do not grant full control.
2.6 Review assignment of the GRANT object permission to verify that it is not assigned to users or roles. Discuss any of these existing privileges with the DBA.
The above list is to just serve as a reference and exceptions can be made based on need.
Reference : This list is adapted from ISACA’s SQL Server Assurance book.