Menu
AWS Schema Conversion Tool
User Guide (Version 1.0)

Required Database Privileges for Using the AWS Schema Conversion Tool

When you use the AWS Schema Conversion Tool (AWS SCT) to convert your database schema, you need certain privileges for the source and target databases. Following, you can find lists of these privileges.

Privileges for Amazon Redshift as a Source Database

The privileges required for Amazon Redshift as a source are listed following:

  • USAGE ON SCHEMA <schema_name>

  • SELECT ON ALL TABLES IN SCHEMA <schema_name>

  • SELECT ON PG_CATALOG.PG_STATISTIC

  • SELECT ON SVV_TABLE_INFO

  • SELECT ON TABLE STV_BLOCKLIST

  • SELECT ON TABLE STV_TBL_PERM

Privileges for Greenplum as a Source Database

The privileges required for Greenplum as a source are listed following:

  • CONNECT ON DATABASE <database_name>

  • USAGE ON SCHEMA <schema_name>

Privileges for Microsoft SQL Server as a Source Database

The privileges required for Microsoft SQL Server as a source are listed following:

  • VIEW DEFINITION

  • VIEW DATABASE STATE

Repeat the grant for each database whose schema you are converting.

Privileges for Microsoft SQL Server Data Warehouse as a Source Database

The privileges required for Microsoft SQL Server data warehouse as a source are listed following:

  • VIEW DEFINITION

  • VIEW DATABASE STATE

  • SELECT ON SCHEMA :: <schema_name>

Repeat the grant for each database whose schema you are converting.

In addition, grant the following, and run the grant on the master database:

  • VIEW SERVER STATE

Privileges for MySQL as a Source Database

The privileges required for MySQL as a source are listed following:

  • SELECT ON *.*

  • SELECT ON mysql.proc

  • SHOW VIEW ON *.*

Privileges for Netezza as a Source Database

The privileges required for Netezza as a source are listed following:

  • SELECT ON SYSTEM.DEFINITION_SCHEMA.SYSTEM VIEW

  • SELECT ON SYSTEM.DEFINITION_SCHEMA.SYSTEM TABLE

  • SELECT ON SYSTEM.DEFINITION_SCHEMA.MANAGEMENT TABLE

  • LIST ON <database_name>

  • LIST ON <database_name>.ALL.TABLE

  • LIST ON <database_name>.ALL.EXTERNAL TABLE

  • LIST ON <database_name>.ALL.VIEW

  • LIST ON <database_name>.ALL.MATERIALIZED VIEW

  • LIST ON <database_name>.ALL.PROCEDURE

  • LIST ON <database_name>.ALL.SEQUENCE

  • LIST ON <database_name>.ALL.FUNCTION

  • LIST ON <database_name>.ALL.AGGREGATE

Privileges for Oracle as a Source Database

The privileges required for Oracle as a source are listed following:

  • connect

  • select_catalog_role

  • select any dictionary

Privileges for Oracle Data Warehouse as a Source Database

The privileges required for Oracle Data Warehouse as a source are listed following:

  • connect

  • select_catalog_role

  • select any dictionary

Privileges for PostgreSQL as a Source Database

The privileges required for PostgreSQL as a source are listed following:

  • CONNECT ON DATABASE <database_name>

  • USAGE ON SCHEMA <database_name>

  • SELECT ON ALL TABLES IN SCHEMA <database_name>

  • SELECT ON ALL SEQUENCES IN SCHEMA <database_name>

Privileges for Teradata as a Source Database

The privileges required for Teradata as a source are listed following:

  • SELECT ON DBC

Privileges for Vertica as a Source Database

The privileges required for Vertica as a source are listed following:

  • USAGE ON SCHEMA <schema_name>

  • USAGE ON SCHEMA PUBLIC

  • GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name>

  • SELECT ON ALL SEQUENCES IN SCHEMA <schema_name>

  • EXECUTE ON ALL FUNCTIONS IN SCHEMA <schema_name>

  • EXECUTE ON PROCEDURE <schema_name.procedure_name(procedure_signature)>

Privileges for Amazon Aurora (MySQL) as a Target Database

The privileges required for Amazon Aurora (MySQL) as a target are listed following:

  • 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 *.*

  • SELECT ON mysql.proc

If your source database is Microsoft SQL Server, grant the additional privilege INSERT,UPDATE ON AWS_SQLSERVER_EXT.*

If your source database is Oracle, grant the additional privilege INSERT,UPDATE ON AWS_ORACLE_EXT.*

If your source database is PostgreSQL, grant the additional privilege INSERT,UPDATE ON AWS_POSTGRESQL_EXT.*

Privileges for Amazon Aurora (PostgreSQL) as a Target Database

The privileges required for Amazon Aurora (PostgreSQL) as a target are explained following.

If the new schema doesn't exist yet, grant the privilege CREATE ON DATABASE <database_name>

If the new schema already exists, grant the privilege INSERT ON ALL TABLES IN SCHEMA <schema_name>

In PostgreSQL, only the owner of a schema or a superuser can drop a schema. The owner can drop the schema, and all contained objects, even if the owner doesn't own all of the contained objects.

Privileges for Amazon Redshift as a Target Database

The privileges required for Amazon Redshift as a target are listed following:

  • CREATE ON DATABASE <database_name>

  • USAGE ON LANGUAGE plpythonu

  • SELECT ON ALL TABLES IN SCHEMA pg_catalog

In Amazon Redshift, only the owner of a schema or a superuser can drop a schema. The owner can drop the schema, and all contained objects, even if the owner doesn't own all of the contained objects.

Privileges for Microsoft SQL Server as a Target Database

