Minimal privileges for Amazon S3 backup user

This is a follow-up to an old post I did about how to backup Oracle database schemas to Amazon S3 using PL/SQL.

In short, the packages provided in the Alexandria Utility Library for PL/SQL allow you to set up a schema-level backup of files from your database to Amazon's Simple Storage Service (S3).

At the end of that article I mentioned that you should use AWS Identity and Access Management (IAM) to create a separate backup user that has minimal privileges, to reduce the risk if the password ("Secret Access Key" in S3 terminology) is ever exposed. By creating a separate user which only has upload ("PutObject") permissions on your S3 folder, and no privileges to list file contents, delete or download files, you can limit the damage that someone with a stolen password can do.

Here's how to set this up:

1. Go to your AWS admin console.
2. Go to S3 service and create a new bucket (your-bucket-name).
3. Create a folder called "backup".
4. Optionally, create subfolders "backup/schemas" and "backup/apps".
5. Go to IAM service.
6. Go to Groups and create a new group ("MySiteBackupUsers"). Do not add any of the default policies.
7. Go to Permissions for the group and under "Inline Policies", click on "Create Group Policy". Choose "Custom Policy".
8. Policy Name: "UploadFilesToBackup".
9. Add the following policy definition:

  {
    "Statement": [
        {
            "Action": [
                "s3:PutObject"
            ],
            "Effect": "Allow",
            "Resource": "arn:aws:s3:::your-bucket-name/backup/*"
        }
    ]
}

Here is a screenshot:

Then go to Users and click "Create New Users". Enter a user name ("MySiteBackupUser"). After the user has been created, click on "Show User Security Credentials" and copy the values (Access Key ID and Secret Access Key) into your backup script settings.
Finally, add the user to the newly created user group (add user "MySiteBackupUser" to group "MySiteBackupUsers").

That's it, you now have a minimally privileged user account that you can use for your S3 backup scripts from PL/SQL.

Version 1.7 of Alexandria Utility Library for PL/SQL

The latest version of Alexandria, the utility library for PL/SQL, is now available for download. The previous version has been downloaded more than 2,000 times in the last 8 months.

The docs/readme.txt file contains a fairly detailed list of additions, enhancements and bug fixes. Below are some of the more noteworthy changes:

Improved installation script

You can now choose between installing the full library (close to 50 packages as of this version), by running the main /setup/install.sql script, or you can install just the core set of packages via setup/install_core.sql and then choose additional sets of packages on top of that (for example, /setup/install_microsoft.sql or /setup/install_inet.sql and so on).

Amazon S3 package enhancements

Jeffrey Kemp has submitted several functional enhancements to the S3 package which are incorporated into this version. Jeffrey has also done talks on the Alexandria Library in general and the S3 package in particular; the slides from these presentations are available here. Thanks for spreading the word, Jeff!

New packages: Icalendar, URI templates, and image utilities

A couple of new kids on the block:

• ICALENDAR_UTIL_PKG: Lets you create Icalendar files from the database.
• IMAGE_UTIL_PKG: Get information (such as image width and height) from JPG/GIF/PNG image files. This package is based on code from Anton Scheffer.
• URI_TEMPLATE_UTIL_PKG: Use URI Templates to deal with "nice URLs" in PL/SQL and Apex web applications. 

I might get back to some of these topics later, in separate blog posts; here is a screenshot of the URI_TEMPLATE_UTIL_PKG package in action. (In real life, you would get the actual URI from a web request, for example via mod_plsql, and do something useful after parsing the request.)

Database (schema) backup to the cloud with PL/SQL

In my last blog post I described using the DBMS_DATAPUMP and DBMS_JOB packages to automate database schema backups from the database itself, rather than using batch files to do so. I also mentioned that "since the files are written to the database server itself, you need some kind of process to move the files to another server or storage location, so the backups don't disappear along with the database if the database server itself is somehow lost or corrupted. [One] option is to upload the backup file from the database to some online ("cloud") storage service, such as Amazon S3."

Since I have previously written a PL/SQL API for Amazon S3, uploading your backup files to S3 is not very difficult. The high-level steps are:

1. Create an export (dump file) of your database schema using DBMS_DATAPUMP (or the DATAPUMP_UTIL_PKG wrapper described in my last blog post).
2. Save the export file to disk on the server.
3. Upload the export file to S3.
4. (Optionally) Delete the export file from the server.
5. (Optionally) Send an email notification indicating if the upload was successful or not.

In the "demos" folder of the Alexandria Utility Library for PL/SQL you can find a package called DATAPUMP_CLOUD_PKG which does the work described above in a procedure called BACKUP_SCHEMA_TO_S3.

Here is how you can set up a call to DBMS_JOB to schedule regular backups of your database schema to Amazon S3 (note the double quoting of the string values):

declare

  l_job number;

  l_what varchar2(4000);

begin

  l_what := 'datapump_cloud_pkg.backup_schema_to_s3(''your_amazon_id'', ''your_amazon_password'', ''your_amazon_bucket'', ''your_amazon_folder'', ''YOUR_ORACLE_BACKUP_DIR'', ''your_backup_file_name_'' || to_char(sysdate, ''yyyymmddhh24miss''), p_email_failure => ''your_email_address@domain'', p_email_success => ''your_email_address@domain'', p_version => ''10.2'', p_gmt_offset => 0);';

  -- run job at the end of each day

  dbms_job.submit (job => l_job, what => l_what, next_date => sysdate, interval => 'trunc(sysdate + 1) + 23/24');

  commit;

end;

/

Security Tip: You may not like to expose your Amazon password in the job definition. What you can do to reduce the risk is to use the AWS Identity and Access Management (IAM) tools to define a separate backup user which only has upload ("put") permissions on your S3 folder, and no privileges to list file contents, delete or download files. This will limit the damage that someone with a stolen password can do.

Amazon S3 API for PL/SQL

Amazon S3 is part of Amazon's Web Service offering and the name is an abbreviation for Simple Storage Service:

"Amazon S3 provides a simple web services interface that can be used to store and retrieve any amount of data, at any time, from anywhere on the web. It gives any developer access to the same highly scalable, reliable, secure, fast, inexpensive infrastructure that Amazon uses to run its own global network of web sites. The service aims to maximize benefits of scale and to pass those benefits on to developers."

A few months ago, Jason Straub published an Oracle whitepaper on how to integrate Oracle Application Express (Apex) with Amazon S3.

