In the previous blog in this series, we discussed the principle of least privilege, and the importance of assigning bare minimum privileges to users and systems at database or server levels. However, there are certain built-in principals in your database that possess all permissions in SQL Server. If an attacker managed to get hold of one of these principals, the database could be easily exploited and damaged.
In this blog, we’ll talk about a few of these principals that are automatically created when you install and configure any SQL Server instance, and why configuring them cautiously is essential to secure your SQL environment.
The system administrator login
The system administrator (sa) login role is a server-level principal present under the sysadmin server role. This role has all the permissions to the SQL instance, and if this login is compromised, an attacker can gain complete control of the database. Malicious insiders can target this role to accomplish privilege escalations, unauthorized database access, data exfiltration, and other harmful activities.
The sa login role can’t be deleted altogether; however, it can be disabled. Disabling this role is a best practice to secure your SQL instance.
The public database and server roles
Each SQL Server instance contains a public fixed server role, and each database contains a public fixed-database role. All logins of the SQL Server are part of a public fixed server role, and all users are part of a public fixed-database role. Neither of these roles can be removed, nor can members be added or removed from them. They are assigned a set of permissions by default that give them the privilege to access sensitive and critical information in the SQL environment, which can threaten the security.
If certain users or systems need to be denied or given certain permissions, you’ll have to configure the permissions separately to override the existing permissions. In an organization, every user or role should be closely monitored to ensure they have only the permissions necessary to perform their duties.
The database owner user and schema
A database owner (dbo) user is a member of the db_owner fixed-database role. This dbo user will have complete access within that database, and all permissions are assigned by default. Additionally, the dbo user role can’t be dropped from the fixed roles. The dbo user role owns a dbo schema, which is the default schema for all database users and roles, unless specified.
One loophole here is that the sa login and sysadmin server roles will be mapped to the dbo user role, and all of these roles will have unrestricted access to the database. For this reason, it’s recommended to disable the dbo user role as well.
How can you monitor built-in principals effectively?
The SQL Server built-in roles can be very tricky to understand, but it’s important to keep track of their activities to ensure nothing malicious occurs through them. Even after configuring these roles, there exists the possibility of these principals being compromised. For instance, when a disabled principal gets enabled again, you need to be notified of it immediately to prevent a potential threat. To learn more critical use cases, you can refer to our e-book on the Basics of SQL Server log auditing.
While the built-in SQL Server auditing tools log these events for forensic and investigation purposes after a breach has occurred, you can prevent a future attack or breach by getting real-time alerts for any deviant activity in your SQL environment using a security information and event management (SIEM) solution.
A SIEM solution can be configured to raise specific alerts. In this case, you can set up a correlation rule to identify if a disabled user becomes enabled after multiple attempts, followed by privilege escalation to any user role. Workflows can be set up based on alerts, so basic response actions are automated.
Want to see how a SIEM solution works in action? Get your free demo of Log360, a comprehensive SIEM solution, and learn how to mitigate threats and attacks easily.
The series so far…
In this blog series, we discussed:
-
How to configure authentication and authorization mechanisms effectively.
-
The importance of adopting the principle of least privilege in your enterprise.
-
Understanding built-in roles and closely monitoring user activities.
-
Configuring powerful alerts for anomalous activities using a SIEM solution to stay a step ahead of attackers.
