Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

DP-300 > Module 3 - Implement a Secure Environment > Flashcards

Module 3 - Implement a Secure Environment Flashcards

1
Q

What does AD and Azure AD Provide

A

Both provide Authentication services and ID Management

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What does AD use to provide Authentication and what is used for Querying AD

A

AD uses Kerberos to provide Authentication using tickets and queries via LDAP (Lightweight Directory Access Protocol)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What Protocols does AZ AD use

A

HTTPS protocols like, SAML and OpenID Connect for Authentication along with OAUTH for Authorizaion

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

How would you go about providing AZ AD services to an on-prem Windows Server

A

You would need to configure AZ AD Connect to connect AD IDs with AZ AD

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What are the modes of AuthN in SQL and which is considered most secure and why

A

Windows Mode and SQL Mode (Windows and Mixed)

Windows is more secure as SQL allows login info to be seen in plain text when passed over the network.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Describe SQL Authentication

A

SQL logon stored in master DB (or user DB if using contained users)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Describe Windows Authentication in the context of SQL Server

A

Connect to SQL using AD Credentials

Windows also allows for easier management of user turnover

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

True or False - Azure SQL Database supports SQL AuthN and AZ AD AuthN giving users the ability to log into SQL with the same credentials as pother resources such as the Azure portal of O365

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How can you connect to on prem devices using the same AZ AD Credentials

A

AZ AD can be configured to sync with on-prem AD allowsing access to on-prem and AZ resources

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

True or False AZ AD cannot provide MFA services

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What are the headlines around the Azure AD Admin Config within the portal for a DB resource

A

Allows admin access to all DBs in server
Best practice is to make this a group
Grants special sysadmin type access
Can be set via ARM using Portal, PowerShell or CLI - not at the DB level though

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is a Security Principal

A

An entitiy that can request access which you can (usually) grant access to.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Do security principals exist at the Server or DB level + are security principals individuals or collections

A

Security principals exist at either the server level or the database level and can be either individuals or collections

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are the 3 scopes for securables

A

Server,
Database,
Schema

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is a Schema

A

A schema is a collection of objects within a DB allowing objects to be grouped into seperate name spaces

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is a securable

A

A securable is the resource (i.e. a table in a database)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

When no Schema is specified in a query which order will schemas be looked in

A

1st = users default schema
2nd = DBO
If not in either of those an error will be returned

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

When writing queries best practice dictates to specify a schema.
If no schema is specified when creating a an object it will go where? And what happens if the user cannot create objects at that location

A

A create statement with no defined schema will go into the users default schema - if the user does not have permission for this access will be denied.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Which DB are (SQL and Windows) Logins stored in

A

Master

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Where are contained users stored and What Authentication can they use?

A

Stored in the UserDB

AuthN with SQL Auth, Win Auth, AZ AD Auth

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is the pre-requisite to using contained users and what is the default state of this for the DB Variants

A

Database must be configured for “Partial Containment”
Default in AZ,
Optional Setting in OnPrem/IaaS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Why is it best practice in Azure SQL DB to use Contained users

A

These users only have access to the DB set up in

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What are some of the headlines on Database and Server Roles

A

Effectively Security groups sharing common permissions
Built in Roles are decided by Microsoft with ability to add custom roles at Server or DB level
Server Roles cannot be granted access to DB objects directly
Server Roles are only available in IaaS, On Prem and MI

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Describe Application Roles

A

A user is not a member of an App role, the app role is activated by using the password.
Once active app role permissions are applied to a user until de-activated

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What does the role db_accessadmin do

A

The db_accessadmin allows creation of other users in DB, no access to Schema, Tables of Data within database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What does the role db_backupoperator do

A

The db_backupoperator role allows users to backup the Database (obviously not applicable with Azure SQL DB)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What does the Role db_datareader do

A

The db_datareader rol;e allows read from every table/view in DB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What does the role db_datawriter do

A

The db_datawriter role allows insert/update/delete to all tables and views in the DB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What does the role db_ddladmin do

A

The db_ddladmin role allows the creation and modification of objects in the DB with no read/write access to the data itself

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

What does the role db_denydatareader do

A

Prevents reading of any Db data when a user has had access through other roles or directly granted

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What does the role db_denydatawriter do

