Walkthrough for the security features of SQL Server on Linux
Applies to: SQL Server - Linux
If you're a Linux user who is new to SQL Server, the following tasks walk you through some of the security tasks. These aren't unique or specific to Linux, but it helps to give you an idea of areas to investigate further. In each example, a link is provided to the in-depth documentation for that area.
This article requires the
AdventureWorks2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.
Create a login and a database user
Grant others access to SQL Server by creating a login in the
master database using the CREATE LOGIN statement. For example:
CREATE LOGIN Larry WITH PASSWORD = '************';
Always use a strong password in place of the asterisks in the previous command.
Logins can connect to SQL Server and have access (with limited permissions) to the
master database. To connect to a user-database, a login needs a corresponding identity at the database level, called a database user. Users are specific to each database and must be separately created in each database to grant them access. The following example moves you into the
AdventureWorks2022 database, and then uses the CREATE USER statement to create a user named Larry that is associated with the login named
Larry. Though the login and the user are related (mapped to each other), they are different objects. The login is a server-level principal. The user is a database-level principal.
USE AdventureWorks2022; GO CREATE USER Larry; GO
- A SQL Server administrator account can connect to any database and can create more logins and users in any database.
- When someone creates a database they become the database owner, which can connect to that database. Database owners can create more users.
Later you can authorize other logins to create more logins by granting them the
ALTER ANY LOGIN permission. Inside a database, you can authorize other users to create more users by granting them the
ALTER ANY USER permission. For example:
GRANT ALTER ANY LOGIN TO Larry; GO USE AdventureWorks2022; GO GRANT ALTER ANY USER TO Jerry; GO
Now the login Larry can create more logins, and the user
Jerry can create more users.
Grant access with least privileges
The first people to connect to a user-database is the administrator and database owner accounts. However these users have all the permissions available on the database. This is more permission than most users should have.
When you're just getting started, you can assign some general categories of permissions by using the built-in fixed database roles. For example, the db_datareader fixed database role can read all tables in the database, but make no changes. Grant membership in a fixed database role by using the ALTER ROLE statement. The following example add the user
Jerry to the db_datareader fixed database role.
USE AdventureWorks2022; GO ALTER ROLE db_datareader ADD MEMBER Jerry;
For a list of the fixed database roles, see Database-Level Roles.
Later, when you're ready to configure more precise access to your data (highly recommended), create your own user-defined database roles using CREATE ROLE statement. Then assign specific granular permissions to your custom roles.
For example, the following statements create a database role named
Sales, grants the
Sales group the ability to see, update, and delete rows from the
Orders table, and then adds the user
Jerry to the
CREATE ROLE Sales; GRANT SELECT ON Object::Sales TO Orders; GRANT UPDATE ON Object::Sales TO Orders; GRANT DELETE ON Object::Sales TO Orders; ALTER ROLE Sales ADD MEMBER Jerry;
For more information about the permission system, see Getting Started with Database Engine Permissions.
Configure row-level security
Row-Level Security enables you to restrict access to rows in a database based on the user executing a query. This feature is useful for scenarios like ensuring that customers can only access their own data or that workers can only access data that is pertinent to their department.
The following steps walk through setting up two Users with different row-level access to the
Create two user accounts to test the row level security:
USE AdventureWorks2022; GO CREATE USER Manager WITHOUT LOGIN; CREATE USER SalesPerson280 WITHOUT LOGIN;
Grant read access on the
Sales.SalesOrderHeader table to both users:
GRANT SELECT ON Sales.SalesOrderHeader TO Manager; GRANT SELECT ON Sales.SalesOrderHeader TO SalesPerson280;
Create a new schema and inline table-valued function. The function returns 1 when a row in the
SalesPersonID column matches the ID of a
SalesPerson login or if the user executing the query is the Manager user.
CREATE SCHEMA Security; GO CREATE FUNCTION Security.fn_securitypredicate(@SalesPersonID AS int) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE ('SalesPerson' + CAST(@SalesPersonId as VARCHAR(16)) = USER_NAME()) OR (USER_NAME() = 'Manager');
Create a security policy adding the function as both a filter and a block predicate on the table:
CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE Security.fn_securitypredicate(SalesPersonID) ON Sales.SalesOrderHeader, ADD BLOCK PREDICATE Security.fn_securitypredicate(SalesPersonID) ON Sales.SalesOrderHeader WITH (STATE = ON);
Execute the following to query the
SalesOrderHeader table as each user. Verify that
SalesPerson280 only sees the 95 rows from their own sales and that the
Manager can see all the rows in the table.
EXECUTE AS USER = 'SalesPerson280'; SELECT * FROM Sales.SalesOrderHeader; REVERT; EXECUTE AS USER = 'Manager'; SELECT * FROM Sales.SalesOrderHeader; REVERT;
Alter the security policy to disable the policy. Now both users can access all rows.
ALTER SECURITY POLICY SalesFilter WITH (STATE = OFF);
Enable dynamic data masking
Dynamic Data Masking enables you to limit the exposure of sensitive data to users of an application by fully or partially masking certain columns.
ALTER TABLE statement to add a masking function to the
EmailAddress column in the
USE AdventureWorks2022; GO ALTER TABLE Person.EmailAddress ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'email()');
Create a new user
SELECT permission on the table, then execute a query as
TestUser to view the masked data:
CREATE USER TestUser WITHOUT LOGIN; GRANT SELECT ON Person.EmailAddress TO TestUser; EXECUTE AS USER = 'TestUser'; SELECT EmailAddressID, EmailAddress FROM Person.EmailAddress; REVERT;
Verify that the masking function changes the email address in the first record from:
Enable transparent data encryption
One threat to your database is the risk that someone steals the database files off of your hard-drive. This could happen with an intrusion that gets elevated access to your system, through the actions of a problem employee, or by theft of the computer containing the files (such as a laptop).
Transparent data encryption (TDE) encrypts the data files as they are stored on the hard drive. The
master database of the SQL Server database engine has the encryption key, so that the database engine can manipulate the data. The database files can't be read without access to the key. High-level administrators can manage, backup, and recreate the key, so the database can be moved, but only by selected people. When TDE is configured, the
tempdb database is also automatically encrypted.
Since the Database Engine can read the data, TDE doesn't protect against unauthorized access by administrators of the computer who can directly read memory, or access SQL Server through an administrator account.
- Create a master key
- Create or obtain a certificate protected by the master key
- Create a database encryption key and protect it by the certificate
- Set the database to use encryption
Configuring TDE requires
CONTROL permission on the
master database and
CONTROL permission on the user database. Typically an administrator configures TDE.
The following example illustrates encrypting and decrypting the
AdventureWorks2022 database using a certificate installed on the server named
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**********'; GO CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My Database Encryption Key Certificate'; GO USE AdventureWorks2022; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyServerCert; GO ALTER DATABASE AdventureWorks2022 SET ENCRYPTION ON;
To remove TDE, run the following command:
ALTER DATABASE AdventureWorks2022 SET ENCRYPTION OFF;
The encryption and decryption operations are scheduled on background threads by SQL Server. You can view the status of these operations using the catalog views and dynamic management views in the list that appears later in this article.
Backup files of databases that have TDE enabled are also encrypted by using the database encryption key. As a result, when you restore these backups, the certificate protecting the database encryption key must be available. This means that in addition to backing up the database, make sure that you maintain backups of the server certificates to prevent data loss. Data loss will result if the certificate is no longer available. For more information, see SQL Server Certificates and Asymmetric Keys.
For more information about TDE, see Transparent Data Encryption (TDE).
Configure backup encryption
SQL Server has the ability to encrypt the data while creating a backup. By specifying the encryption algorithm and the encryptor (a certificate or asymmetric key) when creating a backup, you can create an encrypted backup file.
Always back up the certificate or asymmetric key, and preferably to a different location than the backup file it was used to encrypt. Without the certificate or asymmetric key, you can't restore the backup, rendering the backup file unusable.
The following example creates a certificate, and then creates a backup protected by the certificate.
USE master; GO CREATE CERTIFICATE BackupEncryptCert WITH SUBJECT = 'Database backups'; GO BACKUP DATABASE [AdventureWorks2022] TO DISK = N'/var/opt/mssql/backups/AdventureWorks2022.bak' WITH COMPRESSION, ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptCert ), STATS = 10 GO
For more information, see Backup Encryption.