Database integration user privileges

Where to create the integration user

Where you create the user depends on your database type, and the required privileges sometimes also differ by where the database instance is deployed, for example Amazon RDS compared with self-managed infrastructure.

The following sections list the required privileges, and example commands to create the integration user, for each supported database type. Replace integration_user_name and secure_password with your values.

MariaDB

Replace gateways_network_boundary with an IP expression that identifies where the Okta Privileged Access gateways for this integration are, or will be. Entering a single IP address limits the integration to one gateway, and requires you to change this user's grants if you change the gateway. Using only the % wildcard isn't secure, so Okta discourages it.

-- Create the integration user:
CREATE USER 'integration_user_name'@'gateways_network_boundary' IDENTIFIED BY 'secure_password';

-- Required for user discovery:
GRANT SELECT ON mysql.user TO 'integration_user_name'@'gateways_network_boundary';

-- Required for password rotation:
GRANT CREATE USER ON *.* TO 'integration_user_name'@'gateways_network_boundary';
     
GRANT SELECT ON mysql.global_priv TO 'integration_user_name'@'gateways_network_boundary';     
         

Microsoft SQL server

The following commands work for self-managed database instances, Amazon RDS, Google Cloud Platform Cloud SQL, and Azure SQL managed Instance. For Microsoft SQL server on Azure SQL database, see Azure SQL Database.

These commands create the integration user as a login in the master database, which is the scope at which Okta Privileged Access can read and rotate passwords for logins across the instance.

-- Create the integration user in the "master" context:
CREATE LOGIN [integration_user_name]
    WITH PASSWORD = 'secure_password', DEFAULT_DATABASE = master;
GO

-- Required for discovery and password rotation:
GRANT ALTER ANY LOGIN TO [integration_user_name];
GO

-- Required to reject read-only instances:
GRANT VIEW SERVER STATE TO [integration_user_name];

-- Required to read the server fingerprint, which identifies the instance
-- and prevents duplicate integrations to it:
GRANT VIEW ANY DATABASE TO [integration_user_name];

Azure SQL database

Azure SQL database uses a contained database model and doesn't provide instance-level access, so server-level commands fail. Run the following commands inside the app database rather than master.

-- Create a contained integration user directly in the database:
CREATE USER [integration_user_name] WITH PASSWORD = 'secure_password';
GO

-- Required for rotating passwords:
GRANT ALTER ANY USER TO [integration_user_name];
GO

-- Required to protect against integration with read-only instances:
GRANT VIEW DATABASE STATE TO [integration_user_name];

Run the following command inside the master database of the Azure SQL database to protect against duplicate integrations:

ALTER SERVER ROLE [##MS_DefinitionReader##]
    ADD MEMBER [integration_user_name];

MongoDB

Create a custom role on the admin database, then grant it to the integration user. Replace authentication_db with the authentication database that this integration uses, and user_authentication_db with the database where the integration user is defined.

// 1. Create the role on the 'admin' database (required for cluster and cross-database access):
db.getSiblingDB("admin").createRole({
  role: "opaIntegrationRole",
  privileges: [
    // Required to discover users and rotate passwords on the target database:
    { resource: { db: "authentication_db", collection: "" }, actions: [ "viewUser", "changePassword" ] },
    // Required for preventing integration with a read-only replica:
    { resource: { cluster: true }, actions: [ "replSetGetConfig" ] },
    // Required to reliably identify the instance and prevent duplicate integrations:
    { resource: { db: "config", collection: "version" }, actions: [ "find" ] }
  ],
  roles: []
});

// 2. Grant the role to the user on the user's authentication database:
db.getSiblingDB("user_authentication_db").grantRolesToUser("integration_user_name", [
  { role: "opaIntegrationRole", db: "admin" }
]);

MySQL

Replace gateways_network_boundary with a CIDR block, subnet mask, or IP wildcard expression that corresponds to where the Okta Privileged Access gateways for this integration are, or will be. Entering a single IP address limits the integration to one gateway, and requires you to change this user's grants if you change the gateway. Using only the % wildcard isn't secure, so Okta discourages it.

-- Create the integration user:
CREATE USER 'integration_user_name'@'gateways_network_boundary' IDENTIFIED BY 'secure_password';

-- Required for user discovery:
GRANT SELECT ON mysql.user TO 'integration_user_name'@'gateways_network_boundary';
GRANT SELECT ON mysql.role_edges TO 'integration_user_name'@'gateways_network_boundary';
GRANT RELOAD ON *.* TO 'integration_user_name'@'gateways_network_boundary';

-- Required for password rotation:
GRANT CREATE USER ON *.* TO 'integration_user_name'@'gateways_network_boundary';
GRANT CREATE ROLE ON *.* TO 'integration_user_name'@'gateways_network_boundary';
GRANT CONNECTION_ADMIN ON *.* TO 'integration_user_name'@'gateways_network_boundary';
GRANT ALL PRIVILEGES ON `target_db`.* TO 'integration_user_name'@'gateways_network_boundary' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Oracle database

Oracle database integrations are done at the container level and don't provide access management to child PDBs. Connect directly to the target container, which is CDB$ROOT, an app container, or a PDB, for which you intend to create an integration, and then run the following commands there.

-- Create the integration user:
CREATE USER integration_user_name IDENTIFIED BY "secure_password";

-- Allow connection using this user:
GRANT CREATE SESSION TO integration_user_name CONTAINER=CURRENT;

-- Required for user discovery, for preventing duplicate integrations,
-- and for preventing integration with read-only instances:
GRANT SELECT_CATALOG_ROLE TO integration_user_name CONTAINER=CURRENT;

-- Required for password rotation:
GRANT ALTER USER TO integration_user_name CONTAINER=CURRENT;

For CDB integrations, run the following commands in the CDB$ROOT container, or in the app root container for an app container. The only difference from the preceding commands is CONTAINER=ALL, which Oracle requires because a common user's privilege state must be consistent across every container in the CDB:

CREATE USER c##integration_user_name IDENTIFIED BY "secure_password" CONTAINER=ALL;
GRANT CREATE SESSION,
      SELECT_CATALOG_ROLE,
      ALTER USER,
      SET CONTAINER
   TO c##integration_user_name CONTAINER=ALL;

-- Required to open visibility into every PDB's rows in CDB-wide dynamic views
-- (V$PDBS, V$SESSION, V$INSTANCE)
ALTER USER c##integration_user_name SET CONTAINER_DATA = ALL CONTAINER = CURRENT;

PostgreSQL

-- Create the integration user:
CREATE USER integration_user_name WITH PASSWORD 'secure_password';

-- Superuser is required in order to change password for any user
-- Use this for self-hosted:
ALTER USER integration_user_name WITH CREATEROLE;
ALTER USER integration_user_name WITH SUPERUSER;
-- Use this if DB is on Amazon RDS:
-- GRANT rds_superuser TO integration_user_name;

Okta Privileged Access needs superuser privileges on PostgreSQL. A PostgreSQL user can only change the password of a user whose role it administers, so without superuser you would have to list every managed account in the integration user's grants and update them each time an account is added. Superuser lets Okta Privileged Access rotate the password for any existing or future account on the instance.

What to do next

After you create the integration user, you can install the Okta Privileged Access gateway and configure it to support database integrations.