A

Prevents writing of any data to DB when a user has had access through other roles or directly granted.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

What does the role db_owner do

A

Allows user to perform any action in database and cannot be denied

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What does the role db_security admin do

A

Grants access to other users - no access to data itself but can grant access to tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

What does the public database role do?

A

Default role without permissions unless you assign some to it - however all users including guest (if enabled) have access to this role.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What should you do if you have more granular security requirements than the fixed database roles provide

A

Use Custom Database roles

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What 2 additional database roles does Azure SQL DB have and where are they stored

A

DBManager
Login Manager
Both stored in Master DB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

What does the Azure SQL DB role DBManager allow?

A

Allows members to create DBs (similar to the dbcreator fixed server role)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What does the Azure SQL DB role LoginManager allow?

A

Allows creation of additional logins at th eserver level (Similar to the security admin fixed server role)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

What does the fixed server role SysAdmin allow

A

Sysadmin allows member to perform any action on server

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

What does the fixed server role ServerAdmin allow

A

ServerAdmin allows member to change server wide config and shutdown the server

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

What does the fixed server role SecurityAdmin allow

A

SecurityAdmin allows the management of logins and well as grand and revoke of Server and DB level permissions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

What does the fixed server role ProcessAdmin allow

A

ProccessAdmin allows member to kill processes in SQL Server

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

What does the fixed server role SetupAdmin allow

A

SetupAdmin allows Add and Remove of linked servers via T-SQL

44
Q

What does the fixed server role BulkAdmin allow

A

BulkAdmin allows member to run BULK INSERT T-SQL

45
Q

What does the fixed server role DiskAdmin allow

A

DiskAdmin allows management of Backup devices in SQl

46
Q

What does the fixed server role DBCreator allow

A

DBCreator allows creation, restore, alter and drop of any DB

47
Q

What does the fixed server role PUBLIC do

A

Public is a fixed server role that everyone belongs to - this can be granted, denied or revoked permissions.

48
Q

What are the 4 basic permissions within a DBMS

A

SELECT, UPDATE, INSERT, DELETE

49
Q

Which takes precidence, GRANT or DENY?

A

DENY

50
Q

What is EXECUTE AS USER in SQL Server and Where is it availbale

A

Allwos for user context to be change

Availble in OnPrem, IaaS and MI

51
Q

True or False - You cannot restrict columns available to a security principal

A

False - you can restrict columns to a security principal

52
Q

The 4 main permissions on tables and views are, Select, Insert, Update, Delete - what additional permissions are there for Azure SQL DB and Managed Instance

A

CONTROL and REFERENCES

53
Q

What do the Control and References permissions do with regards to table permissions in Azure SQL DB and MI

A

Control - Grants rights to all objects, can perform any action on an object
References - Allows the viewing of FK on an object (also available in MySQL and PostgreSQL in Azure)

54
Q

What are the 3 permissions of Stored Procs and Functions and what do they do

A

Alter - Change the defnition of an object
Control - Grants all rights to an object
Execute - Allows execution of an object

55
Q

What does View Change Tracking and View Definition control

A

View Change Tracking - Allows viewing of the change tracking settings
View Definition - Allows viewing of the definitions

56
Q

What does ownership chaining allow and what are the 2 caveats

A

Ownership chaining allows users to inherit permissions from other objects (such as a user that has no access to a certain table but can get the data via executing a Stored proc that can get the data from the table)
Caveat 1 - Owners of all the objects in question need to be the same
Caveat 2 - Does not work with Dynamic SQL

57
Q

Describe TDE

A

TDE = Transparent Data Encryption
Encrypts all the data in a database, with data being encrypted as data written to the page and decrypted as being read into memory.
Prevents underlying data and log files being attached elsewhere
No decryption is done when taking backups so backups end up encrypted
New Azure SQL DBs and MI have this on by default (can be turned off via the portal)
Azure SQL DB allows use of MS key and BYOK
If TDE Certificate is lost you will be unable to restore you DB elsewhere

58
Q

What are the Steps to enable TDE via T-SQL

A

Create Master Key in MasterDB
Create Cert in MasterDB used for encryption
Create Database Key in target DB using the Cert
Alter Database Statement to enable TDE

