Introduction to SQL Server Security — Part 4

The series so far:

1. Introduction to SQL Server Security — Part 1
2. Introduction to SQL Server Security — Part 2
3. Introduction to SQL Server Security — Part 3
4. Introduction to SQL Server Security — Part 4

In the previous articles in this series, I introduced you to SQL Server security principals and the part they play in authentication and authorization. At their most basic, principals are server and database entities that can request access to SQL Server resources. The most common principals are server logins, server roles, database users, and database roles.

SQL Server provides a number of built-in principals that are added automatically when you install a SQL Server instance or create a database. In some cases, it’s not always clear when and how to use these principals. For example, SQL Server automatically adds the public server and database roles. Unlike other fixed roles, you can modify their permissions. However, this can impact common database operations, so you must proceed cautiously if changing the default configuration.

In this article, I dig into some of the more confusing built-in principals to help you better understand how they fit into the larger authentication picture. I created the examples for this article on an instance of SQL Server 2017, but much of the information applies to SQL Server editions going back to 2014 or earlier. Regardless of which edition you’re using, you should understand how these built-in principals work before enabling them or modifying them in your production environments.

The sa Login

The sa login, short for system administrator, is one of the riskiest server-level principals in SQL Server. It’s automatically added as a member of the sysadmin fixed server role and, as such, has all permissions on that instance and can perform any activity. If the login were hacked, the attacker could do unlimited damage.

You cannot drop the sa login, but you can disable it. If you select Windows Authentication when installing SQL Server, the database engine assigns a random password to the account and automatically disables it. If you then switch to SQL Server Authentication, the login remains disabled, and you must manually enable it.

If you select SQL Server Authentication during installation, the account will be enabled, but you must provide a password, so make sure it’s a strong one. Even if the login is enabled, you should avoid using it for your applications. In fact, unless a connecting system absolutely requires the sa login, it’s best that the account remains disabled.

You can verify whether the sa login is disabled by querying the sys.server_principals system view, using a SELECT statement similar to the following:

USE master;
GO
SELECT principal_id, type_desc, is_disabled
FROM sys.server_principals
WHERE name = 'sa';

If the login is disabled, the is_disabled value will be 1, as shown in Figure 1.

Figure 1. The sa login on a SQL Server instance

You cannot remove the sa login from the sysadmin server role, but you can verify its membership:

SELECT member.name
FROM sys.server_role_members rm
JOIN sys.server_principals role  
  ON rm.role_principal_id = role.principal_id  
JOIN sys.server_principals member  
  ON rm.member_principal_id = member.principal_id
WHERE role.name = 'sysadmin';

The statement uses the sys.server_role system view and sys.server_principals system view to return the members of the sysadmin role, as shown in Figure 2.

Figure 2. Principals assigned to the sysadmin server role

If you find yourself in a situation that requires the sa login, you must enable the account before it can be used. To do so, run an ALTER LOGIN statement that sets the ENABLE property and then run the statement again to assign a password to the login, as shown in the following example:

USE master;
GO
ALTER LOGIN sa ENABLE;  
GO  
ALTER LOGIN sa WITH PASSWORD = 'tempPW@56789';
GO

Of course, if you were to enable the login, you would need to assign a much stronger password than the one shown here. Moreover, if you’re just trying all this out, be sure to perform your modifications in a non-production environment.

You can test the sa login in SQL Server Management Studio (SSMS) by launching a new query and changing the connection. The following steps describe how to change that connection:

1. In SSMS, launch a new query.
2. On the new query tab, right-click a blank area in the query editor, point to Connection and click Change Connection.
3. In the Connect to Database Engine dialog box, select SQL Server Connection from the Authentication drop-down list. The form will be updated to include login and password text boxes.
4. Type sa for the login and the password you provided when you enabled the login, and then click Connect.

After you’ve logged in as sa, try running a query such as the following:

SELECT * FROM sys.server_principals;

The query should run with no problem because the sa login is permitted to do anything on the server. On my test system, the SELECT statement returns 32 rows.

After you’ve verified the login, run the following ALTER LOGIN statement to disable it:

ALTER LOGIN sa DISABLE; 
GO

Next, launch a new query and again try connecting with the sa login, following the same steps outlined above. This time, you should receive an error similar to the one shown in Figure 3.

Figure 3. The error generated when trying to connect with the disabled sa login

