Saturday, July 23, 2016

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": [
            "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.


Brad Peek said...

Hope you don't consider this too off topic... I am trying to use your Alexandria PL/SQL packages to enable APEX to upload/download to AWS S3 but AWS is returning "ORA-20000: The authorization mechanism you have provided is not supported. Please use AWS4-HMAC-SHA256."

I saw this issue was reported earlier and you mentioned that Oracle 12 DBMS_CRYPTO package has a "hmac_sh256" function.

I edited the AMAZON_AWS_AUTH_PKG body as follows:

l_encrypted_raw := dbms_crypto.mac (src => l_decrypted_raw, typ => dbms_crypto.hmac_sh1, key => l_key_bytes_raw);

l_encrypted_raw := dbms_crypto.mac (src => l_decrypted_raw, typ => dbms_crypto.hmac_sh256, key => l_key_bytes_raw);

This compiled, but unfortunately I get the same error message. Any ideas?

The region where my AWS bucket resides (us-east-2) only supports this one authorization mechanism. Do any of the other regions still support the older method?

Morten Braten said...

@Brad: US East 1 (aka US Standard) still supports the older authentication mechanism, and so do several other regions. See for more details.

- Morten