# Migrating security features to Aurora MySQL
<a name="chap-sql-server-aurora-mysql.security"></a>

This topic provides reference information about security features and encryption capabilities in Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can gain insights into how these database systems handle user permissions, access control, data encryption, and secure connections. The content explores the similarities and differences between SQL Server and Aurora MySQL in areas such as Transparent Data Encryption, user management, authentication methods, and encrypted connections.

**Topics**
+ [

# Column encryption for Aurora MySQL
](chap-sql-server-aurora-mysql.security.columnencryption.md)
+ [

# Data control language for Aurora MySQL
](chap-sql-server-aurora-mysql.security.datacontrollanguage.md)
+ [

# Transparent data encryption Aurora MySQL
](chap-sql-server-aurora-mysql.security.transparentdataencryption.md)
+ [

# Users and roles for Aurora MySQL
](chap-sql-server-aurora-mysql.security.usersroles.md)
+ [

# Encrypted connections for Aurora MySQL
](chap-sql-server-aurora-mysql.security.encryptedconnections.md)

# Column encryption for Aurora MySQL
<a name="chap-sql-server-aurora-mysql.security.columnencryption"></a>

This topic provides reference information about encryption and decryption functions in SQL Server and Amazon Aurora MySQL. You can use these functions to secure sensitive data in your database, such as individual column contents or application user security tokens.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-3.png)   |   ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-automation-0.png)   |  N/A  |  Difference.  | 

## SQL Server Usage
<a name="chap-sql-server-aurora-mysql.security.columnencryption.sqlserver"></a>

SQL Server provides encryption and decryption functions to secure the content of individual columns. The following list identifies common encryption functions:
+ EncryptByKey and DecryptByKey.
+ EncryptByCert and DecruptByCert.
+ EncryptByPassPhrase and DecruptByPassPhrase.
+ EncryptByAsymKey and DecryptByAsymKey.

You can use these functions anywhere in your code; they aren’t limited to encrypting table columns. A common use case is to increase run time security by encrypting of application user security tokens passed as parameters.

These functions follow the general SQL Server encryption hierarchy, which in turn use the Windows Server Data Protection API.

Symmetric encryption and decryption consume minimal resources and can be used for large data sets.

**Note**  
This section doesn’t cover Transparent Data Encryption (TDE) or AlwaysEncrypted end-to-end encryption.

### Syntax
<a name="chap-sql-server-aurora-mysql.security.columnencryption.sqlserver.syntax"></a>

The following example includes the general syntax for EncryptByKey and DecryptByKey.

```
EncryptByKey ( <key GUID> , { 'text to be encrypted' }, { <use authenticator flag>}, { <authenticator> } );
```

```
DecryptByKey ( 'Encrypted Text' , <use authenticator flag>, { <authenticator> )
```

### Examples
<a name="chap-sql-server-aurora-mysql.security.columnencryption.sqlserver.examples"></a>

The following example demonstrates how to encrypt an employee Social Security Number.

The following example creates a database master key.

```
USE MyDatabase;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '<MyPassword>';
```

The following examples create a certificate and a key.

```
CREATE CERTIFICATE Cert01
WITH SUBJECT = 'SSN';
```

```
CREATE SYMMETRIC KEY SSN_Key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Cert01;
```

The following example creates an employees table.

```
CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    SSN_encrypted VARBINARY(128) NOT NULL
);
```

Open the symmetric key for encryption.

```
OPEN SYMMETRIC KEY SSN_Key
DECRYPTION BY CERTIFICATE Cert01;
```

Insert the encrypted data.

```
INSERT INTO Employees (EmployeeID, SSN_encrypted)
VALUES
(1, EncryptByKey(Key_GUID('SSN_Key') , '1112223333', 1, HashBytes('SHA1', CONVERT(VARBINARY, 1)));
```

