Set up Role-Based Access Control Automatically using a Script

You can run the script in Cloud Shell to generate IAM Connections files quickly. This automation process significantly reduces the time required to perform the detailed RBAC configuration. After an Admin user generates the Connections file, it can be shared across the organization to allow signing in to the database from Spreadsheet Add-ins.

The following section describes how to:

Generate Connections file using script
Download Connections file
Allow access for domain users

Prerequisites

To use the script for RBAC configuration, you must have:

An Admin account in Oracle Cloud Infrastructure
An IAM domain
An Autonomous AI Database instance and schema

Generate Connections File using Script

Note:

Though the script performs the configuration tasks automatically it is an interactive script, which requires user inputs to proceed.

Log in to the Oracle Cloud Infrastructure console, if not already logged in, and do the following.

From the Developer Tools icon next to the region, open Cloud Shell and run the script file in the terminal window.
```
adb-create-cred.sh
```
The script discovers whether your Oracle cloud account has an existing API key and fingerprint. If not found, the script creates a key pair for you.
```
You have an existing API Key and Fingerprint! Do you want to try to reuse them? (y/n): y
```
Press y to reuse the existing keys. The script creates your native credential in SQL and JSON files. If you want to generate a new API key pair, press n.
The script offers to generate an AI Profile for your database.
```
Proceed to generate the AI Profile script? (y/n): n
```
In this example, press n to proceed without AI Profile.

Generate IAM Connections for Spreadsheet Add-ins.

Proceed to generate the Spreadsheet add-ins IAM connection? (y/n): y

Press y to continue.

Select the domain with the users that you want to allow to connect to the database instance. Enter the corresponding number in the available domains.
```
Select a domain (1-3):
```
The script creates the following:
- A user custom attribute named Spreadsheet Add-ins RBAC
- A domain integrated application named Spreadsheet Add-ins
- A JWT custom claim
The script also generates a PL/SQL script, create_jwt_profile.sql, containing JWT profile information.

Note:

You can reuse existing user attribute and custom claim or create new ones using the script. If an integrated application already exists, you can create a new one, but any old connections used by the application become invalid.
Run the credential script for the required schema in your Autonomous AI Database.
```
Proceed to run the Credential Scripts on your Autonomous Database? (y/n): y
```
Press y to proceed.

Select the compartment and the database where the schema resides.

Select the number pertaining to your Compartment:
...
Select the number pertaining to your Autonomous Database:

You can use an existing Cloud Wallet or create a new one, if required. If the script discovers an existing Cloud Wallet, it prompts whether you want to reuse it.
```
You have an existing Wallet File for [DB_NAME]. Do you want to reuse it? (y/n):
```
If a wallet does not exist, you can set up a new one.
Specify the login credentials for the schema that you want to allow RBAC to connect.
```
Enter Autonomous Database username (ADMIN):
Enter Autonomous Database password:
```
The script runs the SQL file containing JWT Profile information created earlier and generates an IAM Connections file in JSON format in your home folder.
```
Spreadsheet connection file: [$HOME/iam_connection_[DB_NAME]_[schema].json] created.
```
Note:

You can copy the Connections file name excluding the $HOME/ part and use the same name while downloading the file. If the script is still running, using Ctrl + C to copy might stop or break the script forcefully.
Finally, the script prompts if you want to repeat the steps and create Connections file for another database or exit the script. Thus, you can set up domain user access for multiple schemas.

Download Connections File

In the Cloud Shell window, click the gear icon to open Cloud Shell menu and click Download.
You can paste the same file name copied earlier or specify a new name for the Connections file in JSON format.
Click Download in the message window to download the Connections file.

You can use the Connections file in Spreadsheet Add-ins to connect to your database and share it with other users.

Allow Access for Domain Users

Log in to the Oracle Cloud Infrastructure console, if not already logged in, and do the following.

Click the hamburger menu and go to Identity & Security and select Domains.
Under Name click the domain for which you created the Connections file, for example Default.
Go to the User Management tab and click the username for which you want to allow access.
Click Edit User to modify user information.
Scroll down to the Other information section. In the Spreadsheet add-ins RBAC field enter SQL Developer.
Click Save Changes to add the role to the user.

Starting with ORDS 26.2, ORDS roles are disabled by default. To use an ORDS role, you must enable the role. For example:

BEGIN
    ords.set_property(
      p_key   => 'security.jwt.profile.allowed.roles',
      p_value => 'SQL Developer'
    );
    COMMIT;
  END;
/

For more information on ORDS user roles, see About Oracle REST Data Services User Roles.