# Creating and setting target data providers in DMS Schema Conversion
<a name="data-providers-target"></a>

You can use MySQL, PostgreSQL, Amazon Redshift and Amazon RDS for Db2 databases as a target data provider in migration projects for DMS Schema Conversion.

**Topics**
+ [Using a MySQL database as a target in DMS Schema Conversion](data-providers-mysql.md)
+ [Using a PostgreSQL database as a target in DMS Schema Conversion](data-providers-postgresql.md)
+ [Using an Amazon Redshift cluster as a target in DMS Schema Conversion](data-providers-redshift.md)
+ [Using Amazon RDS for Db2 database as a target in DMS Schema Conversion](sc-data-providers-rds.md)

# Using a MySQL database as a target in DMS Schema Conversion
<a name="data-providers-mysql"></a>

You can use MySQL databases as a migration target in DMS Schema Conversion.

For information about supported target databases, see [Target data providers for DMS Schema Conversion](CHAP_Introduction.Targets.md#CHAP_Introduction.Targets.SchemaConversion).

## Privileges for MySQL as a target
<a name="data-providers-mysql-permissions"></a>

The following privileges are required for MySQL as a target:
+ `CREATE ON *.*`
+ `ALTER ON *.*`
+ `DROP ON *.*`
+ `INDEX ON *.*`
+ `REFERENCES ON *.*`
+ `SELECT ON *.*`
+ `CREATE VIEW ON *.*`
+ `SHOW VIEW ON *.*`
+ `TRIGGER ON *.*`
+ `CREATE ROUTINE ON *.*`
+ `ALTER ROUTINE ON *.*`
+ `EXECUTE ON *.*`
+ `CREATE TEMPORARY TABLES ON *.*`
+ `AWS_LAMBDA_ACCESS (Only valid for Aurora MySQL)`
+ `INSERT, UPDATE ON AWS_ORACLE_EXT.*`
+ `INSERT, UPDATE, DELETE ON AWS_ORACLE_EXT_DATA.*`
+ `INSERT, UPDATE ON AWS_SQLSERVER_EXT.*`
+ `INSERT, UPDATE, DELETE ON AWS_SQLSERVER_EXT_DATA.*`
+ `CREATE TEMPORARY TABLES ON AWS_SQLSERVER_EXT_DATA.*`

You can use the following code example to create a database user and grant the privileges.

```
CREATE USER 'user_name' IDENTIFIED BY 'your_password';
GRANT CREATE ON *.* TO 'user_name';
GRANT ALTER ON *.* TO 'user_name';
GRANT DROP ON *.* TO 'user_name';
GRANT INDEX ON *.* TO 'user_name';
GRANT REFERENCES ON *.* TO 'user_name';
GRANT SELECT ON *.* TO 'user_name';
GRANT CREATE VIEW ON *.* TO 'user_name';
GRANT SHOW VIEW ON *.* TO 'user_name';
GRANT TRIGGER ON *.* TO 'user_name';
GRANT CREATE ROUTINE ON *.* TO 'user_name';
GRANT ALTER ROUTINE ON *.* TO 'user_name';
GRANT EXECUTE ON *.* TO 'user_name';
GRANT CREATE TEMPORARY TABLES ON *.* TO 'user_name';
GRANT AWS_LAMBDA_ACCESS TO 'user_name'; (Only valid for Aurora MySQL)
GRANT INSERT, UPDATE ON AWS_ORACLE_EXT.* TO 'user_name';
GRANT INSERT, UPDATE, DELETE ON AWS_ORACLE_EXT_DATA.* TO 'user_name';
GRANT INSERT, UPDATE ON AWS_SQLSERVER_EXT.* TO 'user_name';
GRANT INSERT, UPDATE, DELETE ON AWS_SQLSERVER_EXT_DATA.* TO 'user_name';
GRANT CREATE TEMPORARY TABLES ON AWS_SQLSERVER_EXT_DATA.* TO 'user_name';
```

In the preceding example, replace *user\$1name* with the name of your user. Then, replace *your\$1password* with a secure password.

To use Amazon RDS for MySQL or Aurora MySQL as a target, set the `lower_case_table_names` parameter to `1`. This value means that the MySQL server handles identifiers of such object names as tables, indexes, triggers, and databases as case insensitive. If you have turned on binary logging in your target instance, then set the `log_bin_trust_function_creators` parameter to `1`. In this case, you don't need to use the `DETERMINISTIC`, `READS SQL DATA` or `NO SQL` characteristics to create stored functions. To configure these parameters, create a new DB parameter group or modify an existing DB parameter group.

# Using a PostgreSQL database as a target in DMS Schema Conversion
<a name="data-providers-postgresql"></a>

You can use PostgreSQL databases as a migration target in DMS Schema Conversion.

For information about supported target databases, see [Target data providers for DMS Schema Conversion](CHAP_Introduction.Targets.md#CHAP_Introduction.Targets.SchemaConversion).

## Privileges for PostgreSQL as a target
<a name="data-providers-postgresql-permissions"></a>

To use PostgreSQL as a target, DMS Schema Conversion requires the following minimum privileges. The target database must exist before you connect. For each database you want to use in your migration project, grant the following privileges to the user account used for the migration:

```
GRANT CONNECT ON DATABASE <db_name> TO <user>;
GRANT CREATE ON DATABASE <db_name> TO <user>;
```

To use Amazon RDS for PostgreSQL as a target, DMS Schema Conversion requires the `rds_superuser` role.

To use the converted public synonyms, change the database default search path using the following command.

```
ALTER DATABASE <db_name> SET SEARCH_PATH = "$user", public_synonyms, public;
```

In this example, replace the `<db_name>` placeholder with the name of your database.

In PostgreSQL, only the schema owner or a `superuser` can drop a schema. The owner can drop a schema and all objects that this schema includes, even if the owner of the schema doesn't own some of its objects.

When you use different users to convert and apply different schemas to your target database, you may encounter an error message when DMS Schema Conversion can't drop a schema. To avoid this error message, use the `superuser` role.

# Using an Amazon Redshift cluster as a target in DMS Schema Conversion
<a name="data-providers-redshift"></a>

You can use Amazon Redshift databases as a migration target in DMS Schema Conversion. For information about supported target databases, see [Target data providers for DMS Schema Conversion](CHAP_Introduction.Targets.md#CHAP_Introduction.Targets.SchemaConversion). 

## Privileges for Amazon Redshift as a target
<a name="data-providers-redshift-privileges"></a>

Using Amazon Redshift as a target for DMS Schema Conversion requires the following privileges:
+ **CREATE ON DATABASE**: Allows DMS to create new schemas in the database.
+ **CREATE ON SCHEMA**: Allows DMS to create objects in the database schema.
+ **GRANT USAGE ON LANGUAGE**: Allows DMS to create new functions and procedures in the database.
+ **GRANT SELECT ON ALL TABLES IN SCHEMA pg\$1catalog**: Provides the user system information about the Amazon Redshift cluster.
+ **GRANT SELECT ON pg\$1class\$1info**: Provides the user information about the table distribution style.

You can use the following code example to create a database user and grant it permissions. Replace the example values with your values.

```
CREATE USER user_name PASSWORD your_password;
GRANT CREATE ON DATABASE db_name TO user_name;
GRANT CREATE ON SCHEMA schema_name TO user_name;
GRANT USAGE ON LANGUAGE plpythonu TO user_name;
GRANT USAGE ON LANGUAGE plpgsql TO user_name;
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO user_name;
GRANT SELECT ON pg_class_info TO user_name;
GRANT SELECT ON sys_serverless_usage TO user_name;
GRANT SELECT ON pg_database_info TO user_name;
GRANT SELECT ON pg_statistic TO user_name;
```

Repeat the `GRANT CREATE ON SCHEMA` operation for each target schema where you will apply the converted code or migrate data.

You can apply an extension pack on your target Amazon Redshift database. An extension pack is an add-on module that emulates source database functions that are required when converting objects to Amazon Redshift. For more information, see [Using extension packs in DMS Schema Conversion](extension-pack.md).

# Using Amazon RDS for Db2 database as a target in DMS Schema Conversion
<a name="sc-data-providers-rds"></a>

You can use Amazon RDS for Db2 databases as a migration target in DMS Schema Conversion.

For more information regarding supported target databases, see [Target data providers for DMS Schema Conversion](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.Targets.html#CHAP_Introduction.Targets.SchemaConversion).

## Privileges for Amazon RDS for Db2 as a target
<a name="sc-data-providers-rds-privileges"></a>

To use Amazon RDS for Db2 as a target, DMS Schema Conversion requires the `master_user_role` role. For more information, see [Amazon RDS for Db2 default roles](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/db2-default-roles.html) in the *Amazon Relational Database Service User Guide*.