59
Q

What does SQL do once you enable TDE

A

Starts to encrypt all the pages which can take some time, overhead is kept to a minimum as this is deemed a low priority task by SQL

60
Q

TDE is not available on Azure MySQL or PostgreSQL - what can you do instead?

A

Instead of TDE you can encrypt the disks you databases are stored on.

61
Q

Azure Key Vault configuration is easy for Azure Resources and can be done for VMs at creation or a later date, what information is required when configuring Azure Key Vault

A

Key vault URL
Principal Name
Principal Secret
Credential Name

62
Q

What are the steps to configure Azure Key Vault within SQL Server?

A

Create SQL Login at instance
Create and Map credential to login
—Use the name of the key vault for the identity of the credential
—Use the ApplicationID in the key vault for the credential secret
Then…
Create an Asymmetric Key in Key Vault
Create an Asymmetric Key in SQL DB which maps to key vault using “CREARE ASYMMETRIC KEY … FROM PROVIDER” Syntax
The Asymmetric key can then be used for Backups, TDE Always Encrypted etc

63
Q

What does Azure Disk Encryption do

A

Encrypts disks of Azure VMs for extra level of protection as an ADDITION to any SQL Level encryption you implement

64
Q

What is Azure Key Vault and How would you typically access it

A

Azure Key Vault is a tool for storing secrets and is typically accessed programatically

65
Q

What’s special about the Azure Key Vault RBAC policies

A

Azure Key Vault RBAC policies are seperate from the subscription so a subscription admin doesn’t nessecairly have access to Azure Key Vault secrets

66
Q

Describe Always Encrypted

A

