DP-203: Data Engineering on Microsoft Azure
You have an Azure SQL database named DB1 that contains a table named Table1. Table1 has a field named Customer_ID that is varchar(22).
You need to implement masking for the Customer_ID field to meet the following requirements:
- The first two prefix characters must be exposed.
- The last four prefix characters must be exposed.
- All other characters must be masked.
Does this meet the goal?
Yes
No
Answer is Yes
We must use Custom Text data masking, which exposes the first and last characters and adds a custom padding string in the middle.
Reference:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dynamic-data-masking-get-started
You have an Azure SQL database named DB1 that contains a table named Table1. Table1 has a field named Customer_ID that is varchar(22).
You need to implement masking for the Customer_ID field to meet the following requirements:
- The first two prefix characters must be exposed.
- The last four prefix characters must be exposed.
- All other characters must be masked.
Does this meet the goal?
Yes
No
Answer is No
Must use Custom Text data masking, which exposes the first and last characters and adds a custom padding string in the middle.
References:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dynamic-data-masking-get-started
You have an Azure SQL database named DB1 that contains a table named Table1. Table1 has a field named Customer_ID that is varchar(22).
You need to implement masking for the Customer_ID field to meet the following requirements:
- The first two prefix characters must be exposed.
- The last four prefix characters must be exposed.
- All other characters must be masked.
Does this meet the goal?
Yes
No
Answer is No
Must use Custom Text data masking, which exposes the first and last characters and adds a custom padding string in the middle.
Reference:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dynamic-data-masking-get-started
You plan to use Microsoft Azure SQL Database instances with strict user access control. A user object must:
- Move with the database if it is run elsewhere
- Be able to create additional users
Which two Transact-SQL commands should you run?
ALTER LOGIN Mary WITH PASSWORD = 'strong_password'; CREATE LOGIN Mary WITH PASSWORD = 'strong_password'; ALTER ROLE db_owner ADD MEMBER Mary; CREATE USER Mary WITH PASSWORD = 'strong_password'; GRANT ALTER ANY USER TO Mary; ALTER ROLE adds or removes members to or from a database role, or changes the name of a user-defined database role.
Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server.
CREATE USER adds a user to the current database.
Note: Logins are created at the server level, while users are created at the database level. In other words, a login allows you to connect to the SQL Server service (also called an instance), and permissions inside the database are granted to the database users, not the logins. The logins will be assigned to server roles (for example, serveradmin) and the database users will be assigned to roles within that database (eg. db_datareader, db_bckupoperator).
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql
You have an Azure SQL server named Server1 that hosts two development databases named DB1 and DB2.
You have an administrative workstation that has an IP address of 192.168.8.8. The development team at your company has an IP addresses in the range of 192.168.8.1 to 192.168.8.5.
You need to set up firewall rules to meet the following requirements:
- Allows connection from your workstation to both databases.
- The development team must be able connect to DB1 but must be prevented from connecting to DB2.
- Web services running in Azure must be able to connect to DB1 but must be prevented from connecting to DB2.
Create a firewall rule on DB1 that has a start IP address of 192.168.8.1 and an end IP address of 192.168.8.5.
Create a firewall rule on DB1 that has a start and end IP address of 0.0.0.0.
Create a firewall rule on Server1 that has a start IP address of 192.168.8.1 and an end IP address of 192.168.8.5.
Create a firewall rule on DB1 that has a start and end IP address of 192.168.8.8.
Create a firewall rule on Server1 that has a start and end IP address of 192.168.8.8.
Answers are;
Create a firewall rule on DB1 that has a start IP address of 192.168.8.1 and an end IP address of 192.168.8.5.
Create a firewall rule on Server1 that has a start IP address of 192.168.8.1 and an end IP address of 192.168.8.5.
Create a firewall rule on Server1 that has a start and end IP address of 192.168.8.8.
Your company manages a payroll application for its customers worldwide. The application uses an Azure SQL database named DB1. The database contains a table named Employee and an identity column named EmployeeId.
A customer requests the EmployeeId be treated as sensitive data.
Whenever a user queries EmployeeId, you need to return a random value between 1 and 10 instead of the EmployeeId value.
Which masking format should you use?
string
number
default
You have an Azure SQL database that contains a table named Customer. Customer contains the columns shown in the following table.
You apply a masking rule as shown in the following table.
Which users can view the email addresses of the customers?
Server administrators and all users who are granted the UNMASK permission to the Customer_Email column only.
All users who are granted the UNMASK permission to the Customer_Email column only.
Server administrators only.
Server administrators and all users who are granted the SELECT permission to the Customer_Email column only.
Answer is All users who are granted the UNMASK permission to the Customer_Email column only.
Grant the UNMASK permission to a user to enable them to retrieve unmasked data from the columns for which masking is defined.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking
You need to ensure that Azure Data Factory pipelines can be deployed.
How should you configure authentication and authorization for deployments?
A-A
A-B
A-C
B-B
B-C
B-D
C-C
D-D
Answer is A - A
The way you control access to resources using RBAC is to create role assignments. This is a key concept to understand – it’s how permissions are enforced. A role assignment consists of three elements: security principal, role definition, and scope.
Scenario:
No credentials or secrets should be used during deployments
Phone-based poll data must only be uploaded by authorized users from authorized devices
Contractors must not have access to any polling data other than their own Access to polling data must set on a per-active directory user basis
References:
https://docs.microsoft.com/en-us/azure/role-based-access-control/overview
Your company uses Azure SQL Database and Azure Blob storage.
All data at rest must be encrypted by using the company’s own key. The solution must minimize administrative effort and the impact to applications which use the database.
You need to configure security.
What should you implement?
A-B
B-C
C-D
D-C
B-D
C-A
D-B
A-C
Answer is
Box 1: transparent data encryption
TDE with customer-managed keys in Azure Key Vault allows to encrypt the Database Encryption Key (DEK) with a customer-managed asymmetric key called TDE Protector. This is also generally referred to as Bring Your Own Key (BYOK) support for Transparent Data Encryption.
Note: Transparent data encryption encrypts the storage of an entire database by using a symmetric key called the database encryption key. This database encryption key is protected by the transparent data encryption protector.
Transparent data encryption (TDE) helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Data Warehouse against the threat of malicious offline activity by encrypting data at rest. It performs real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application.
Box 2: Storage account keys
You can rely on Microsoft-managed keys for the encryption of your storage account, or you can manage encryption with your own keys, together with Azure Key Vault.
References:
https://docs.microsoft.com/en-us/azure/sql-database/transparent-data-encryption-azure-sql
https://docs.microsoft.com/en-us/azure/storage/common/storage-service-encryption
You have an Azure subscription that contains the following resources:
- An Azure Active Directory (Azure AD) tenant that contains a security group named Group1
- An Azure Synapse Analytics SQL pool named Pool1
You need to control the access of Group1 to specific columns and rows in a table in Pool1.
Which Transact-SQL commands should you use?
A-B
B-C
C-D
D-C
A-D
B-A
C-C
D-B
Answer is D - C
Box 1: GRANT
You can implement column-level security with the GRANT T-SQL statement.
Box 2: CREATE SECURITY POLICY
Implement Row Level Security by using the CREATE SECURITY POLICY Transact-SQL statement
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/column-level-security