Use Google Service Account to Access Google Cloud Platform Resources

You can use a Google service account to access Google Cloud Platform (GCP) resources from an Autonomous Database instance.

About Using a Google Service Account to Access Google Cloud Resources

When you use Google service account based authentication with Autonomous Database, an application can securely access Google Cloud Platform (GCP) resources without creating and saving credentials based on long-term IAM access keys for the GCP resources.

A Google service account is a special kind of GCP account used by an application. You can use a Google service account to make authorized GCP REST API calls from an application (after the service account is given access permissions through IAM role configuration). When an application makes calls with GCP service account based authentication, the initial call generates a temporary access token through OAuth2.0. The OAuth2.0 access token is valid for one hour. Subsequent requests within the hour use the OAuth2.0 access token to make authorized GCP REST API calls.

For example, you may want to load data from Google Cloud Storage into your Autonomous Database, perform some operation on the data, and then write the modified data back to Google Cloud Storage. You can do this without using a service account if you have GCP user credentials to access Google Cloud Storage. However, using a role-based Google service account to access GCP resources from Autonomous Database has the following benefits:

  • You can create role-based access, with different policies for different users or schemas that need access to GCP resources from an Autonomous Database instance. This allows you to set a policy to limit access to resources by role. For example, setting a policy that is limited to read-only access, by role, to a Google Cloud Storage bucket.
  • Google service account based credentials provide better security, as you do not need to provide long-term user credentials in code when your application accesses GCP resources. Autonomous Database manages the temporary credentials for the Google service account and does not need to store GCP resource user credentials in the database.

See Service accounts for information on Google service accounts.

Enable Google Service Account and Find the GCP Service Account Name

Prior to using a Google Cloud Platform (GCP) resource with a Google service account you need to enable GCP access for your Autonomous Database instance.

  1. Enable Google service account authentication with DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH.

    For example, to enable Google service account authentication for the ADMIN user:

    BEGIN
        DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(
            provider => 'GCP' );
    END;
    /

    Enable Google service account authentication for a non-ADMIN user, adb_user as follows:

    BEGIN
        DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(
            provider => 'GCP',
            username => 'adb_user');
    END;
    /

    If you want the specified user to have privileges to enable Google service account authentication for other users, set the params parameter grant_option to TRUE.

    BEGIN
        DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(
            provider => 'GCP',
            username => 'adb_user',
            params   => JSON_OBJECT('grant_option' value TRUE));
    END;
    /

    After you run DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH with grant_option set to TRUE, adb_user can enable Google service account authentication for another user. For example, if you connect as adb_user, you can run the following command to enable GCP service account access for adb_user2:

    BEGIN
        DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(
            provider => 'GCP',
            username => 'adb_user2');
    END;
    /
  2. When DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH runs it creates a Google service account. Query CLOUD_INTEGRATIONS to obtain the service account details for your Autonomous Database instance.
    SELECT * FROM CLOUD_INTEGRATIONS WHERE param_name = 'gcp_service_account';
    
    PARAM_NAME           PARAM_VALUE
    ---------------------------------------------------------------------------
    gcp_service_account  GCP-SA-22222-32222@gcp-example.iam.gserviceaccount.com
  3. Note the gcp_service_account parameter value as you must supply this value when you configure GCP resources.

See ENABLE_PRINCIPAL_AUTH Procedure for more information.

Assign Roles to the Google Service Account and Provide Access for GCP Resources

To use Google Cloud Platform (GCP) resources from an Autonomous Database instance, you or a Google Cloud Administrator must assign roles and privileges to the Google service account that your application accesses. In addition to assigning roles for the Google service account, for any GCP resources you want to use a Google Cloud administrator must add Google IAM principals.

