Make DBA policies work for you. They can really help.

Eric Dycus March 11, 2022

 


When I first took on my role as Supervisor for my team I implemented policies for our team. As first every was saying, "Waste of time". We know what we want our policies to be but we will never be able to enforce them because our user's needs are all over the place. 

Policies may seem like it is just an excursive in academics but if you invest the time in it, it has huge payoffs. Let me give you a story to example how it helps.

Bob is a very good DBA. he has been around for years and knows a little bit of everything. Bob was working with an application owner about his database and they said my account needs sysadmin permissions on the server because of the way it works. Bob pushes back on the request but in the end, he gives up and provides the customer with the access they need. If there was a policy that read "sysadmin permissions are only to be used if they can provide documentation on why it is needed. Now Bob looks at the policy says, Our policy says I can give your application sysadmin permissions if you have documentation on why that level of permissions is required. Most of the time the user will back down from their request saying, Oh, can you give me the Owner and the ability to manage the SQL jobs. Which is much more reasonable. Without the policy, Bob had no way to defend what he knew was right.  

Since we created policies the following improvement has taken place.

  1. Backups are standardized
  2. Configurations are standardized
  3. Security has been changed to least privileged
  4. User's accounts are no longer assigned to the database. permission are assigned by group names

Feel free to start with these policies and adjust them to you environment.  

  • 0.1           Security
  • 0.1.1              DM AD Group/User/Service Access and Permissions
  • 0.1.1.2           DM NERC SQL Server Auditing
  • 0.1.2              Encryption
  • 0.1.2.1                DM Encryption Connection
  • 0.1.2.2                DM Data Protection
  • 0.1.3             DM SQL Auditing and Logging
  • 0.2          Administration
  • 0.2.1            SQL Server Design and Configuration
  • 0.2.1.1                DM Guest Level Configurations
  • 0.2.1.2                DM SQL Server Version Standard
  • 0.2.1.3                DM Database Installation and Configuration
  • 0.2.1.4                DM Database Environments and Usage
  • 0.2.1.5                DM Email Notification
  • 0.2.2            DM Backups and Backup Retention
  • 0.2.3            DM SQL Vulnerabilities
  • 0.2.4            DM Disaster Recovery
  • 0.2.5            DM NERC Testing
  • 0.2.6            DM SQL Server Patching and Cumulative Updates
  • 0.2.7            DM Monitoring and maintenance


DM AD Group/User/Service Access and Permissions

Purpose:

Implement and maintain database access.

Roles and Responsibilities:

Service Desk - Creates users and groups. 

Data Management Team – Uses users/service/groups to allow the least privilege to the database

Implementation and Usage Standards:

  • Data Management may request Active Directory admin/service/groups to be created to handle authentication for SQL resources.

  • Permissions are granted with least access allowable as defined by business need.

  • Groups will be used to reduce administration efforts when possible. 

  • Normal user account requesting sysadmin permissions will be given an admin account for that permission.

  • Active Directory accounts will be used unless SQL Authentication is required.

  • Any special permissions such as roles needed for the database will have an suffix of RE-<<ROLENAME>>

Reference Requirements:

 

Effective date: 

8/6/2019

Date last reviewed:

 

Information contact:

Data Management <<email address>>

Procedure link:






 DM SQL Auditing and Logging - Need Procedure

Purpose:

Implement and maintain auditing and logging for proactive monitoring.

Roles and Responsibilities:

Data Management - Responsible for configuring alerts and thresholds on 3rd party tool(s) and auditing of the SQL servers. Delivery of daily reports via email

Implementation and Usage Standards:

  • All SQL Servers will be configured to audit failed login and restarts in the SQL logs daily.

  • The DM Dashboard will reviewed daily and a group email will be used corrections.

Reference Requirements:

 

Effective date: 

8/6/2019

Date last reviewed:

 

Information contact:

Data Management  <<email address>>

Procedure link:

 





DM Encryption Connection

Purpose:

Encrypt confidential SQL Server data in transit using Transport Layer Security (TLS) encryption..

Roles and Responsibilities:

Infrastructure Team and the Data Management Team - Implementation and administration of TLS encryption for SQL Server. Responsible for enforcement of this standard and make exceptions.

 

Data Management Team - Supporting clients and aid the Service Desk in troubleshooting.

 

Service Desk team - Responsible for loading TLS 1.2 complaint drivers on the workstations. 

Implementation and Usage Standards:

  • At least TLS 1.2 will be used to encrypt connections on SQL servers except when the application doesn't support it.

Reference Requirements:

 

Effective date: 

8/6/2019

Date last reviewed:

 

Information contact:

Data Management  <<email>>

Procedure link:

 







DM Data Protection

Purpose:

Protecting confidential data.

Roles and Responsibilities:

Data Management – Performing yearly audits to ensure that old accounts are cleaned up and sysadmin permissions are using least privileges. 

 

Implementation and Usage Standards:

  • Privileged accounts with elevated access will be audited annually.

Implementation and Usage standards

  • Audit Individual User Accounts

  • Individual User Accounts will be checked once every quarter, as a part of Audit process.  

     

    In CORP and NERC SQL Servers, all the accounts will be audited to check if there are any Individual accounts. If there are individual accounts found, they need to be moved to a group account

                 

    • Audit SysAdmin accounts:

     

    All type of accounts with SysAdmin access will be checked once every quarter, as a part of Audit process.

     

    Group Accounts with SysAdmin access needs to be checked with business users so to reduce the permissions to the least privileges allowable as defined by business need.

    If an account needs to have Admin account, a business justification is gathered.

    • Auditing reports and Justifications will be provided to the compliance department yearly.

     

Reference Requirements:

 

Effective date: 

8/6/2019

Date last reviewed:

 

Information contact:

Data Management  <<email>>

Procedure link:

 



DM SQL Server Version and Upgrades

Purpose:

Determine when the SQL versions need to be upgraded and what versions to support. 

Roles and Responsibilities:

Data Management - Responsible for ?

Implementation and Usage Standards:

  • SQL servers versions that are not on mainstream support will be upgraded

  • Use SQL standard Edition for new installs unless otherwise features or owners dictate otherwise

Reference Requirements:

 

Effective date: 

8/6/2019

Date last reviewed:

 

Information contact:

Data Management  <<email>>

Procedure link:

 

 





DM Backups and Backup Retention

ose:

Implement and maintain database backup and retention of SQL Servers managed by EDFR IT department.

Roles and Responsibilities:

Infrastructure team - Responsible for providing security for the backup storage SQL backups. 

 Data Management team  - Responsible for implementation and administration of SQL Server backup and retention

Implementation and Usage Standards:

  • Standard backups will have a full backup every 24 hours and will be able to recover with no more than 24 hours data loss.

  • Standard Backup Retention:

    • Daily Backup: <<Your BACKUP>> days unless otherwise specified

    •  

 

Reference Requirements:

 

Effective date: 

8/6/2019

Date last reviewed:

 

Information contact:

Data Management  <<email>>

Procedure link:

 




DM Monitoring and Maintenance Policy

Purpose:

Monitoring and Maintenance Policy

Roles and Responsibilities:

Data Management – Daily Monitoring and Maintenance of SQL servers.

Infrastructure team - Responsible for all 3rd party SQL server monitoring tool(s) or dashboards.

Implementation and Usage Standards:

Data Management, will monitor and maintain SQL servers / databases providing preventive/protective measures.

  • Monitoring

  • SQL Servers ( CPU, Memory, Drive )

  • Jobs / backups ( DBA Care )

  • Performance ( Blocking, Solarwinds )

  • Drives space analysis

  • Server properties ( SQL Services, Notifications )

  • 3rd party tools (Solar winds)

    • SQL Health Overview

  • Maintenance

  • Maintain database integrity

  • Verify Restores ( Yearly )

  • Index rebuild/reorg

  • Update stats

Reference Requirements:

 

Reason:

 

Effective date: 

9/13/2019

Date last reviewed:

 

Information contact:

Data Management  <<email>>

Procedure link:

 


Post a Comment

0 Comments