```
SELECT EmployeeID,
CONVERT(CHAR(10), DecryptByKey(SSN, 1 , HashBytes('SHA1', CONVERT(VARBINARY, EmployeeID)))) AS SSN
FROM Employees;

EmployeeID  SSN_Encrypted              SSN
1           0x00F983FF436E32418132...  1112223333
```

For more information, see [Encrypt a Column of Data](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver15) and [Encryption Hierarchy](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encryption-hierarchy?view=sql-server-ver15) in the *SQL Server documentation*.

## MySQL Usage
<a name="chap-sql-server-aurora-mysql.security.columnencryption.mysql"></a>

 Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) provides encryption and decryption functions similar to SQL Server with a much less elaborate security hierarchy that is easier to manage.

The encryption functions require the actual key as a string, so you must take extra measures to protect the data. For example, hashing the key values on the client.

 Aurora MySQL supports the AES and DES encryption algorithms. You can use the following functions for data encryption and decryption:
+  `AES_DECRYPT` 
+  `AES_ENCRYPT` 
+  `DES_DECRYPT` 
+  `DEC_ENCRYPT` 

**Note**  
The `ENCRYPT`, `DECRYPT`, `ENCODE`, and `DECODE` functions are deprecated beginning with MySQL version 5.7.2 and 5.7.6. Asymmetric encryption isn’t supported in Aurora MySQL.

