SHOW DATABASES - Amazon Redshift
SyntaxParametersExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

SHOW DATABASES

Displays databases from a specified account ID.

Syntax

SHOW DATABASES FROM
DATA CATALOG [ ACCOUNT  '<id1>', '<id2>', ... ]
[ LIKE '<expression>' ]
[ IAM_ROLE default | 'SESSION' | 'arn:aws:iam::<account-id>:role/<role-name>' ]

Parameters

ACCOUNT '<id1>', '<id2>', ...

The Amazon Glue Data Catalog accounts from which to list databases. Omitting this parameter indicates that Amazon Redshift should show the databases from the account that owns the cluster.

LIKE '<expression>'

Filters the list of databases to those that match the expression that you specify. This parameter supports patterns that use the wildcard characters % (percent) and _ (underscore).

IAM_ROLE default | 'SESSION' | 'arn:aws:iam::<account-id>:role/<role-name>'

If you specify an IAM role that is associated with the cluster when running the SHOW DATABASES command, Amazon Redshift will use the role’s credentials when you run queries on the database.

Specifying the default keyword means to use the IAM role that's set as the default and associated with the cluster.

Use 'SESSION' if you connect to your Amazon Redshift cluster using a federated identity and access the tables from the external database created using the CREATE DATABASE command. For an example of using a federated identity, see Using a federated identity to manage Amazon Redshift access to local resources and Amazon Redshift Spectrum external tables, which explains how to configure federated identity.

Use the Amazon Resource Name (ARN) for an IAM role that your cluster uses for authentication and authorization. As a minimum, the IAM role must have permission to perform a LIST operation on the Amazon S3 bucket to be accessed and a GET operation on the Amazon S3 objects the bucket contains. To learn more about databases created from the Amazon Glue Data Catalog for datashares and using IAM_ROLE, see Working with Lake Formation-managed datashares as a consumer.

The following shows the syntax for the IAM_ROLE parameter string for a single ARN.

IAM_ROLE 'arn:aws:iam::<aws-account-id>:role/<role-name>'

You can chain roles so that your cluster can assume another IAM role, possibly belonging to another account. You can chain up to 10 roles. For more information, see Chaining IAM roles in Amazon Redshift Spectrum.

To this IAM role, attach an IAM permissions policy similar to the following.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AccessSecret",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetResourcePolicy",
                "secretsmanager:GetSecretValue",
                "secretsmanager:DescribeSecret",
                "secretsmanager:ListSecretVersionIds"
            ],
            "Resource": "arn:aws:secretsmanager:us-west-2:123456789012:secret:my-rds-secret-VNenFy"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetRandomPassword",
                "secretsmanager:ListSecrets"
            ],
            "Resource": "*"
        }
    ]
}

For the steps to create an IAM role to use with federated query, see Creating a secret and an IAM role to use federated queries.

Note

Don't include spaces in the list of chained roles.

The following shows the syntax for chaining three roles.

IAM_ROLE 'arn:aws:iam::<aws-account-id>:role/<role-1-name>,arn:aws:iam::<aws-account-id>:role/<role-2-name>,arn:aws:iam::<aws-account-id>:role/<role-3-name>'

Examples

The following example displays all of the Data Catalog databases from the account ID 123456789012.

SHOW DATABASES FROM DATA CATALOG ACCOUNT '123456789012'

  catalog_id  | database_name |                        database_arn                    |     type     |                                             target_database                                      | location | parameters
--------------+---------------+--------------------------------------------------------+--------------+--------------------------------------------------------------------------------------------------+----------+------------
 123456789012 |   database1   | arn:aws:glue:us-east-1:123456789012:database/database1 | Data Catalog |                                                                                                  |          |
 123456789012 |   database2   | arn:aws:glue:us-east-1:123456789012:database/database2 | Data Catalog | arn:aws:redshift:us-east-1:123456789012:datashare:035c45ea-61ce-86f0-8b75-19ac6102c3b7/database2 |          |

The following are examples that demonstrate how to display all of the Data Catalog databases from the account ID 123456789012 while using an IAM role's credentials.

SHOW DATABASES FROM DATA CATALOG ACCOUNT '123456789012' IAM_ROLE default;
         
SHOW DATABASES FROM DATA CATALOG ACCOUNT '123456789012' IAM_ROLE <iam-role-arn>;