As a prerequisite, first enable the Google service account on your Autonomous Database instance. See Enable Google Service Account and Find the GCP Service Account Name for more information.

  1. Open the Google Cloud Console for your account.
  2. Create roles with the specified permissions.
    1. From the navigation menu, select IAM & Admin.
    2. From the IAM & Admin navigator, select Roles.
    3. On the Roles page, click More Actions and select + CREATE ROLE.

    For example, you can create a role Object Store Read and Write to control the use of an Object Store bucket.

    Description of gcp_iam_roles_create.png follows
  3. On the Create Role page, click + ADD PERMISSIONS.
    1. Select filters to limit the list of permissions.

      For example, enter the filter Permission: Storage.Objects to show only the Object Store permissions.

      Description of gcp_iam_roles_add_permissions.png follows
    2. On the Add permissions dialog, click ADD.
  4. On the Create Role page, click CREATE.
    Description of gcp_iam_roles_create_assigned.png follows
  5. Add roles and principals for the resource you want to access.

    For example, if you want to access Google Cloud Storage using the role you just created, Object Store Read Write:

    1. From the navigator, select Cloud Storage and select Buckets.
    2. Select the bucket you want to use, and click PERMISSIONS.
    3. Click + ADD PRINCIPAL.
  6. On the Grant access to "bucketname" dialog, add roles and a principals for the selected resource.
    1. Under Add principals add the value of the gcp_service_account parameter from your Autonomous Database instance.
    2. On the Grant access to "bucketname" dialog, enter roles under Assign Roles and then click SAVE.
      Description of gcp_iam_bucket_permissions.png follows

After you complete these steps the roles and principals are assigned. This allows your application running on the Autonomous Database instance to access the GCP resource with a Google service account.

Use Google Service Account with DBMS_CLOUD

When you make DBMS_CLOUD calls to access Google Cloud Platform (GCP) resources and specify the credential name as GCP$PA, the authentication on the Google Cloud Platform side happens using a Google service account.

If you have not already done so, perform the prerequisite steps:

To use a DBMS_CLOUD procedure or function with Google service account authentication:

  1. Use GCP$PA as the credential name.
  2. Construct the URI to access the GCP resource using virtual hosted-style:

    https://BUCKET_NAME.storage.googleapis.com/OBJECT_NAME

    For example, you can access Google Cloud Storage using Google service account credentials as follows:

    SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('GCP$PA', 'https://treetypes.storage.googleapis.com/' );
    
    OBJECT_NAME BYTES CHECKSUM                         CREATED LAST_MODIFIED
    ----------- ----- -------------------------------- ------- ------------------------
    trees.txt      58 682075a8c38f5686c32c25c6fb67dcbe         2022-10-05T20:03:55.253Z 
    

See the following for more information:

Disable Google Service Account

To disable Google service account access to Google Cloud Platform (GCP) resources, use DBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH.

When the provider value is GCP and the username is a user other than the ADMIN user, the procedure revokes the privileges from the specified user. In this case, the ADMIN user and other users can continue to use GCP$PA.

For example, to revoke privileges for adb_user:

BEGIN
    DBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH(
        provider => 'GCP',
        username => 'adb_user');
END;
/

When the provider value is GCP and the username is ADMIN, the procedure disables Google service account access on the Autonomous Database instance. The default value for username is ADMIN.

For example:

BEGIN
    DBMS_CLOUD_ADMIN.DISABLE_PRINCIPAL_AUTH(
        provider => 'GCP' );
END;
/

See DISABLE_PRINCIPAL_AUTH Procedure for more information.

Google Service Account Notes

Notes for using Google service account.

  • Google Cloud Platform (GCP) character restriction: DBMS_CLOUD does not support a URI containing an "_" to access a Google Cloud Storage bucket name. If your Google Cloud Storage bucket name contains an "_", you might see the following error:

    SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('GCP$PA', 'https://app_bucket.storage.googleapis.com/');
    
    ORA-20006: Unsupported object store URI - https://app_bucket.storage.googleapis.com/
    ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1306
  • Cloning an Autonomous Database instance with a Google service account: When you clone an instance with a Google service account enabled, the Google service account configuration is not carried over to the clone. Perform the steps to enable the Google service account on the clone if you want to enable Google service account on a cloned instance.