Whenever possible, you should ensure that the sa login remains disabled. Also, consider renaming the login to provide another line of defense. Because the login is so well known, it’s a frequent target for cybercriminals who have lots of tools at their disposal for cracking passwords. Once they’ve gotten into your databases, you might not be able to stop them until it’s too late.

Certificate-Based Logins

When poking around SQL Server, you’re likely to notice a set of server logins whose names start and end with double hash marks, as in ##MS_PolicySigningCertificate##. The logins are certificate-mapped accounts used by the database engine for internal purposes. You should not delete these or mess with them in any way.

To retrieve a list of certificate-based logins, run the following query:

SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE name LIKE '##%'
ORDER BY name;

Figure 4 shows the results that the statement returns on my system.

Figure 4. Certificate-based logins on a SQL Server instance

Notice that two of the logins are marked as disabled and their type listed as SQL_LOGIN, rather than CERTIFICATE_MAPPED_LOGIN. Despite these differences, Microsoft documentation specifically states that these two are also considered to be certificate-mapped logins.

You can view the certificates associated with the enabled certificate-based logins by running the following SELECT statement:

SELECT name FROM sys.certificates
WHERE name LIKE '##%'
ORDER BY name;

On my system, the statement returns the results shown in Figure 5, confirming that there’s a certificate for each enabled login.

Figure 5. Certificates associated with certificate-mapped logins

For the most part, you don’t have to worry about the logins, unless you’re involved in an audit or performing a security scan, in which case you might need to explain to the powers-that-be why these logins are showing up in your reports.

The public Server and Database Roles

Each SQL Server instance contains the public fixed server role, and each database (including system databases) contains the public fixed database role. All logins belong to the public server role, and all database users belong to the public database role. You cannot drop either role, and you cannot add members to or remove members from either role.

The database engine assigns a set of permissions to the roles by default. Logins inherit all permissions granted to the public server role unless a login has been specifically granted or denied those permissions. The same goes for the public database role. Users inherit all permissions unless they’ve been specifically granted or denied permissions.

To view the permissions assigned to the public server role, run the following SELECT statement:

SELECT pm.state_desc, 
  pm.permission_name,
  pm.class_desc,
  pm.major_id,
  ep.name
FROM sys.server_permissions pm
JOIN sys.server_principals pr 
  ON pm.grantee_principal_id = pr.principal_id
LEFT JOIN sys.endpoints ep
  ON pm.major_id = ep.endpoint_id
WHERE pr.name = 'public';

The statement joins the sys.server_permissions, sys.server_principals, and sys.endpoints system views to retrieve the relevant information. On my system, I retained the default permissions, which are shown in Figure 6

Figure 6. Default permissions assigned to the public server role

You can take a similar approach to retrieve the permissions assigned to the public database role:

USE ImportSales1;
GO
SELECT pm.state_desc, 
  pm.permission_name, 
  pm.class_desc,
  pm.major_id,
  OBJECT_NAME(pm.major_id) obj_name
FROM sys.database_permissions pm
JOIN sys.database_principals pr 
  ON pm.grantee_principal_id = pr.principal_id
WHERE pr.name = 'public';

In this case, the public database role is specific to the ImportSales1 database, which was created as part of the examples in the previous article in this series. The database is a simple, contained database that includes only the Sales schema and the Customers table. You can refer back to that article for details about the database, or you can run this statement against another database.

Figure 7 shows part of the results returned by the SELECT statement on my system. As with the public server role, I made no modifications to the public database role. By default, the role has been granted 176 permissions, the majority of which are the SELECT permission granted to system views.

Figure 7. Partial view of the default permissions assigned to the public database role

If you were to run the same query against the master database, it would return a much larger result set (2,254 rows on my system). In this case, the results would also include the EXECUTE permission, granted on system functions and stored procedures.

The public roles are different from other fixed server and database roles because you can grant, deny, and revoke permissions. Even so, you should avoid modifying the public roles and instead create one or more additional roles. However, if you work someplace that insists upon using the public roles, you should grant permissions to the roles only on securables that you want to make available to all users. In addition, you should avoid denying permissions because they can override the permissions granted to individual users or logins.

You can also revoke permissions on the public roles, but be careful when doing so. SQL Server assigns a number of permissions to these roles by default (as you saw in the preceding examples), and many of those permissions are used for routine database operations. Revoking permissions on these roles can impact all logins or users.

The dbo Database User and Schema

Every database contains a dbo user and dbo schema. Although the two are related, they serve very different purposes. (The term dbo stands for database owner.)