**Note**  
 Amazon Relational Database Service (Amazon RDS) for MySQL 8 supports FIPS mode if compiled using OpenSSL and an OpenSSL library and FIPS Object Module are available at runtime. FIPS mode imposes conditions on cryptographic operations such as restrictions on acceptable encryption algorithms or requirements for longer key lengths. For more information, see [FIPS Support](https://dev.mysql.com/doc/refman/8.0/en/fips-mode.html) in the *MySQL documentation*.

### Syntax
<a name="chap-sql-server-aurora-mysql.security.columnencryption.mysql.syntax"></a>

The following example shows the general syntax for the encryption functions:

```
[A|D]ES_ENCRYPT(<string to be encrypted>, <key string> [,<initialization vector>])
[A|D]ES_DECRYPT(<encrypted string>, <key string> [,<initialization vector>])
```

For more information, see [AES\$1ENCRYPT](https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_aes-encrypt) in the *MySQL documentation*.

It is highly recommended to use the optional initialization vector to circumvent whole value replacement attacks. When encrypting column data, it is common to use an immutable key as the initialization vector. With this approach, decryption fails if a whole value moves to another row.

Consider using SHA2 instead of SHA1 or MD5 because there are known exploits available for the SHA1 and MD5. Passwords, keys, or any sensitive data passed to these functions from the client aren’t encrypted unless you are using an SSL connection. One benefit of using AWS IAM is that database connections are encrypted with SSL by default.

### Examples
<a name="chap-sql-server-aurora-mysql.security.columnencryption.mysql.examples"></a>

The following examples demonstrate how to encrypt an employee Social Security Number.

The following example creates an employees table.

```
CREATE TABLE Employees
(
    EmployeeID INT NOT NULL PRIMARY KEY,
    SSN_Encrypted BINARY(32) NOT NULL
);
```

The following example inserts the encrypted data.

```
INSERT INTO Employees (EmployeeID, SSN_Encrypted)
VALUES (1, AES_ENCRYPT('1112223333', UNHEX(SHA2('MyPassword',512)), 1));
```

**Note**  
Use the UNHEX function for more efficient storage and comparisons.

Verify decryption.

```
SELECT EmployeeID,
SSN_Encrypted,
AES_DECRYPT(SSN_Encrypted, UNHEX(SHA2('MyPassword',512)), EmployeeID) AS SSN
FROM Employees

EmployeeID SSN_Encrypted     SSN
1          ` ©> +yp°øýNZ~Gø  1112223333
```

For more information, see [Encryption and Compression Functions](https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html) in the *MySQL documentation*.

# Data control language for Aurora MySQL
<a name="chap-sql-server-aurora-mysql.security.datacontrollanguage"></a>

This topic provides reference information foruser permissions and access control in Amazon Aurora MySQL compared to Microsoft SQL Server. You can understand the similarities and differences in how these database systems manage user privileges, including the types of permissions available, the granularity of access control, and the commands used to grant or revoke permissions.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-automation-0.png)   |  N/A  |  Difference.  | 

## SQL Server Usage
<a name="chap-sql-server-aurora-mysql.security.datacontrollanguage.sqlserver"></a>

The ANSI standard specifies, and most Relational Database Management Systems (RDBMS) use `GRANT` and `REVOKE` commands to control permissions.

However, SQL Server also provides a `DENY` command to explicitly restrict access to a resource. `DENY` takes precedence over `GRANT` and is needed to avoid potentially conflicting permissions for users having multiple logins. For example, if a user has `DENY` for a resource through group membership but `GRANT` access for a personal login, the user is denied access to that resource.

SQL Server allows granting permissions at multiple levels from lower-level objects such as columns to higher level objects such as servers. Permissions are categorized for specific services and features such as the service broker.

Permissions are used in conjunction with database users and roles.

For more information, see [Users and Roles](chap-sql-server-aurora-mysql.security.usersroles.md).

### Syntax
<a name="chap-sql-server-aurora-mysql.security.datacontrollanguage.sqlserver.syntax"></a>

The following examples show the simplified syntax for SQL Server DCL commands:

```
GRANT { ALL [ PRIVILEGES ] } | <permission> [ ON <securable> ] TO <principal>
```

```
DENY { ALL [ PRIVILEGES ] } | <permission> [ ON <securable> ] TO <principal>
```

```
REVOKE [ GRANT OPTION FOR ] {[ ALL [ PRIVILEGES ] ]|<permission>} [ ON <securable> ] { TO | FROM } <principal>
```

For more information, see [Permissions Hierarchy (Database Engine)](https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-hierarchy-database-engine?view=sql-server-ver15) in the *SQL Server documentation*.

## MySQL Usage
<a name="chap-sql-server-aurora-mysql.security.datacontrollanguage.mysql"></a>

 Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) supports the ANSI Data Control Language (DCL) commands `GRANT` and `REVOKE`.

Administrators can grant or revoke permissions for individual objects such as a column, a stored function, or a table. Administrators can grant permissions to multiple objects using wildcards.

Only explicitly granted permissions can be revoked. For example, if a user was granted `SELECT` permissions for the entire database using the following command:

```
GRANT SELECT
ON database.*
TO UserX;
```

It isn’t possible to `REVOKE` the permission for a single table. Instead, revoke the `SELECT` permission for all tables using the following command:

```
REVOKE SELECT
ON database.*
FROM UserX;
```

 Aurora MySQL provides a `GRANT` permission option, which is very similar to the `WITH GRANT OPTION` clause in SQL Server. This permission gives a user permission to further grant the same permission to other users.

```
GRANT EXECUTE
ON PROCEDURE demo.Procedure1
TO UserY
WITH GRANT OPTION;
```

**Note**  
 Aurora MySQL users can have resource restrictions associated with their accounts similar to the SQL Server resource governor. For more information, see [Resource Governor](chap-sql-server-aurora-mysql.management.resourcegovernor.md).

The following table identifies Aurora MySQL privileges:


| Permissions | Use to | 
| --- | --- | 
|   `ALL [PRIVILEGES]`   |  Grant all privileges at the specified access level except `GRANT OPTION` and `PROXY`.  | 
|   `ALTER`   |  Enable use of `ALTER TABLE`. Levels: Global, database, table.  | 
|   `ALTER ROUTINE`   |  Enable stored routines to be altered or dropped. Levels: Global, database, procedure.  | 
|   `CREATE`   |  Enable database and table creation. Levels: Global, database, table.  | 
|   `CREATE ROUTINE`   |  Enable stored routine creation. Levels: Global, database.  | 
|   `CREATE TEMPORARY TABLES`   |  Enable the use of `CREATE TEMPORARY TABLE`. Levels: Global, database.  | 
|   `CREATE USER`   |  Enable the use of `CREATE USER`, `DROP USER`, `RENAME USER`, and `REVOKE ALL PRIVILEGES`. Level: Global.  | 
|   `CREATE VIEW`   |  Enable views to be created or altered. Levels: Global, database, table.  | 
|   `DELETE`   |  Enable the use of `DELETE`. Level: Global, database, table.  | 
|   `DROP`   |  Enable databases, tables, and views to be dropped. Levels: Global, database, table.  | 
|   `EVENT`   |  Enable the use of events for the Event Scheduler. Levels: Global, database.  | 
|   `EXECUTE`   |  Enable the user to run stored routines. Levels: Global, database, table.  | 
|   `GRANT OPTION`   |  Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, procedure, proxy.  | 
|   `INDEX`   |  Enable indexes to be created or dropped. Levels: Global, database, table.  | 
|   `INSERT`   |  Enable the use of `INSERT`. Levels: Global, database, table, column.  | 
|   `LOCK TABLES`   |  Enable the use of `LOCK TABLES` on tables for which you have the `SELECT` privilege. Levels: Global, database.  | 
|   `PROXY`   |  Enable user proxying. Level: From user to user.  | 
|   `REFERENCES`   |  Enable foreign key creation. Levels: Global, database, table, column.  | 
|   `REPLICATION CLIENT`   |  Enable the user to determine the location of primary and secondary servers. Level: Global.  | 
|   `REPLICATION SLAVE`   |  Enable replication replicas to read binary log events from the primary. Level: Global.  | 
|   `SELECT`   |  Enable the use of `SELECT`. Levels: Global, database, table, column.  | 
|   `SHOW DATABASES`   |  Enable `SHOW DATABASES` to show all databases. Level: Global.  | 
|   `SHOW VIEW`   |  Enable the use of `SHOW CREATE VIEW`. Levels: Global, database, table.  | 
|   `TRIGGER`   |  Enable trigger operations. Levels: Global, database, table.  | 
|   `UPDATE`   |  Enable the use of `UPDATE`. Levels: Global, database, table, column.  | 

### Syntax
<a name="chap-sql-server-aurora-mysql.security.datacontrollanguage.mysql.syntax"></a>

```
GRANT <privilege type>...
ON [object type] <privilege level>
TO <user> ...
```

```
REVOKE <privilege type>...
ON [object type] <privilege level>
FROM <user> ...
```

**Note**  
Table, Function, and Procedure object types can be explicitly stated but aren’t mandatory.

### Examples
<a name="chap-sql-server-aurora-mysql.security.datacontrollanguage.mysql.examples"></a>

Attempt to `REVOKE` a partial permission that was granted as a wild card permission.

```
CREATE USER TestUser;
GRANT SELECT
    ON Demo.*
    TO TestUser;
REVOKE SELECT ON Demo.Invoices
    FROM TestUser
```

For the preceding example, the result looks as shown following.

```
SQL ERROR [1147][42000]: There is no such grant defined for user TestUser on host '%'
on table 'Invoices'
```

Grant the `SELECT` permission to a user on all tables in the demo database.

```
GRANT SELECT
ON Demo.*
TO 'user'@'localhost';
```

Revoke `EXECUTE` permissions from a user on the `EmployeeReport` stored procedure.

```
REVOKE EXECUTE
ON Demo.EmployeeReport
FROM 'user'@'localhost';
```

For more information, see [GRANT Statement](https://dev.mysql.com/doc/refman/5.7/en/grant.html) in the *MySQL documentation*.

# Transparent data encryption Aurora MySQL
<a name="chap-sql-server-aurora-mysql.security.transparentdataencryption"></a>

This topic provides reference information about data encryption capabilities in Microsoft SQL Server and Amazon Aurora MySQL. You can understand how Transparent Data Encryption (TDE) works in SQL Server to protect data at rest without requiring application changes.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-4.png)   |  N/A  |  N/A  |  Enable encryption when creating the database instance.  | 

## SQL Server Usage
<a name="chap-sql-server-aurora-mysql.security.transparentdataencryption.sqlserver"></a>

Transparent data encryption (TDE) is an SQL Server feature designed to protect data at-rest in the event an attacker obtains the physical media containing database files.

TDE doesn’t require application changes and is completely transparent to users. The storage engine encrypts and decrypts data on-the-fly. Data isn’t encrypted while in memory or on the network. TDE can be turned on or off individually for each database.

TDE encryption uses a Database Encryption Key (DEK) stored in the database boot record, making it available during database recovery. The DEK is a symmetric key signed with a server certificate from the primary system database.

In many instances, security compliance laws require TDE for data at rest.

### Examples
<a name="chap-sql-server-aurora-mysql.security.transparentdataencryption.sqlserver.examples"></a>

The following example demonstrates how to enable TDE for a database.

Create a master key and certificate.

```
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword';
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
```

Create a database encryption key.

```
USE MyDatabase;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;
```

Enable TDE.

```
ALTER DATABASE MyDatabase SET ENCRYPTION ON;
```

For more information, see [Transparent data encryption (TDE)](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15) in the *SQL Server documentation*.

## MySQL Usage
<a name="chap-sql-server-aurora-mysql.security.transparentdataencryption.mysql"></a>

 Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) provides the ability to encrypt data at rest (data stored in persistent storage) for new database instances. When data encryption is enabled, Amazon Relational Database Service (RDS) automatically encrypts the database server storage, automated backups, read replicas, and snapshots using the AES-256 encryption algorithm.

You can manage the keys used for Amazon Relational Database Service (Amazon RDS) encrypted instances from the Identity and Access Management (IAM) console using the AWS Key Management Service (AWS KMS). If you require full control of a key, you must manage it yourself. You can’t delete, revoke, or rotate default keys provisioned by AWS KMS.

The following limitations exist for Amazon RDS encrypted instances:
+ You can only enable encryption for an Amazon RDS database instance when you create it, not afterward. It is possible to encrypt an existing database by creating a snapshot of the database instance and then creating an encrypted copy of the snapshot. You can restore the database from the encrypted snapshot. For more information, see [Copying a snapshot](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CopySnapshot.html).
+ Encrypted database instances can’t be modified to turn off encryption.
+ Encrypted Read Replicas must be encrypted with the same key as the source database instance.
+ An unencrypted backup or snapshot can’t be restored to an encrypted database instance.
+ KMS encryption keys are specific to the region where they are created. Copying an encrypted snapshot from one region to another requires the KMS key identifier of the destination region.

**Note**  
Disabling the key for an encrypted database instance prevents reading from, or writing to, that instance. When Amazon RDS encounters a database instance encrypted by a key to which Amazon RDS doesn’t have access, it puts the database instance into a terminal state. In this state, the database instance is no longer available and the current state of the database can’t be recovered. To restore the database instance, you must re-enable access to the encryption key for Amazon RDS and then restore the database instance from a backup.

Table encryption can now be managed globally by defining and enforcing encryption defaults. The `default_table_encryption` variable defines an encryption default for newly created schemas and general tablespace. The encryption default for a schema can also be defined using the `DEFAULT ENCRYPTION` clause when creating a schema. By default a table inherits the encryption of the schema or general tablespace it is created in. Encryption defaults are enforced by enabling the `table_encryption_privilege_check` variable. The privilege check occurs when creating or altering a schema or general tablespace with an encryption setting that differs from the `default_table_encryption` setting or when creating or altering a table with an encryption setting that differs from the default schema encryption. The `TABLE_ENCRYPTION_ADMIN` privilege permits overriding default encryption settings when `table_encryption_privilege_check` is enabled. For more information, see [Defining an Encryption Default for Schemas and General Tablespaces](https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html#innodb-schema-tablespace-encryption-default).

### Creating an Encryption Key
<a name="chap-sql-server-aurora-mysql.security.transparentdataencryption.mysql.examples"></a>

To create your own key, browse to the Key Management Service (KMS) and choose **Customer managed keys** and create a new key.

1. Choose relevant options and choose **Next**.

1. Define alias as the name of the key and choose **Next**.

1. You can skip **Define Key Administrative Permissions** and choose **Next**.

1. On the next step make sure to assign the key to the relevant users who will need to interact with Amazon Aurora.

1. On the last step you will be able to see the ARN of the key and its account.

1. Choose **Finish** and now this key will be listed in under customer managed keys.

Now you will be able to set Master encryption key by using the ARN of the key that you have created or picking it from the list.

Proceed to finish and launch the instance.

As part of the database settings, you will be prompted to enable encryption and select a master key.

Encryption for an Amazon RDS DB instance can be enabled only during the instance creation.

You can select the default key provided for the account or define a specific key based on an IAM KMS ARN from your account or a different account.

# Users and roles for Aurora MySQL
<a name="chap-sql-server-aurora-mysql.security.usersroles"></a>

This topic provides reference information comparing security features between Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can understand the key differences in user management, authentication methods, and access control between these two database systems.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Two star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-2.png)   |  N/A  |  N/A  |  No native role support in the database. Use AWS IAM accounts with the AWS Authentication Plugin.  | 

## SQL Server Usage
<a name="chap-sql-server-aurora-mysql.security.usersroles.sqlserver"></a>

SQL Server provides two layers of security principals: Logins at the server level and Users at the database level. Logins are mapped to users in one or more databases. Administrators can grant logins server-level permissions that aren’t mapped to particular databases such as Database Creator, System Administrator and Security Administrator.

SQL Server also supports Roles for both the server and the database levels. At the database level, administrators can create custom roles in addition to the general purpose built-in roles.

For each database, administrators can create users and associate them with logins. At the database level, the built-in roles include `db_owner`, `db_datareader`, `db_securityadmin`, and others. A database user can belong to one or more roles (users are assigned to the public role by default and can’t be removed). Administrators can grant permissions to roles and then assign individual users to the roles to simplify security management.

Logins are authenticated using either Windows Authentication, which uses the Windows Server Active Directory framework for integrated single sign-on, or SQL authentication, which is managed by the SQL Server service and requires a password, certificate, or asymmetric key for identification. Logins using windows authentication can be created for individual users and domain groups.

In previous versions of SQL server, the concepts of user and schema were interchangeable. For backward compatibility, each database has several existing schemas, including a default schema named dbo which is owned by the `db_owner` role. Logins with system administrator privileges are automatically mapped to the dbo user in each database. Typically, you don’t need to migrate these schemas.

### Examples
<a name="chap-sql-server-aurora-mysql.security.usersroles.sqlserver.examples"></a>

The following example creates a login.

```
CREATE LOGIN MyLogin WITH PASSWORD = 'MyPassword'
```

The following example creates a database user for `MyLogin`.

```
USE MyDatabase; CREATE USER MyUser FOR LOGIN MyLogin;
```

The following example assigns `MyLogin` to a server role.

```
ALTER SERVER ROLE dbcreator ADD MEMBER 'MyLogin'
```

The following example assigns `MyUser` to the `db_datareader` role.

```
ALTER ROLE db_datareader ADD MEMBER 'MyUser';
```

For more information, see [Database-level roles](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15) in the *SQL Server documentation*.

## MySQL Usage
<a name="chap-sql-server-aurora-mysql.security.usersroles.mysql"></a>

 Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) supports only Users; Roles aren’t supported. Database administrators must specify privileges for individual users. Aurora MySQL uses database user accounts to authenticate sessions and authorize access to specific database objects.

**Note**  
When granting privileges, you have the option to use wild-card characters for specifying multiple privileges for multiple objects. For more information, see [Data Control Language](chap-sql-server-aurora-mysql.security.datacontrollanguage.md).

When using Identity and Access Management (IAM) database authentication, roles are available as part of the IAM framework and can be used for authentication. This authentication method uses tokens in place of passwords. AWS Signature Version 4 generates authentication tokens with a lifetime of 15 minutes. You don’t need to store user credentials in the database because authentication is managed externally. You can use IAM in conjunction with standard database authentication.

**Note**  
In Aurora MySQL, a database is equivalent to an SQL Server schema.

The AWS Authentication Plugin works seamlessly with Aurora MySQL instances. Users logged in with AWS IAM accounts use access tokens to authenticate. This mechanism is similar to the SQL Server windows authentication option.

IAM database authentication provides the following benefits:
+ Supports roles for simplifying user and access management.
+ Provides a single sign on experience that is safer than using MySQL managed passwords.
+ Encrypts network traffic to and from the database using Secure Sockets Layer (SSL) protocol.
+ Provides centrally managed access to your database resources, alleviating the need to manage access individually for each database instance or database cluster.

**Note**  
IAM database authentication limits the number of new connections to 20 connections/second.

**Note**  
 Amazon Relational Database Service (Amazon RDS) for MySQL 8 supports roles which are named collections of privileges. Roles can be created and dropped. Roles can have privileges granted to and revoked from them. Roles can be granted to and revoked from user accounts. The active applicable roles for an account can be selected from among those granted to the account and can be changed during sessions for that account. For more information, see [Using Roles](https://dev.mysql.com/doc/refman/8.0/en/roles.html).

```
CREATE ROLE 'app_developer', 'app_read', 'app_write';
```

**Note**  
 Amazon RDS for MySQL 8 incorporates the concept of user account categories with system and regular users distinguished according to whether they have the `SYSTEM_USER` privilege. For more information, see [Account Categories](https://dev.mysql.com/doc/refman/8.0/en/account-categories.html).

```
CREATE USER u1 IDENTIFIED BY 'password';

GRANT ALL ON *.* TO u1 WITH GRANT OPTION;

-- GRANT ALL includes SYSTEM_USER, so at this point

-- u1 can manipulate system or regular accounts
```

### Syntax
<a name="chap-sql-server-aurora-mysql.security.usersroles.mysql.syntax"></a>

Simplified syntax for `CREATE USER` in Aurora MySQL:

```
CREATE USER <user> [<authentication options>] [REQUIRE {NONE | <TLS options>] }]
[WITH <resource options> ] [<Password options> | <Lock options>]
```

```
<Authentication option>:
{IDENTIFIED BY 'auth string'|PASSWORD 'hash string'|WITH auth plugin|auth plugin BY
'auth_string'|auth plugin AS 'hash string'}
<TLS options>: {SSL| X509| CIPHER 'cipher'| ISSUER 'issuer'| SUBJECT 'subject'}
<Resource options>: { MAX_QUERIES_PER_HOUR | MAX_UPDATES_PER_HOUR | MAX_CONNECTIONS_
PER_HOUR | MAX_USER_CONNECTIONS count}
<Password options>: {PASSWORD EXPIRE | DEFAULT | NEVER | INTERVAL N DAY}
<Lock options>: {ACCOUNT LOCK | ACCOUNT UNLOCK}
```

**Note**  
In Aurora MySQL, you can assign resource limitations to specific users, similar to SQL Server Resource Governor. For more information, see [Resource Governor](chap-sql-server-aurora-mysql.management.resourcegovernor.md).

### Examples
<a name="chap-sql-server-aurora-mysql.security.usersroles.mysql.examples"></a>

The following example creates a user, forces a password change, and imposes resource limits.

```
CREATE USER 'Dan'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'Dan''sPassword'
WITH MAX_QUERIES_PER_HOUR 500
PASSWORD EXPIRE;
```

The following example creates a user with IAM authentication.

```
CREATE USER LocalUser
IDENTIFIED WITH AWSAuthenticationPlugin AS 'IAMUser';
```

## Summary
<a name="chap-sql-server-aurora-mysql.security.usersroles.summary"></a>

The following table summarizes common security tasks and the differences between SQL Server and Aurora MySQL.


| Task | SQL Server |  Aurora MySQL  | 
| --- | --- | --- | 
|  View database users  |  <pre>SELECT Name FROM sys.sysusers</pre>  |  <pre>SELECT User FROM mysql.user</pre>  | 
|  Create a user and password  |  <pre>CREATE USER <User Name> WITH<br />PASSWORD = <PassWord>;</pre>  |  <pre>CREATE USER <User Name><br />IDENTIFIED BY <Password></pre>  | 
|  Create a role  |  <pre>CREATE ROLE <Role Name></pre>  |  Use AWS IAM Roles  | 
|  Change a user’s password  |  <pre>ALTER LOGIN <SQL Login> WITH<br />PASSWORD = <PassWord>;</pre>  |  <pre>ALTER USER <User Name><br />IDENTIFIED BY <Password></pre>  | 
|  External authentication  |  Windows Authentication  |   AWS IAM (Identity and Access Management)  | 
|  Add a user to a role  |  <pre>ALTER ROLE <Role Name> ADD MEMBER <User Name></pre>  |  Use AWS IAM Roles  | 
|  Lock a user  |  <pre>ALTER LOGIN <Login Name><br />DISABLE</pre>  |  <pre>ALTER User <User Name><br />ACCOUNT LOCK</pre>  | 
|  Grant SELECT on a schema  |  <pre>GRANT SELECT ON SCHEMA::<Schema Name> to <User Name></pre>  |  <pre>GRANT SELECT ON <Schema Name>.* TO <User Name></pre>  | 

For more information, see [What is IAM](https://docs.aws.amazon.com/IAM/latest/UserGuide/introduction.html) and [IAM Identities (users, user groups, and roles)](https://docs.aws.amazon.com/IAM/latest/UserGuide/id.html).

# Encrypted connections for Aurora MySQL
<a name="chap-sql-server-aurora-mysql.security.encryptedconnections"></a>

This topic provides reference information about encrypted connections in Microsoft SQL Server and MySQL, with a focus on how these concepts apply to Amazon Aurora MySQL migration. You’ll learn about the protocols and technologies used for secure data transmission in both database systems.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-compatibility-3.png)   |   ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/images/pb-automation-0.png)   |  N/A  |  N/A  | 

## SQL Server Usage
<a name="chap-sql-server-aurora-mysql.security.encryptedconnections.sqlserver"></a>

In SQL Server, you can encrypt data across communication channels. Encrypted connections are enabled for an instance of the SQL Server Database Engine and use SQL Server Configuration Manager to specify a certificate.

Make sure that the server has a certificate provisioned. To provision the certificate on the server, make sure to import it into Windows. The client machine must be set up to trust the certificate’s root authority.

**Note**  
Starting with SQL Server 2016 (13.x), Secure Sockets Layer (SSL) has been discontinued. Use Transport Layer Security (TLS) instead.

## MySQL Usage
<a name="chap-sql-server-aurora-mysql.security.encryptedconnections.mysql"></a>

MySQL supports encrypted connections between clients and the server using the TLS (Transport Layer Security) protocol. TLS is sometimes referred to as SSL (Secure Sockets Layer) but MySQL doesn’t actually use the SSL protocol for encrypted connections because its encryption is weak.

OpenSSL 1.1.1 supports the TLS v1.3 protocol for encrypted connections.

**Note**  
 Amazon Relational Database Service (Amazon RDS) for MySQL 8.0.16 and higher supports TLS v1.3 as well if both the server and client are compiled using OpenSSL 1.1.1 or higher. For more information, see [Encrypted Connection TLS Protocols and Ciphers](https://dev.mysql.com/doc/refman/5.7/en/encrypted-connection-protocols-ciphers.html) in the *MySQL documentation*.