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:

Privilege Standard Edition 1 Standard Edition Enterprise Edition

SELECT_CATALOG_ROLE

Required Required Required

RESOURCE

Required Required Required

CONNECT

Required Required Required

alter user <username> quota unlimited on USERS;

Required Required Required

alter user <username> quota unlimited on IDX;

Required Required Required

alter user <username> quota unlimited on ARCH;

Required Required Required

alter user <username> quota unlimited on ARCH_IDX;

Required Required Required

DROP ANY CUBE BUILD PROCESS

Not required Not required Required

ALTER ANY CUBE

Not required Not required Required

CREATE ANY CUBE DIMENSION

Not required Not required Required

CREATE ANY ASSEMBLY

Required Required Required

ALTER ANY RULE

Required Required Required

SELECT ANY DICTIONARY

Required Required Required

ALTER ANY DIMENSION

Required Required Required

CREATE ANY DIMENSION

Required Required Required

ALTER ANY TYPE

Required Required Required

DROP ANY TRIGGER

Required Required Required

CREATE ANY VIEW

Required Required Required

ALTER ANY CUBE BUILD PROCESS

Not required Not required Required

CREATE ANY CREDENTIAL

Not required Not required Required

DROP ANY CUBE DIMENSION

Not required Not required Required

DROP ANY ASSEMBLY

Required Required Required

DROP ANY PROCEDURE

Required Required Required

ALTER ANY PROCEDURE

Required Required Required

ALTER ANY SQL TRANSLATION PROFILE

Required Required Required

DROP ANY MEASURE FOLDER

Not required Not required Required

CREATE ANY MEASURE FOLDER

Not required Not required Required

DROP ANY CUBE

Not required Not required Required

DROP ANY MINING MODEL

Not required Not required Required

CREATE ANY MINING MODEL

Not required Not required Required

DROP ANY EDITION

Required Required Required

CREATE ANY EVALUATION CONTEXT

Required Required Required

DROP ANY DIMENSION

Required Required Required

ALTER ANY INDEXTYPE

Required Required Required

DROP ANY TYPE

Required Required Required

CREATE ANY PROCEDURE

Required Required Required

CREATE ANY SQL TRANSLATION PROFILE

Required Required Required

CREATE ANY CUBE

Not required Not required Required

COMMENT ANY MINING MODEL

Not required Not required Required

ALTER ANY MINING MODEL

Not required Not required Required

DROP ANY SQL PROFILE

Required Required Required

CREATE ANY JOB

Required Required Required

DROP ANY EVALUATION CONTEXT

Required Required Required

ALTER ANY EVALUATION CONTEXT

Required Required Required

CREATE ANY INDEXTYPE

Required Required Required

CREATE ANY OPERATOR

Required Required Required

CREATE ANY TRIGGER

Required Required Required

DROP ANY ROLE

Required Required Required

DROP ANY SEQUENCE

Required Required Required

DROP ANY CLUSTER

Required Required Required

DROP ANY SQL TRANSLATION PROFILE

Required Required Required

ALTER ANY ASSEMBLY

Required Required Required

CREATE ANY RULE SET

Required Required Required

ALTER ANY OUTLINE

Required Required Required

UNDER ANY TYPE

Required Required Required

CREATE ANY TYPE

Required Required Required

DROP ANY MATERIALIZED VIEW

Required Required Required

ALTER ANY ROLE

Required Required Required

DROP ANY VIEW

Required Required Required

ALTER ANY INDEX

Required Required Required

COMMENT ANY TABLE

Required Required Required

CREATE ANY TABLE

Required Required Required

CREATE USER

Required Required Required

DROP ANY RULE SET

Required Required Required

CREATE ANY CONTEXT

Required Required Required

DROP ANY INDEXTYPE

Required Required Required

ALTER ANY OPERATOR

Required Required Required

CREATE ANY MATERIALIZED VIEW

Required Required Required

ALTER ANY SEQUENCE

Required Required Required

DROP ANY SYNONYM

Required Required Required

CREATE ANY SYNONYM

Required Required Required

DROP USER

Required Required Required

ALTER ANY MEASURE FOLDER

Required Required Required

ALTER ANY EDITION

Required Required Required

DROP ANY RULE

Required Required Required

CREATE ANY RULE

Required Required Required

ALTER ANY RULE SET

Required Required Required

CREATE ANY OUTLINE

Required Required Required

UNDER ANY TABLE

Required Required Required

UNDER ANY VIEW

Required Required Required

DROP ANY DIRECTORY

Required Required Required

ALTER ANY CLUSTER

Required Required Required

CREATE ANY CLUSTER

Required Required Required

ALTER ANY TABLE

Required Required Required

CREATE ANY CUBE BUILD PROCESS

Not required Not required Required

ALTER ANY CUBE DIMENSION

Not required Not required Required

CREATE ANY EDITION

Required Required Required

CREATE ANY SQL PROFILE

Required Required Required

ALTER ANY SQL PROFILE

Required Required Required

DROP ANY OUTLINE

Required Required Required

DROP ANY CONTEXT

Required Required Required

DROP ANY OPERATOR

Required Required Required

DROP ANY LIBRARY

Required Required Required

ALTER ANY LIBRARY

Required Required Required

CREATE ANY LIBRARY

Required Required Required

ALTER ANY MATERIALIZED VIEW

Required Required Required

ALTER ANY TRIGGER

Required Required Required

CREATE ANY SEQUENCE

Required Required Required

DROP ANY INDEX

Required Required Required

CREATE ANY INDEX

Required Required Required

DROP ANY TABLE

Required Required Required

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