Close Menu

    Enable Amazon RDS for Oracle immutable tables for protected workloads

    In financial or audit-regulated industries, applications that write data to a relational database sometimes require that data not be modifiable after it’s written, and that mechanisms are in place to prevent internal or external users from making unauthorized modifications to the data. Immutable tables are an Oracle database feature that function as insert-only tables, allowing new records to be written each time data is added. When data is written to immutable tables, it can’t be modified, preventing users from making any DDL updates or DML DELETE SQL statements.

    Immutable tables are a feature of Oracle Enterprise Edition, or Oracle Standard Edition database 19c and higher. In this post, we guide you through the features of immutable tables when creating, storing, and managing data on Amazon Relational Database Service (Amazon RDS) for Oracle.

    Solution overview

    Amazon RDS for Oracle is a fully managed service of Amazon RDS. You can run Amazon RDS for Oracle under two different licensing models: License Included (LI) and Bring Your Own License (BYOL). In the LI service model, you don’t need to separately purchase Oracle licenses; the Oracle Database software has been licensed by AWS. The LI model is only supported on Amazon RDS for Oracle Database Standard Edition 2 (SE2), which we review in this post.

    Your DB instance resides in a virtual private cloud (VPC). We recommend Multi-AZ deployments for production workloads to maintain high availability. A Multi-AZ deployment creates a primary DB instance and a secondary standby DB instance in another Availability Zone for failover support. AWS Backup is a fully managed service that centralizes orchestration and management of data protection activity across AWS services and hybrid workloads. In AWS Backup, a backup vault is a container that stores and organizes your backups.

    The following diagram illustrates the architecture of a Multi-AZ RDS for Oracle DB instance in a VPC, with AWS Backup Vault for protected backups.

    Immutable tables were introduced to Oracle 21.3 and 19.11 at the same time. To use immutable tables in Oracle 19c, make sure that the COMPATIBLE database initialization parameter is set to 19.11.0 or higher. The value of the COMPATIBLE parameter can be increased to a higher version, but it can’t be decreased to a lower version. Therefore, as a best practice, before you implement the immutable feature on your RDS for Oracle instance, we recommend testing it. You can do this by cloning your existing production instance to a test an RDS for Oracle instance, or by setting up a new RDS for Oracle instance, preferably using the LI model for SE2, so you don’t need separate licenses to purchase. In this post, we use the Amazon RDS for Oracle 19c Standard Edition 2 LI model to create immutable tables.

    Prerequisites

    You must have connectivity to an RDS for Oracle 19c DB instance running in your AWS account. If this is your first time using AWS or Amazon RDS for Oracle, complete the following prerequisites to get started:

    Create an AWS account with an RDS for Oracle instance LI model for SE2 running.

    RDS for Oracle instances have an associated database parameter group where you can customize the parameter value for COMPATIBLE. To do so, create a parameter group using the Amazon RDS console and modify the RDS for Oracle database to associate it with the new parameter group.

    Create a parameter group:

    On the Amazon RDS console, in the navigation pane, choose Parameter groups.
    Choose Create parameter group.
    For Parameter group name, enter the name of your new DB parameter group.
    For Description, enter a description for your new DB parameter group.
    For Engine type, choose your DB engine.
    For Parameter group family, choose a DB parameter group family.
    Choose Create.

    You can also use the AWS Command Line Interface (AWS CLI) to create a DB parameter group, with the create-db-parameter-group command. For Linux, macOS, or Unix, use the following code

    aws rds create-db-parameter-group
    –db-parameter-group-name rds-oracle-customparametergroup-se2
    –db-parameter-group-family oracle-se2-19
    –description “set compatible parameter to enable immutable table feature”

    Edit the parameter COMPATIBLE to 19.11.0 or higher and choose Save Changes.

    You can also use the AWS CLI modify-db-parameter-group command to modify a DB parameter group. For Linux, macOS, or Unix, use the following code:

    aws rds modify-db-parameter-group
    –db-parameter-group-name rds-oracle-customparametergroup-se2
    –parameters “ParameterName=’compatible’,ParameterValue=19.11.0,ApplyMethod=pending-reboot”

    Now you can modify the RDS for Oracle database you created to associate the parameter group you just created:

    In the navigation pane, choose Databases.
    Select the DB instance you want to modify and choose Modify.
    Change the DB parameter group.
    Choose Continue and review the summary of modifications.
    Select Apply immediately and choose Modify DB instance.


    You can also use the AWS CLI modify-db-parameter-group command to modify an RDS for Oracle instance and associate it with the newly created DB parameter group. For Linux, macOS, or Unix, use the following code:

    aws rds modify-db-instance
    –db-instance-identifier rdsoracle-instance-1
    –db-parameter-group-name rds-oracle-customparametergroup-se2
    –apply-immediately

    When you associate a new DB parameter group with a DB instance, Amazon RDS applies the modified static and dynamic parameters only after the DB instance is rebooted. For more details, refer to Use cases for rebooting a DB.

    Reboot the DB instance:

    In the navigation pane, choose Databases.
    Select the DB instance you want to reboot and on the Actions menu, choose Reboot.

    Optionally, choose Reboot with failover? to force a failover from one Availability Zone to another.
    Choose Reboot to reboot your DB instance.

    To reboot a DB instance using the AWS CLI, call the reboot-db-instance command. For Linux, macOS, or Unix, use the following code:

    aws rds reboot-db-instance
    –db-instance-identifier rdsoracle-instance-1

    Create an immutable table

    When creating immutable tables in the database, you must specify the retention period for the immutable table and for the rows within it, using the NO DROP and NO DELETE clauses. The NO DROP clause determines how long the table is protected from being dropped, with a minimum value of 0 days. The NO DELETE clause specifies the retention period for individual rows, specifying how long each row will be protected from deletion, with a minimum value of 16 days.

    The retention periods defined in the NO DROP and NO DELETE clauses can only be increased over time. As a best practice, we recommend starting with the minimum values and adjusting them as needed based on business requirements. Additionally, if you’re testing the immutable table feature on an existing RDS for Oracle instance that is actively being used, we recommend creating a separate schema with the necessary privileges. Connect to the schema, create the immutable table with the minimum retention values, and drop the schema when testing is complete.

    To test the immutable table feature in an RDS for Oracle instance, complete the following steps:

    Connect to the RDS for Oracle DB instance.
    Create a schema called immutable_table_test with the necessary privileges in the RDS for Oracle instance you created earlier.
    The CREATE TABLE system privilege is required to create immutable tables in your own schema. The CREATE ANY TABLE system privilege is required to create immutable tables in another user’s schema. In this example, we create a schema with the default tablespace “users” and the temporary tablespace “temp”, which you can update according to your requirements. See the following code:

    CREATE USER immutable_table_test default tablespace users temporary tablespace temp IDENTIFIED BY <password>;
    grant create session to immutable_table_test;
    grant create table to IMMUTABLE_TABLE_TEST;
    alter user immutable_table_test quota unlimited on users;

    Connect to the RDS for Oracle instance using the schema you created and create an immutable table called user_audit. The NO DROP and NO DELETE clauses are mandatory in a CREATE IMMUTABLE TABLE statement. In this example, we specify the retention values as minimum values. Keep in mind that you can’t modify the table structure (add, remove, or rename columns) after you create the immutable table.

    CREATE IMMUTABLE TABLE user_audit (userid NUMBER, action VARCHAR2(40), application VARCHAR2(10)) NO DROP UNTIL 0 DAYS IDLE NO DELETE UNTIL 16 DAYS AFTER INSERT;

    Insert sample records into the immutable table:

    Insert into user_audit values (10101, ‘document:index page’, ‘webapp01’);
    Insert into user_audit values (10101, ‘document:auth page’, ‘webapp01’);
    Insert into user_audit values (10101, ‘document:accounts page’, ‘webapp01’);
    Insert into user_audit values (10101, ‘document:mortgage page’, ‘webapp01’);
    Insert into user_audit values (10101, ‘document:transfer page’, ‘webapp01’);

    Only rows that are outside the specified retention period can be deleted from an immutable table. Following the insert, if you try to delete a record from the immutable table, it will generate an error:

    delete from user_audit where userid=10101 and action like ‘%transfer%’;
    SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table

    If you try to modify a record in the table, and you will see a similar error message:

    update user_audit set action=’document:credit page’ where userid=10101 and action like ‘%transfer%’;
    SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table

    You can validate the records in the immutable table to make sure the records were not deleted or modified. Use the following command to view the records:

    select * from user_audit;

    Optionally, you can modify the retention period for an immutable table and the retention period for rows within the immutable table by using the ALTER TABLE statement. The following statement modifies the definition of the immutable table user_audit and specifies that it can’t be dropped if the newest row is less than 50 days old. The previous value for the NO DROP clause was 0 days.

    ALTER TABLE user_audit NO DROP UNTIL 50 DAYS IDLE;

    The following statement modifies the definition of the immutable table user_audit and specifies that a row can’t be deleted until 120 days after it was created. The previous value for the NO DELETE clause was 16 days.

    ALTER TABLE user_audit NO DELETE UNTIL 120 DAYS AFTER INSERT;

    An immutable table can be dropped only if it’s empty or after it has not been modified for a period of time that is defined by its retention period. Because the immutable table was created with NO Drop Until ‘n’, it can’t be dropped if the newest row is less than n days old.

    drop table user_audit;
    ORA-05723: drop blockchain or immutable table USER_AUDIT not allowed

    You can get the information of immutable tables in the RDS for Oracle database using the view USER_IMMUTABLE_TABLES or ALL_IMMUTABLE_TABLES for the tables owned by the user. See the following code:

    SELECT table_name “Immutable Table Name”,row_retention “Row Retention Period”, row_retention_locked “Row Retention Lock”, table_inactivity_retention “Table Retention Period” FROM user_immutable_tables;

    To learn more about immutable table features and restrictions, see Managing Tables in the Oracle documentation.

    AWS Backup Vault Lock: Amazon RDS backup immutability

    AWS Backup is a fully managed service that centralizes orchestration and management of data protection activity across AWS services and hybrid workloads.

    By default, Amazon RDS creates and saves automated backups of the database during a specified backup window. You can also manually back up your database instance by creating a database snapshot or schedule backups using AWS Backup. AWS Backup integrates with Amazon RDS, allowing you to use policy-based rules to automate backup creation, copy, restore testing, and deletion. The following diagram illustrates this workflow.

    AWS Backup offers an optional feature called AWS Backup Vault Lock, which provides immutability for backup data by preventing changes to the backup retention period and preventing manual deletion of backups.

    For more details, refer to the Write-One-Read-Many (WORM) section in the AWS Backup FAQs.

    To enhance data security and exercise control over your backup vaults, as a best practice, we recommend that customers using AWS Backup utilize the AWS Backup Vault Lock feature in conjunction with immutable tables. You can configure AWS Backup Vault Lock using the AWS Backup console, which offers a user-friendly interface for enabling immutability.

    There are two types of vault lock:

    Governance mode – The governance mode lock can be removed by users with sufficient AWS Identity and Access Management (IAM) permissions.
    Compliance mode – The compliance mode lock can’t be removed after the grace time window has passed. This is more secure than governance mode, but will have cost implications if the backups are put with long-term retention in a vault locked with compliance mode. Those backups can’t be deleted until their retention period expires, and there is no workaround to remove a compliance mode vault lock. Review AWS Backup Vault Lock before making the decision to set a long-term retention time.

    For a step-by-step guide to configure immutable backups using AWS Backup Vault Lock, refer to Protecting data with AWS Backup Vault Lock.

    Clean up

    To clean up the resources you created, complete the steps in this section.

    Delete rows from immutable tables

    Only rows that are outside the specified retention period can be deleted from an immutable table. Only the RDS for Oracle primary user or the owner of the table (in this example, the schema immutable_table_test) can delete rows from an immutable table in an RDS for Oracle DB instance.

    Use the PL/SQL procedure DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS to delete the rows that are beyond the specified retention period or obsolete rows that were created before a specified time.

    The following code example, when connected as schema immutable_table_test or the RDS for Oracle primary user, deletes the rows in the immutable table user_audit that are outside of the retention window. The number of rows deleted is stored in the output parameter num_rows.

    DECLARE
    num_rows NUMBER;
    BEGIN
    DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS(‘IMMUTABLE_TABLE_TEST’,’USER_AUDIT’, NULL, num_rows);
    DBMS_OUTPUT.PUT_LINE(‘Number_of_rows_deleted = ‘ || num_rows);
    END;
    /

    Drop immutable tables

    You can drop an immutable table if it’s empty or after it hasn’t been modified for a period of time that is defined by its retention period. The immutable table must be contained in your schema or you must have the DROP ANY TABLE system privilege.

    Use the DROP TABLE statement to drop an immutable table. Dropping an immutable table removes its definition from the data dictionary, deletes its rows, and deletes the indexes and triggers defined on the table. It is recommended that you include the PURGE option when you drop an immutable table.

    The following statement drops the immutable table user_audit in the immutable_table_test schema:

    drop table immutable_table_test.user_audit purge;

    Drop the RDS for Oracle test instance

    After you have successfully tested the solution and dropped the immutable table as mentioned in the previous step, you can delete the RDS for Oracle instance that you used for testing the immutable table feature. However, if you are testing it in an existing RDS for Oracle instance, you can drop the schema immutable_table_test that was created for testing the feature. See the following code:

    drop user immutable_table_test cascade;

    Conclusion

    Immutable tables on Amazon RDS for Oracle are suitable for applications that write data that isn’t modifiable. This feature can be classified as a Write-Once-Read-Many (WORM) model on a relational database. Because Amazon RDS is a fully managed service, you don’t have to worry about database maintenance tasks like installing software, patching, upgrades, backups, and other tasks.

    If you are planning an Amazon RDS for Oracle migration, check out our amazing AWS RDS Partners to help with your migration journey to AWS.

    About the Authors

    Bharath Terala is a Sr. Partner Solutions Architect and is a database technical field community member at Amazon Web Services. Prior to joining AWS, he held Oracle DBA and manager roles for DBA practice. He worked with various industry customers for Oracle database, ERP, BI, WebLogic, and Hyperion application implementations, and migrations to AWS. Outside of work, he enjoys time with his family.

    Javeed Mohammed is a Sr. Database Specialist Solutions Architect with Amazon Web Services. He works with the Amazon RDS team, focusing on commercial database engines like Oracle and Db2. He enjoys working with customers to help design, deploy, and optimize relational database workloads in the AWS Cloud.

    Source: Read More

    Related Posts

    Leave A Reply