Encrypts data within columns
Allows encryption of data at the Client App based on the column settings within SQL Server
Based on Master + Column Encryption Keys (columns can be encyrpted with different keys for additional protection

67
Q

Describe the process of using Always Encrypted

A

In AE Wizard Choose Columns to encrypt, the encryption type to use and configure the column encryption key
Generate or Select the master encryption key

68
Q

Where must an AE Master Encryption Key be stored

A

In a keystore such as Azure Key Vault or Windows Certificate Store or a Hardware Security Module

69
Q

What is the difference between deterministic and randomized encryption

A 
Deterministic = Source data always encrypts to same encrypted value
Randomized = Identical source data will have different encrypted values
70
Q

What are the requirements to decrypt AE encrypted data?

A

App needs to connect via a driver that supports AE
App needs access to the key store

App can then retrieve data, any data written back is encrypted at the client driver level.

71
Q

What does the applications connection string need to include to use AE

A

App connection string needs to include “Column Encryption Setting = Enabled” to be able to use AE

72
Q

Where can further adjustments to the metadata queries for AE be adjusted

A

SQLCommandColumnEncryptionSetting

73
Q

For the Metadata queries in AE define, Disabled, Enabled and ResultSet

A 
Disabled = No meta data queries 
Enabled = All meta data queries
ResultSet = Only queries values in the SELECT statement (i.e. not the where clause etc)
74
Q

What is a Secure Enclave

A

Secure portion of memory in SQL Server for processing encrypted data
A black box that you cannot view any data or code within - even with a debugger

75
Q

What benefit does Secure Enclave bring

A

Allows pattern matching and range comparisons on Randomized Encryption columns

76
Q

Define Dynmaic Data Masking

A

Allows viewing of secure data whilst masking part of the value
Presentation layer feature

77
Q

How can DDM be implemented

A

Via the Azure Portal (for PaaS) or via T-SQL using the ALTER TABLE _ ALTER COLUMN Command

78
Q

What does the mask option default do?

A

Displays X’s for strings, 0 for numbers and 01/01/1900 for dates

79
Q

What does the mask option Credit Card do

A

Displays xxxx-xxxx-xxxx-1234

80
Q

What does the mask option Social Security Number do?

A

Displays xxx-xx-1234

81
Q

What does the mask option Random Number do

A

Displays a random number - should only be used for number columns

82
Q

What does the Custom Text Mask option allow

A

Custom rules to be set up

83
Q

Give a good use case for Dynamic Data Masking

A

When exporting a prod DB to dev, do so with an account that does not have (UNMASK PERMISSIONS) to persist the maskings in the dev environment.

84
Q

What are firewalls used for

A

To prevent unautorized access to protected resources

85
Q

Fill in the Blanks
Each Azure SQL DB maps to a [blank 1] [blank 2] hosted by Microsoft.
Each Azure Region will have one of more [blank 3] [blank 4] where you can reach you [blank 5] [blank 6], which will take you to your DB

A 
1. Public
2 IP
3 Public
4 IP
5 Database
6 Gateway
86
Q

Azure provides built in firewall to limit access, it has 2 sets of firewall rules what are these

A

Server Firewall rules

Database Firewall rules

87
Q

What do the Azure Firewall rules use to govern Authentication

A

Azure Firewalls use IP Rules instead of SQL Logins, this allows all users at same location access

88
Q

What does a server level firewall allow

A

Access to MasterDB and all user DBS

89
Q

What does a database level firewall allow

A

Access to a specific DB

90
Q

How can Server level firewalls be configured

A

Via Azure Portal or T-SQL

91
Q

How can Database level firewalls be configured

A

Via T-SQL only

92
Q

Describe the steps Azure SQL DB takes (in terms of firewalls) when a user is making a connection?

A

Upon Connection Azure SQL DB looks for a server firewall rule in master.
If a DB is specified in connection string will look to the database level firewall
If either exist the connection complete.
If neither exist and User is connection via SSMS or Azure Data Studio they will be prompted to create a firewall rule

93
Q

Describe Virtual Network Endpoints

A

Allow traffic from a specific Azure vNet
Rules apply at server level not just DB level
Service endpoint applies to only one region (the underlying endpoints region)
Added concern is that Azure DB must have outbound access to the Public IP address for Azure SQL DB - can be configured with tags

94
Q

Describe Private Link

A

Allows you to connect to Azure SQL DB (and other PaaS offerrings) via a private endpoint over the Azure Backbone and not via the Public Internet
Provide Private IP Address on your vNet
Allows for Express Route connections through that circuit
Also allows for Cross-Region private connectivity and protection against data leakage by only allows connections to specific resources

95
Q

Confidential data should be classified as such, data classification allows apps and users to know the sensitivity of data - at what level does this happen

A

Data classification is done on a column by column basis.

96
Q

Where is sensitivity meta data for data classification stored as of SQL 2019

A

In the DMV, sys.sensitivity_classification (previously in extended properties)

97
Q

Data classification can be managed from where

A

Data classification can be managed from Azure Portal (for PaaS) and via SSMS

98
Q

You should verify classification suggestions by the Classification Engine…why?

A

You should verify classification suggestions by the Classification Engine as it scans based on Column name

99
Q

You can add Sensitivity classifications to columns via T-SQL, why is this practice good for users and DBA

A

It allows users to make better desicions on how to handle data
It gives DBAs a good idea on which columns to encrypt.

100
Q

Describe Advanced Threat Protection

A

Advanced Threat Protection offers a suite of protections

Configured via the Az

101
Q

What does Advanced Threat Protection monitor

A

Advanced Threat Protection Monitors Connections and Queries being executed

102
Q

Where is Advanced Threat Protection managed from

A

Advanced Threat protection is managed via the AZure Portal

103
Q

What should you do to get maximum benefit from Advanced Threat Protection

A

To get maximum benefit from Advanced Threat protection you should enable Auditing as this allows for deeper investigations

104
Q

What alerts does Advanced Threat Protection have

A

Vulnerability to SQL injection (checks for things like a SP not sanitizing user input.
Potential SQL Injection (when a user is actively trying to carry out a SQL injection attack)
Access from Unusual location
Access from Unusual Azure Data Centre
Access from Unfamiliar principal (when user or app uses a DB they havent before)
Access from potentially harmful application
BruteForce of SQL Credentials

105
Q

Describe SQL Injection

A

Common Method for data breaches

Attacker appens SQL Code via user entry form in attempt to execute there own SQL

106
Q

What is the Best way to combat SQL injection

A

Best way to combat SQL injection is to check input is the correct data type is of correct length etc and contains no Binary data

Whilst Fixing Application should be top priority - Advanced Threat Protection can help protect you as a backup

DP-300 (4 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions