Azure SQL Database provides Auditing and Threat detection to proactively protect it from malicious attacks.
Auditing tracks and records database events to an audit log in a given Azure Storage account. Auditing can help you to:
Auditing allows you to:
Auditing can be configured at the server-level and database-level. If auditing is configured at the server level, it'll automatically apply to all of the databases in the server. Auditing configured at the database will only apply to a particular database.
It's recommended to audit the server instead of auditing individual databases.
In this section, we’ll configure auditing for the
toyfactory
Azure SQL Server to take care of unusual activities and trends, and enable analysis of the audit log.
toyfactory
Azure SQL Server.toyfactory
Azure SQL Server pane, under the Settings section, find and select Auditing & Threat Detection:toyfactorystorage
account created in a previous chapter:Click OK to continue.
toyfactorystorage
Azure storage:sqldbauditlogs
container. The sqldbauditlogs
folder contains one folder for every Azure SQL Server that is configured for auditing.Threat detection provides an extra layer of security on top of Auditing by automatically detecting unusual activities and potentially harmful attempts to access or exploit databases. For example, it can detect and alert you about SQL injection attacks.
Users can set up email alerts so that they are notified by an email whenever any such activity is detected.
Unlike Auditing, Threat Detection is a paid service and is charged at $15/node/month. It's free for the first 60 days.
In this section, we’ll configure Threat Detection for the
toyfactory
Azure SQL Server to protect the database from malicious attacks.
toyfactory
Azure SQL Server.toyfactory
Azure SQL Server pane, under the Settings section, find and select Auditing & Threat Detection:In this section, we will understand how to implement row-level security using our example of ToyStore Ltd. Mike has been asked to implement row-level security so that every customer is able to view and edit their records only. The
CustomerAdmin
user, however, should be allowed to view and edit all customer records. Follow these steps to complete the activity:
dbo.Customers
table and populate it with sample records:CREATE TABLE Customers ( CustomerID int identity, Name sysname, CreditCardNumber varchar(100), Phone varchar(100), Email varchar(100) ) Go INSERT INTO Customers VALUES ('Mike',0987654312345678,9876543210,'[email protected]'), ('Mike',0987654356784567,9876549870,'[email protected]'), ('Mike',0984567431234567,9876567210,'[email protected]'), ('[email protected]',0987654312345678,9876246210,'[email protected]'), ('[email protected]',0987654123784567,9876656870,'[email protected]'), ('[email protected]',09856787431234567,9876467210,'[email protected]'), ('CustomerAdmin',0987654312235578,9873456210,'[email protected]'), ('CustomerAdmin',0984564123784567,9872436870,'[email protected]'), ('CustomerAdmin',0945677874312367,9872427210,'[email protected]')
CustomerAdmin
:CREATE USER CustomerAdmin WITHOUT LOGIN
CustomerAdmin
on the dbo.Customers
table:GRANT SELECT ON dbo.Customers TO Mike GO GRANT SELECT ON dbo.Customers TO [[email protected]] GO GRANT SELECT ON dbo.Customers TO CustomerAdmin
CREATE SCHEMA Security; GO CREATE FUNCTION Security.fn_securitypredicate(@Customer AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS predicateresult WHERE @Customer = USER_NAME() OR USER_NAME() = 'CustomerAdmin';
fn_securitypredicate
, which will return 1 (True) when the logged-in username is equal to the @Customer
parameter, or when the logged-in user is CustomerAdmin
.CREATE SECURITY POLICY CustomerFilter ADD FILTER PREDICATE Security.fn_securitypredicate(Name) ON dbo.Customers, ADD BLOCK PREDICATE Security.fn_securitypredicate(Name) ON dbo.Customers AFTER INSERT WITH (STATE = ON);
USER_NAME()
function.dbo.Customers
table:EXECUTE AS USER='Mike' GO SELECT USER_NAME() GO SELECT * FROM dbo.Customers
You should get the following output:
EXECUTE AS USER='Mike' GO SELECT USER_NAME() GO -- CustomerID 4 belongs to John UPDATE dbo.Customers SET Email='[email protected]' WHERE CustomerID=4 GO -- Switch User context to John EXECUTE AS USER='[email protected]' GO SELECT USER_NAME() GO -- Verify if email is updated or not SELECT * FROM dbo.Customers WHERE CustomerID=4
Mike can't update
CustomerID 4
as it belongs to John. You won't get any error, however, the value isn't updated.
You should get the following output:
[email protected]
:EXECUTE AS USER='Mike' GO SELECT USER_NAME() GO INSERT INTO dbo.Customers VALUES('[email protected]',9876543445345678,65412396852,'[email protected]')
The After Insert block predicate will block the insert, as defined by the security policy, and will show the following error:
CustomerAdmin
security context to return all rows from the dbo.Customers
table:REVERT; GO EXECUTE AS USER='CustomerAdmin' GO SELECT USER_NAME() GO SELECT * FROM dbo.Customers
ALTER SECURITY POLICY CustomerFilter WITH (STATE = OFF);
With row-level security implemented in the previous activity, Mike has ensured that the customer can only view his own data; however, to take the security of data to the next level, he wants to mask some of the sensitive data that is shared by the customer. In order to do this, he has to implement Dynamic Data Masking. In this activity, we'll implement Dynamic Data Masking to mask the credit card number, phone number, and email id of a customer.
dbo.Customers
table:CREATE USER TestUser WITHOUT LOGIN; GO GRANT SELECT ON dbo.Customers TO TestUser
creditcardnumber
, phone
, and email
column using different masking functions:ALTER TABLE dbo.Customers ALTER COLUMN Phone VARCHAR(100) MASKED WITH (FUNCTION = 'default()') GO ALTER TABLE dbo.Customers ALTER COLUMN Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') GO ALTER TABLE dbo.Customers ALTER COLUMN CreditCardNumber VARCHAR(100) MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)')
CreditCardNumber
with the partial masking function that masks all characters excluding the last four characters.TestUser
to return all rows from the dbo.Customers
table:EXECUTE AS USER='TestUser' GO SELECT * FROM dbo.Customers;
Customer
table:REVERT; GO SELECT mc.name, t.name as table_name,mc.masking_function FROM sys.masked_columns AS mc JOIN sys.tables AS t ON mc.[object_id] = t.[object_id] WHERE is_masked = 1 and t.name='Customers'
TestUser
to see the masked data:GRANT UNMASK TO TestUser; GO EXECUTE AS USER='TestUser' GO SELECT * FROM dbo.Customers; GO
UNMASK
permission allows the TestUser
to see the masked data.REVERT; REVOKE UNMASK TO TestUSER
18.118.128.105