The privileges required for Microsoft SQL Server as a target are listed following:

  • CREATE SCHEMA

  • CREATE TABLE

  • CREATE VIEW

  • CREATE TYPE

  • CREATE DEFAULT

  • CREATE FUNCTION

  • CREATE PROCEDURE

  • CREATE ASSEMBLY

  • CREATE AGGREGATE

  • CREATE FULLTEXT CATALOG

Privileges for MySQL as a Target Database

The privileges required for MySQL as a target are listed following:

  • 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 *.*

  • SELECT ON mysql.proc

If your source database is Microsoft SQL Server, grant the additional privilege INSERT,UPDATE ON AWS_SQLSERVER_EXT.*

If your source database is Oracle, grant the additional privilege INSERT,UPDATE ON AWS_ORACLE_EXT.*

If your source database is PostgreSQL, grant the additional privilege INSERT,UPDATE ON AWS_POSTGRESQL_EXT.*

Privileges for Oracle as a Target Database

The privileges required for Oracle as a target are listed following:

  • SELECT_CATALOG_ROLE

  • RESOURCE

  • CONNECT

  • alter user <username> quota unlimited on USERS;

  • alter user <username> quota unlimited on IDX;

  • alter user <username> quota unlimited on ARCH;

  • alter user <username> quota unlimited on ARCH_IDX;

  • DROP ANY CUBE BUILD PROCESS

  • ALTER ANY CUBE

  • CREATE ANY CUBE DIMENSION

  • CREATE ANY ASSEMBLY

  • ALTER ANY RULE

  • SELECT ANY DICTIONARY

  • ALTER ANY DIMENSION

  • CREATE ANY DIMENSION

  • ALTER ANY TYPE

  • DROP ANY TRIGGER

  • CREATE ANY VIEW

  • ALTER ANY CUBE BUILD PROCESS

  • CREATE ANY CREDENTIAL

  • DROP ANY CUBE DIMENSION

  • DROP ANY ASSEMBLY

  • DROP ANY PROCEDURE

  • ALTER ANY PROCEDURE

  • ALTER ANY SQL TRANSLATION PROFILE

  • DROP ANY MEASURE FOLDER

  • CREATE ANY MEASURE FOLDER

  • DROP ANY CUBE

  • DROP ANY MINING MODEL

  • CREATE ANY MINING MODEL

  • DROP ANY EDITION

  • CREATE ANY EVALUATION CONTEXT

  • DROP ANY DIMENSION

  • ALTER ANY INDEXTYPE

  • DROP ANY TYPE

  • CREATE ANY PROCEDURE

  • CREATE ANY SQL TRANSLATION PROFILE

  • CREATE ANY CUBE

  • COMMENT ANY MINING MODEL

  • ALTER ANY MINING MODEL

  • DROP ANY SQL PROFILE

  • CREATE ANY JOB

  • DROP ANY EVALUATION CONTEXT

  • ALTER ANY EVALUATION CONTEXT

  • CREATE ANY INDEXTYPE

  • CREATE ANY OPERATOR

  • CREATE ANY TRIGGER

  • DROP ANY ROLE

  • DROP ANY SEQUENCE

  • DROP ANY CLUSTER

  • DROP ANY SQL TRANSLATION PROFILE

  • ALTER ANY ASSEMBLY

  • CREATE ANY RULE SET

  • ALTER ANY OUTLINE

  • UNDER ANY TYPE

  • CREATE ANY TYPE

  • DROP ANY MATERIALIZED VIEW

  • ALTER ANY ROLE

  • DROP ANY VIEW

  • ALTER ANY INDEX

  • COMMENT ANY TABLE

  • CREATE ANY TABLE

  • CREATE USER

  • DROP ANY RULE SET

  • CREATE ANY CONTEXT

  • DROP ANY INDEXTYPE

  • ALTER ANY OPERATOR

  • CREATE ANY MATERIALIZED VIEW

  • ALTER ANY SEQUENCE

  • DROP ANY SYNONYM

  • CREATE ANY SYNONYM

  • DROP USER

  • ALTER ANY MEASURE FOLDER

  • ALTER ANY EDITION

  • DROP ANY RULE

  • CREATE ANY RULE

  • ALTER ANY RULE SET

  • CREATE ANY OUTLINE

  • UNDER ANY TABLE

  • UNDER ANY VIEW

  • DROP ANY DIRECTORY

  • ALTER ANY CLUSTER

  • CREATE ANY CLUSTER

  • ALTER ANY TABLE

  • CREATE ANY CUBE BUILD PROCESS

  • ALTER ANY CUBE DIMENSION

  • CREATE ANY EDITION

  • CREATE ANY SQL PROFILE

  • ALTER ANY SQL PROFILE

  • DROP ANY OUTLINE

  • DROP ANY CONTEXT

  • DROP ANY OPERATOR

  • DROP ANY LIBRARY

  • ALTER ANY LIBRARY

  • CREATE ANY LIBRARY

  • ALTER ANY MATERIALIZED VIEW

  • ALTER ANY TRIGGER

  • CREATE ANY SEQUENCE

  • DROP ANY INDEX

  • CREATE ANY INDEX

  • DROP ANY TABLE

Privileges for PostgreSQL as a Target Database

The privileges required for PostgreSQL as a target are explained following.

If the new schema doesn't exist yet, grant the privilege CREATE ON DATABASE <database_name>

If the new schema already exists, grant the privilege INSERT ON ALL TABLES IN SCHEMA <schema_name>

In PostgreSQL, only the owner of a schema or a superuser can drop a schema. The owner can drop the schema, and all contained objects, even if the owner doesn't own all of the contained objects.

Related Topics