The dbo user is added as a member of the db_owner fixed database role. By default, the user is granted all permissions in a database and can perform all activities within the scope of that database. You cannot limit the dbo user or drop the user from the database.

SQL Server automatically maps the sa login, database owner, and members of the sysadmin server role to the dbo user account in each database. To verify this, connect to a SQL Server instance as one of these users and query the CURRENT_USER system function, as in the following example:

SELECT CURRENT_USER;

The SELECT statement should return dbo as the current user.

The dbo user also owns the dbo schema, which is the default schema for all newly created databases and users, unless a different schema is specified. As with the dbo user, you cannot drop the dbo schema.

To verify the login name and default database associated with the dbo user, run the following query against one of your databases:

USE ImportSales1;
GO
SELECT SUSER_SNAME(sid) login_name, default_schema_name
FROM sys.database_principals
WHERE name = 'dbo';

In this case, the query is specific to the ImportSales1 database created for the previous article, but you can use any database. It should return the login for your current connection, as well as the dbo schema.

You can also verify that the dbo user has been added to the db_owner database role:

SELECT member.name
FROM sys.database_role_members rm
JOIN sys.database_principals role  
  ON rm.role_principal_id = role.principal_id  
JOIN sys.database_principals member  
  ON rm.member_principal_id = member.principal_id
WHERE role.name = 'db_owner';

The statement should return dbo, along with any other role members. When I ran the query on my system, I was still working within the context of the ImportSales1 database, so the query returned only dbo, which is tied to my login.

However, look what happens with the WideWorldImporters database, which I attached to my SQL Server instance from the backup file I downloaded from GitHub:

USE WideWorldImporters;
GO
SELECT SUSER_SNAME(sid) login_name, default_schema_name
FROM sys.database_principals
WHERE name = 'dbo';

On my system, the results indicate that sa is the associated login, along with dbo as the default schema, as shown in Figure 8.

Figure 8. Login associated with the dbo user

In addition, when I retrieve the members of the db_owner database role, the results include both the dbo user and my login account:

SELECT member.name
FROM sys.database_role_members rm
JOIN sys.database_principals role  
  ON rm.role_principal_id = role.principal_id  
JOIN sys.database_principals member  
  ON rm.member_principal_id = member.principal_id
WHERE role.name = 'db_owner';

Because the dbo user is associated with the sa account, my login is added as a separate member to the db_owner database role. In this way, both my login and the sa login have full control over the database (assuming the sa login is enabled).

Be aware, however, you might not see the same results on your system as I see mine. It’s possible that only the dbo user will be added to the role. It will depend on how you’ve configured your system and added the WideWorldImporters database.

On my system, my login is considered the owner for both the ImportSales1 database and WideWorldImporters database, even though my login is associated only with the dbo user in the ImportSales1 database. To confirm the database owners, I ran the following query:

SELECT name, SUSER_SNAME(owner_sid) 
FROM sys.databases
WHERE name = 'ImportSales1' 
  OR name = 'WideWorldImporters';

The query returned my login for both databases, as shown in Figure 9.

Figure 9. Login associated with WideWorldImporters and ImportSales1 databases

You should also be aware of the query engine’s behavior when it comes to the dbo schema. When you query a database object without specifying a schema, the query engine first looks in your default schema if it’s other than dbo. If the object is not there, the query engine looks in the dbo schema. If the object is not in that schema, an error is returned.

When you create a user, you can specify a default schema. If you do not, the dbo schema is assumed. Users with dbo as their default schema do not inherit the permissions granted to the dbo user.

In addition, if you specify a default schema other than dbo and the user is a member of the sysadmin server role, the specified schema is ignored and the dbo schema is used. The default schema for all members of sysadmin is dbo.

The guest Database User and Schema

As with dbo, every database contains a guest user and a guest schema. You can use the guest user to grant database access to logins that are not associated with user accounts in that database, a strategy that should not be implemented lightly.

Although the guest user cannot be dropped, it is disabled by default and assigned no permissions. Microsoft recommends that you keep it that way. If enabled, logins that should not be able to get into a database will have access. Do not enable the account unless you have a compelling reason to so.

The guest user owns the guest schema. Like the user, the schema cannot be dropped. However, it contains no objects and has been granted no permissions. In fact, the guest schema is seldom used, if at all.

To verify whether the guest user is enabled, run the following query:

USE ImportSales1;
GO
SELECT u.hasdbaccess, p.default_schema_name
FROM sys.database_principals p
JOIN sys.sysusers u
  ON p.principal_id = u.uid
WHERE p.name = 'guest';

The query should return a hasdbaccess value of 0, indicating that the user account is disabled, as shown in Figure 10. The query should also return guest as the default schema.

Figure 10. Enabled status of the guest user account

If against all advice you decide to enable the guest account, you must grant the CONNECT permission to the user, as shown in the following example:

GRANT CONNECT TO guest;
GO

Granting the CONNECT permission is all it takes to enable the guest user. If you rerun the previous SELECT statement, the hasdbaccess value should now be 1.

At any time (the sooner, the better), you can disable the guest account by revoking the CONNECT permission:

REVOKE CONNECT FROM guest;
GO

To verify whether the guest schema contains any objects, run the following query, which will normally return zero rows:

SELECT o.name
FROM sys.all_objects o
JOIN sys.schemas s
  ON o.schema_id = s.schema_id
WHERE s.name = 'guest';

If you were to enable the guest user, you might want to add objects to the guest schema specifically for that user to access, but chances are, you won’t be touching either one.

The sys Database User and Schema

Every database includes the sys user and sys schema. The sys user owns the sys schema. The user serves no other purpose. It’s associated with no logins and is disabled by default. In all likelihood, you’ll never need to interact with this user account.

The sys schema is another story. The database engine requires the schema for internal use. You cannot modify or drop the schema. It contains a number of important system objects, such as system tables, catalog views, dynamic management views, and built-in functions. You’ve already seen several of the catalog views in action in the previous examples. The sys schema is particularly handy for accessing SQL Server metadata.

You can view the objects in the sys schema by running the following query:

USE ImportSales1;
GO
SELECT o.type_desc, o.name
FROM sys.all_objects o
JOIN sys.schemas s
  ON o.schema_id = s.schema_id
WHERE s.name = 'sys'
ORDER BY o.type_desc, o.name;

In this case, the SELECT statement is specific to the ImportSales1 database. On my system, the statement returns 2,273 rows, broken into 12 object types. To view a list of the object types, run the following query:

SELECT distinct o.type_desc
FROM sys.all_objects o
JOIN sys.schemas s
  ON o.schema_id = s.schema_id
WHERE s.name = 'sys'
ORDER BY o.type_desc

Figure 11 shows the results I received when I ran the SELECT statement.

Figure 11. The types of objects in the sys schema

You can also retrieve a list of objects based on type, as shown in the following example:

SELECT o.name
FROM sys.all_objects o
JOIN sys.schemas s
  ON o.schema_id = s.schema_id
WHERE s.name = 'sys' 
  AND o.type_desc = 'VIEW'
ORDER BY o.name;

The statement returns only the objects of type VIEW. Figure 12 shows part of the results I received. There were 473 rows in all.

Figure 12. A partial list of the views in the sys schema

For more information about catalog views and dynamic management views, check out my Simple Talk article SQL Server System Views: The Basics.

The INFORMATION_SCHEMA Database User and Schema

Like sys, every database also includes the INFORMATION_SCHEMA user and INFORMATION_SCHEMA schema. Again, the user is there only to support the schema. You cannot drop the user, but it is disabled by default.

Unlike sys, the INFORMATION_SCHEMA schema contains only a small number of views and no other object types. You can confirm this by running the following SELECT statement:

SELECT o.name
FROM sys.all_objects o
JOIN sys.schemas s
  ON o.schema_id = s.schema_id
WHERE s.name = 'INFORMATION_SCHEMA'
ORDER BY o.name;

Figure 13 shows part of the results that I received. In all, there are 21 INFORMATION_SCHEMA views for the ImportSales1 database.

Figure 13. A partial list of the views in the INFORMATION_SCHEMA schema

For more information about INFORMATION_SCHEMA views, refer to the same Simple Talk article, SQL Server System Views: The Basics.

SQL Server’s Odd Collection of Predefined Principals

When working with SQL Server and its databases, it’s important that you understand how the built-in principals work, especially the ones I’ve covered here. For the most part, SQL Server attempts to configure these principals in a way that best protects your data, such as disabling the sa server login or guest database user, but this doesn’t prevent you from taking steps that can disrupt operations or, worse still, open your data up to security risks. The better you understand how to work with the server and database principals, the better you can protect your SQL Server instance and its databases.

The post Introduction to SQL Server Security — Part 4 appeared first on Simple Talk.

from Simple Talk https://ift.tt/2YnZDmd
via