AWS AmazonRDS documentation change
Summary
Added detailed documentation about case sensitivity considerations when creating Zero-ETL integrations between Oracle and Redshift, including examples of uppercase, lowercase, and mixed-case configurations with query implications
Security assessment
The changes address case sensitivity in object naming conventions between Oracle and Redshift, which is a configuration guidance rather than a security fix. There's no mention of vulnerabilities, exploits, or access control issues. The documentation helps prevent misconfigurations that could lead to data visibility errors but doesn't directly address security mechanisms.
Diff
diff --git a/AmazonRDS/latest/UserGuide/zero-etl.filtering.md b/AmazonRDS/latest/UserGuide/zero-etl.filtering.md index 1674ba8ac..552f826e9 100644 --- a//AmazonRDS/latest/UserGuide/zero-etl.filtering.md +++ b//AmazonRDS/latest/UserGuide/zero-etl.filtering.md @@ -225,0 +226,115 @@ The following examples demonstrate how data filtering works for RDS for Oracle z +### Case sensitivity considerations + +Oracle Database and Amazon Redshift handle object name casing differently, which affects both data filter configuration and target queries. Note the following: + + * Oracle Database stores database, schema, and object names in uppercase unless explicitly quoted in the `CREATE` statement. For example, if you create `mytable` (no quotes), the Oracle data dictionary stores the table name as `MYTABLE`. If you quote the object name, the data dictionary preserves the case. + + * Zero-ETL data filters are case sensitive and must match the exact case of object names as they appear in the Oracle data dictionary. + + * Amazon Redshift queries default to lowercase object names unless explicitly quoted. For example, a query of `MYTABLE` (no quotes) searches for `mytable`. + + + + +Be mindful of the case differences when you create the Amazon Redshift filter and query the data. + +#### Creating an uppercase integration + +When you create a table without specifying the name in double quotes, the Oracle database stores the name in uppercase in the data dictionary. For example, you can create `MYTABLE` using any of the following SQL statements. + + + CREATE TABLE REINVENT.MYTABLE (id NUMBER PRIMARY KEY, description VARCHAR2(100)); + CREATE TABLE reinvent.mytable (id NUMBER PRIMARY KEY, description VARCHAR2(100)); + CREATE TABLE REinvent.MyTable (id NUMBER PRIMARY KEY, description VARCHAR2(100)); + CREATE TABLE reINVENT.MYtabLE (id NUMBER PRIMARY KEY, description VARCHAR2(100)); + +Because you didn't quote the table name in the preceding statements, the Oracle database stores the object name in uppercase as `MYTABLE`. + +To replicate this table to Amazon Redshift, you must specify the uppercase name in your data filter of your `create-integration` command. The Zero-ETL filter name and Oracle data dictionary name must match. + + + aws rds create-integration \ + --integration-name upperIntegration \ + --data-filter "include: ORCL.REINVENT.MYTABLE" \ + ... + +By default, Amazon Redshift stores data in lowercase. To query `MYTABLE` in the replicated database in Amazon Redshift, you must quote the uppercase name `MYTABLE` so that it matches the case in the Oracle data dictionary. + + + SELECT * FROM targetdb1."REINVENT"."MYTABLE"; + +The following queries don't use the quoting mechanism. They all return an error because they search for an Amazon Redshift table named `mytable`, which uses the default lowercase name, but the table is named `MYTABLE` in the Oracle data dictionary. + + + SELECT * FROM targetdb1."REINVENT".MYTABLE; + SELECT * FROM targetdb1."REINVENT".MyTable; + SELECT * FROM targetdb1."REINVENT".mytable; + +The following queries uses the quoting mechanism to specify a mixed case name. The queries all return an error because they search for an Amazon Redshift table that isn't named `MYTABLE`. + + + SELECT * FROM targetdb1."REINVENT"."MYtablE"; + SELECT * FROM targetdb1."REINVENT"."MyTable"; + SELECT * FROM targetdb1."REINVENT"."mytable"; + +#### Creating a lowercase integration + +In the following alternative example, you use double quotes to store the table name in lowercase in the Oracle data dictionary. You create `mytable` as follows. + + + CREATE TABLE REINVENT."mytable" (id NUMBER PRIMARY KEY, description VARCHAR2(100)); + +The Oracle database stores the table name as `mytable` in lowercase. To replicate this table to Amazon Redshift, you must specify the lowercase name `mytable` in your Zero-ETL data filter. + + + aws rds create-integration \ + --integration-name lowerIntegration \ + --data-filter "include: ORCL.REINVENT.mytable" \ + ... + +When you query this table in the replicated database in Amazon Redshift, you can specify the lowercase name `mytable`. The query succeeds because it searches for a table named `mytable`, which is the table name in the Oracle data dictionary. + + + SELECT * FROM targetdb1."REINVENT".mytable; + +Because Amazon Redshift defaults to lowercase object names, the following queries also succeed in finding `mytable`. + + + SELECT * FROM targetdb1."REINVENT".MYtablE; + SELECT * FROM targetdb1."REINVENT".MYTABLE; + SELECT * FROM targetdb1."REINVENT".MyTable; + +The following queries use the quoting mechanism for the object name. They all return an error because they search for an Amazon Redshift table whose name is different from `mytable`. + + + SELECT * FROM targetdb1."REINVENT"."MYTABLE"; + SELECT * FROM targetdb1."REINVENT"."MyTable"; + SELECT * FROM targetdb1."REINVENT"."MYtablE"; + +#### Create a table with a mixed-case integration + +In the following example, you use double quotes to store the table name in lowercase in the Oracle data dictionary. You create `MyTable` as follows. + + + CREATE TABLE REINVENT."MyTable" (id NUMBER PRIMARY KEY, description VARCHAR2(100)); + +The Oracle database stores this table name as `MyTable` with mixed case. To replicate this table to Amazon Redshift, you must specify the mixed case name in the data filter. + + + aws rds create-integration \ + --integration-name mixedIntegration \ + --data-filter "include: ORCL.REINVENT.MyTable" \ + ... + +When you query this table in the replicated database in Amazon Redshift, you must specify the mixed case name `MyTable` by quoting the object name. + + + SELECT * FROM targetdb1."REINVENT"."MyTable"; + +Because Amazon Redshift defaults to lowercase object names, the following queries don't find the object because they are searching for the lowercase name `mytable`. + + + SELECT * FROM targetdb1."REINVENT".MYtablE; + SELECT * FROM targetdb1."REINVENT".MYTABLE; + SELECT * FROM targetdb1."REINVENT".mytable; + @@ -228 +343 @@ The following examples demonstrate how data filtering works for RDS for Oracle z -Using regular expressions in the filter value for database name, schema, or table name, is not supported in RDS for Oracle integrations. +You can't use regular expressions in the filter value for database name, schema, or table name in RDS for Oracle integrations.