As Jason points out, Amazon has a Free Usage Tier which allows you to get started using Amazon S3 for free. If you have ever bought a book from Amazon, they already have your credit card on file, so signing up for Amazon Web Services is quick and easy (and they won't start charging your credit card until the free trial period is over).

Introducing the S3 API for PL/SQL

Inspired by Jason's whitepaper, I decided to write a stand-alone PL/SQL API for Amazon S3. This API can be used in any PL/SQL solution, with or without Apex.

The API supports all common S3 operations, including

• Authentication
• Creating new buckets
• Listing existing buckets
• Listing existing objects (with or without filtering)
• Creating (uploading) new objects (and setting an Access Control List - ACL)
• Generating download links (with or without expiry dates)
• Downloading objects
• Deleting objects

See the examples below for more details.

Use Cases

So what can you do with Amazon's S3 service in combination with a PL/SQL API?

I can think of several interesting use cases, some of which I might explore further in future posts:

• Backing up your database (use DBMS_DATAPUMP to dump a file to disk, then compress it using ZIP_UTIL_PKG, then encrypt it using CRYPTO_UTIL_PKG, and upload it to S3)
• Backing up your PL/SQL source code (use data dictionary views or DBMS_METADATA to extract the source code, optionally zip and/or encrypt it, and upload to S3)
• Backing up your Apex applications (use WWV_FLOW_UTILITIES.EXPORT_APPLICATION_TO_CLOB to generate export file, optionally zip and/or encrypt it, and upload to S3)
• Cloud storage for file uploads (instead of storing [large] files inside your database, store them in the cloud and download them on demand -- especially relevant for Oracle XE which has a file size limit)
• Serve static content (generate static [text, CSV, HTML, PDF] files from the database and upload to S3)
• Replication or shared storage (upload from one machine/database, download to another)
• Data loading or message processing (set up to poll for new incoming files - uploaded by other S3 clients - and process them)

Remember that all these things can be scheduled to run in the database using DBMS_JOB or DBMS_SCHEDULER.


Where to get the Amazon S3 API for PL/SQL

You can download the API as part of the Alexandria Utility Library for PL/SQL.


Getting started

Download and compile the relevant PL/SQL API packages. Then register with Amazon for an S3 account and get your AWS keys (key and secret key), and login to the AWS Management Console to get familiar with the basic operations.

If you are unfamiliar with Amazon S3, I recommend that you read this short getting started guide that describes the common operations.

In the following examples we shall see how you can do the same operations using PL/SQL.


Authentication

From your Amazon account control panel, you'll get the key strings you need to use the Amazon web services.

Before you call any of the following API methods, you must initialize the authentication package. You only have to do this once per database session (but remember, on the web, every page view is a separate database session, so in Apex you'll need to run this code for every page, typically as a Before Header page process).

Creating new buckets

Buckets are what you use to organize your objects (files) in Amazon S3. Think of them as top-level folders, but note that you cannot create more than 100 buckets in a single account, and the bucket name must be unique across all user accounts on Amazon S3. So creating buckets is not really something you'd do very often, and usually it will be done manually (to resolve any name conflicts with existing buckets).

A bucket is associated with a specific region where your objects will be stored. For reasons of latency/speed and possibly legal issues, it makes sense to select a region that's close to you and your users (although you may actually want to locate it far away if the purpose is backup for a major disaster in your own area).

Here's how to create a new bucket via PL/SQL code:

Checking the AWS management console to verify that the bucket has indeed been created (in the specified region):

Listing existing buckets

With one or more buckets created in your account, you can list the bucket names.

There are two way to do this, either by retrieving an index-by PL/SQL table using the GET_BUCKET_LIST function:

or, alternatively, via SQL using a pipelined function named GET_BUCKET_TAB:

Creating (uploading) new objects

An "object" is a file, and this is really what the S3 service is all about, storing files. So let's upload a file or two to our new bucket!

The API lets you upload any BLOB data to S3 using the NEW_OBJECT procedure.

When you upload a file to S3, the default Access Control List (ACL) makes sure that only the owner of the file (you!) can access (download) it.

Others get an "Access Denied" message (but see the "Generating download links" section for how to generate special time-limited download links):

There are a number of predefined ACLs that you can specify if, for example, you want to make the file publicly available.

Which can then be freely downloaded by anyone (the use of HTTPS is optional).

A note about "folders": S3 has no concept of "folders", but you can simulate folders by using a forward slash in your file names (as seen in the previous example). Some S3 clients, such as the AWS management console, will present such files in a folder structure. As far as the PL/SQL API is concerned, the slash is simply part of the file name and has no special meaning.

Listing existing objects

Now that we have uploaded a couple of files, we can list the contents of the bucket via the GET_OBJECT_LIST function:

You can also get a list in SQL via a pipelined function named GET_OBJECT_TAB:

In both cases, you can optionally specify a prefix that acts as a search filter for the file names you want to return, and/or the maximum number of items you want to return.

Generating download links

You can access a file that has been protected by an ACL by including a special checksum parameter in the URL.

The GET_DOWNLOAD_URL function lets you generate the URL needed to access the file. You can specify when the link should expire, so this means you can share a download link that will stop working after a specified amount of time, which can obviously be useful in a number of scenarios.

Pasting the generated URL into the browser allows us to access the file:

Downloading objects

Downloading a file from S3 using PL/SQL is straightforward with a call to the GET_OBJECT function which returns a BLOB:

Deleting objects

Removing a file is likewise very simple, just call the DELETE_OBJECT procedure:

Summary

The ability to upload and download any file from the Oracle database to "the cloud" (and vice versa) via PL/SQL is extremely useful for a number of purposes.

Let me know if you find this API useful!