Oracle DB

JDBC Connection String

The Oracle DB connection string can take two formats. If you will be using the SID the format is

jdbc:oracle:thin:@<host>:<port>:<SID>

If you will be using the service name of the database, the format is

 jdbc:oracle:thin:@//<host>:<port>/<service>

Validate Credentials

SELECT 
    username as integration_specific_id,
    username,
    username as email,
    CASE WHEN account_status = 'LOCKED' THEN 'INACTIVE' 
         WHEN account_status = 'EXPIRED' THEN 'DEPROVISIONED' 
         ELSE 'ACTIVE' END as user_status,
    CASE WHEN profile = 'DEFAULT' THEN 'USER' 
         ELSE 'SERVICE' END as account_type 
FROM dba_users 
WHERE ROWNUM = 1

List Accounts

SELECT 
    username as integration_specific_id,
    username,
    username as email,
    CASE WHEN account_status = 'LOCKED' THEN 'INACTIVE' 
         WHEN account_status = 'EXPIRED' THEN 'DEPROVISIONED' 
         ELSE 'ACTIVE' END as user_status,
    CASE WHEN profile = 'DEFAULT' THEN 'USER' 
         ELSE 'SERVICE' END as account_type 
FROM dba_users

List Entitlements

SELECT 
    'ROLE_' || role as integration_specific_id,
    SYS_CONTEXT('USERENV', 'DB_NAME') as integration_specific_resource_id,
    'ROLE' as entitlement_type,
    'Role - ' || role as label,
    1 as is_assignable
FROM dba_roles

Find Entitlement Associations

📘
If you are using a version of Oracle DB older than 12.2, you can only use identifiers that are 30 characters or less. In that case use integ_specific_entitlement_id and integ_specific_resource_id as the field names for this query.

SELECT 
    grantee as account_id,
    'ROLE_' || granted_role as integration_specific_entitlement_id,
    SYS_CONTEXT('USERENV', 'DB_NAME') as integration_specific_resource_id
FROM dba_role_privs

List Resources

SELECT 
    SYS_CONTEXT('USERENV', 'DB_NAME') as integration_specific_id,
    SYS_CONTEXT('USERENV', 'DB_NAME') as label,
    'DATABASE' as resource_type
FROM dual

📘
For write queries containing DDL (creating or altering users) the parameterization syntax requires that identifiers that should not be contained within quotes are suffixed with an !. In the example below, the parameters email and id need to have an ! after them to work correctly because they are identifiers that should not be quoted in the SQL DDL statement. In the example distinguished_name should be passed as a string literal in the query so it should not have the ! suffix

Create Account

CREATE USER {email!} IDENTIFIED BY TempPass123 PASSWORD EXPIRE

Update Account

ALTER USER {id!} IDENTIFIED EXTERNALLY AS {distinguished_name}