Oracle Cloud Infrastructure Documentation

Generate Client ID and Client Secret using UI

In this section you use the Web UI to obtain the client_id and client_secret .

You generate the client keys by accessing the Autonomous Database instance URL appending with oauth/clients.

For example, if your instance is “ https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/ords/<schema Name>/_sdw/", you need to sign in to the link " https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/ords/<schema Name>/oauth/clients/". Be sure to include the trailing slash.

  1. Sign in to Database Actions with the "https://machinename.oraclecloudapps.com/ords/SchemaName/oauth/clients/" link. You can view an OAuth Clients page in the link "https://localhost:port/ords/schemaName/_sdw/?nav=rest-workshop&rest-workshop=oauth-clients".
  2. Click the +Create OAuth Client button to create a new client.
    Description of create-client.png follows
    Description of the illustration create-client.png
  3. From the Grant type drop-down, select the type of client connection you want. You can select the following options:
    • AUTH_CODE: Select this option for implicit connection. Use this response type when the autonomous database is in a private subnet or within a customer firewall.
    • IMPLICIT: Select this option for explicit connection. This is the more secure method and is preferred to use if the Autonomous database has public access.
  4. Enter the following fields. The fields with an asterisk (*) are mandatory:
    • Name: Name of the client.
    • Description: Description of the purpose of the client.
    • Redirect URI: web application deployment URL you copied from step 10 of Deploy the Google Script as a Web app
    • Support URI: Enter the URI where end users can contact the client for support. Example: https://script.google.com/
    • Support Email: Enter the email where end users can contact the client for support.
    • Logo: Optionally, select an image from your local system to insert a logo for your new client.
    Navigate to the Roles tab to select the roles of the client. This is not a mandatory field.
  5. Progress to the Allowed Origins tab. Specify and add the list of URL prefixes in the text field. This is not a mandatory field.
  6. Progress to the Privileges tab to add any privilege. You are not required to have any privileges to create an OAuth Client.
  7. Click Create to create the new OAuth Client. This registers the OAuth Client which you can view on the OAuth Clients page.
    Description of new-client.png follows
    Description of the illustration new-client.png
  8. Click the show icon to view the Client ID and the Client Secret fields.

How do I connect manually?

The following sections demonstrate how to connect using implicit and explicit connections. Google Sheets needs permission to access the Autonomous Database. You must first complete the authorization to connect to the autonomous database. The add-on requires one-time authentication for the setup.

  1. On the Google Sheet, click Oracle Autonomous Database and select Connections.
    Selecting Connections requires one-time Google authentication.
    • Clicking Connections opens a pop-up window that asks your permission to run the authorization. Click Continue.
      Description of auth-continue.png follows
      Description of the illustration auth-continue.png
    • You will now view a window that informs you that the application requests access to sensitive information in your Google account.
    • Click Advanced and select the Go to Untitled project (unsafe) link. Selecting the link opens new window, ensuring you trust the application. Click Allow to continue. You have now completed the setup.
  2. On the Connections wizard, click Add Connection from the Manage Connections drop-down menu to add a connection.
    Description of add-connection.png follows
    Description of the illustration add-connection.png
  3. Selecting Add Connection opens an Add Connection wizard in the Connections wizard's connection list panel.
    Description of add-connection-fields.png follows
    Description of the illustration add-connection-fields.png

  4. Specify the following field values in the wizard:

    Connection Name: Enter the connection's name—for example, TestConnection.

    Autonomous Database URL: Enter the URL of the Autonomous Database you wish to connect to. For example, “https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/”

    In the OAuth Client Grant Type field, select one of the two options based on the type of connections you want. Refer to the Generate Client ID and Client Secret using the UI section.

    This option varies with implicit and explicit connections.

    Implicit: Select this option for implicit connection. Use this response type when the autonomous database is in a private subnet or within a customer firewall.

    AUTH_CODE: Select this option for explicit connection. This is the more secure method and is preferred to use.

    When you select the Implicit option, you can view the following fields:
    Description of implicit.png follows
    Description of the illustration implicit.png

    OAuth Client ID: client_id you generate using the Create New Client wizard in the UI. Refer to the Generate Client ID and Client Secret using the UI section.

    Schema Name: Specify the name of the schema.

    When you select AUTH_CODE, you can view the following fields:


    Description of explicit.png follows
    Description of the illustration explicit.png

    OAuth Client ID: client_id you generate using the Create New Client wizard in the UI. Refer to the Generate Client ID and Client Secret using the UI section.

    OAuth Client Secret: client_secret you generate using the Create New Client wizard in the UI. Refer to the Generate Client ID and Client Secret using the UI section.

    Schema Name: Specify the name of the schema.

    Click Save.

    After you click Save, you can view the new connection in the connection list panel. The connection list displays the connection's name, the schema's name, and the OAuth type you grant. However, it is still in a disconnected state.

  5. Click the three vertical dots beside the connection name and perform the following operations:

    Connect: Select Connect to the Autonomous Database and change the connection status to active. Selecting Connect opens the sign-in page of the Autonomous database. After you log in, you will view a page that shows that database access has been granted to you. Close the window and return to Google Sheets. You will now see that the connection is active.

    Edit: Select Edit to update any value of the connection. Click Save to update the edited values.

    Duplicate: Select Duplicate to create a duplicate connection.

    Remove: Select Remove to remove the connection from the connection list.

Exporting Connections

  1. Click Export Connection from the Manage Connections drop-down menu to export the selected connection.
  2. Select the connection you want to export, and click Export.
    Description of export.png follows
    Description of the illustration export.png
  3. Click Export.

  4. The exported connection downloads in your local system. The connection file is saved as spreadsheet_addin_connections.json.