Welcome to the final post in our five-part series about the common questions we answer for our clients. Security is often at the top of people’s minds—or, it should be—yet there is a weakness common to many systems. Not only does this oversight put their system at risk, it also impacts GDPR compliance.
Question #5: Is Our Data/Database Secure?
Once we have established that a system is performing well and can deal with spikes of traffic beyond normal levels, we begin to investigate a little deeper into the architecture and setup of the database(s) and server(s).
In many cases, we are asked to give our opinion on the security setup of the system, along with remediation recommendations for anything that we find problematic.
A regular finding is that users are assigned far more permissions in the database than necessary. This stems from a lack of knowledge on the administration side and a desire for fast resolution of a permission issue.
The scenario that typically result in a user having too many permissions is:
- Company A purchases a 3rd party line of business application that runs on SQL Server
- The software is installed and states that it needs “sysadmin” permissions on the SQL Server so that the application can run.
- The DBA/IT Admin pushes back, because “sysadmin” permissions allow full access to the entire SQL Server (delete databases, remove users, stop the server etc.)
- The application supplier cannot provide a list of required permissions and demands “sysadmin”
- The IT department concedes the point and provides “sysadmin” permissions
This leaves a user with sysadmin permissions on a production database server that can do anything they like, with no way of knowing what commands are being issued. In most cases, the application will not do anything nefarious, but if the application is designed in the wrong way, it may allow any user of the application to send commands to the SQL Server that are then processed with this highly privileged user. This situation is the basis of many famous hacks of databases that caused personally identifiable data to be leaked onto the internet.
Keeping Permissions on a Need-to-Do Basis
In IT security, the simplest and best practice is the principle of least privilege (PoLP). An account should only receive the permissions required to fulfill their role and nothing more. In the scenario illustrated, the 3rd party application likely needed short-term permissions to create its own database and from then on only permissions inside its own database. The sysadmin role has full privileges over the entire system (something only a DBA would need) and therefore the scenario breaks PoLP.
We regularly see systems where a company has assigned sysadmin privileges to all employees, because the 3rd party application deemed it necessary for the application to function. This is usually unnecessary and poses a high risk to the company in terms of data security and privacy. It goes without saying that this is equally unacceptable for a system which must follow GDPR rules.
Beyond application security over-reach, we also see generous use of single user permission assignments. The issue here is not necessarily the permissions being assigned, but the fact that it is assigned to a single user. This makes permission control far too granular and difficult to administer. A much easier approach is to use the power of the Active Directory and create permission groups and assigning the individual users to these groups. This allows for a centralized management of users and permissions. If an employee changes department, joins the company or leaves the company, their permission sets can be rapidly changed and deployed centrally from changes made in the Active Directory instead of on each database. Establishing these groups also allows short-term delegation of permissions in the case of sickness or vacation, by assigning a user to a security group temporarily.
In short: Most users are assigned far too many permissions on SQL Servers and these are generally managed on a per user basis. Changing the security privileges to follow the PoLP and assigning those permissions to groups (with members) will clean up many of your security woes.
Have More Questions?
Not sure if your system’s current SQL server or windows versions can handle your operations? Book a call or send us an email—I’m always happy to offer guidance.
