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

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