ATP Datapump using resource principal authentication for non admin user

 The Datapump concept in Oracle database is very much useful for different usecases like taking backup, migrating the database from on-prem to cloud and more. Another advantage is it can directly push the dump into the OCI Object Storage  

https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/export-data-create-dump-file.html#GUID-8D734C1A-FAF3-446C-B777-16DF62FB049E

If we want to upload the dump into OCI object storage, we can use different authentication mechanism like authToken, basic credential based authentication, Resource Principal based authentication. 

In this post, we will see how we can enable the resource principal and in particular how we can grant necessary roles to the non admin database user

EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();

EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(username => 'USER1');

However if we want non-admin user to enable the resource principal for USER1 then the following needs to be granted to that non-admin user

GRANT EXECUTE ON DBMS_CLOUD_ADMIN TO PROXY_ADMIN_USER;

EXEC DBMS_CLOUD_ADMIN.enable_resource_principal(username => 'PROXY_ADMIN_USER');

GRANT EXECUTE ON ADMIN.OCI$RESOURCE_PRINCIPAL TO PROXY_ADMIN_USER WITH GRANT OPTION;

References:

https://www.oreilly.com/library/view/oracle-database-administration/1565925165/ch06s01s05s01.html

Connecting to Google Cloud SQL from SQuirreL SQL

Objective:

The objective of this post is to explain how to access Google Cloud SQL from third party tools like SQuirrel SQL.

About Google Cloud SQL:

Is a MySQL DB running in google cloud. This facility is charged by google based on type of instance we select.

https://cloud.google.com/sql/docs/introduction

About SQuirreL SQL:

SQuirrel SQL is an open source universal SQL client. It comes as a jar file and the concept that it uses to connect to SQL server is JDBC; thus we should have the corresponding JDBC driver for different types of databases.

http://squirrel-sql.sourceforge.net/

Accessing Google Cloud SQL server from SQuirrel SQL client:

The SQuirrel SQL is very much helpful in doing administration kind of work with databases.

Step 1: Create cloud sql instance in google developer console. The instance name is always prefixed with project name. While creating the instance, we need to select the type of instance and billing type.

Step 2: For safety and security, Google does not allow request from every IP to access the database. Hence we need to explicitly add our PC/laptop IP where SQuirrel SQL is running. Also we need to provide the public IP address and not the proxy IP.

Step 3: Create an user under 'Users' tab and provide % for client host

Step 4: SQuirrel SQL is not supporting IPV6, so we need to request IPV4 under IP address tab. Be aware if we are not using the IPV4 address then google will charge us. So once the work is completed, release IPV4 address.

Now all set in developer console. Now time to bring up SQuirrel SQL.

Step 5: When we launch SQuirrel SQL for the first time, the MySQL Driver may be missing, We can see the list of drivers in Driver tab.

Step 6: If the driver for MySQL is missing then download the mysql-connector-java-<x.x.x>-bin.jar and add it in class path as shown below.

Step 7: Create an aliases and provide proper JDBC URL and username & password given in developer console. Test the connection. The thing that will need change in the URL is the IP address and database name.