Prepare Database Users and Assign Privileges for Oracle GoldenGate for MySQL

Requirements for the database user for Oracle GoldenGate processes are as follows:

  • Create a database user that is dedicated to Oracle GoldenGate. It can be the same user for all the Oracle GoldenGate processes that must connect to a database.

  • To support DDL replication, the MySQL user must have privileges to install the database plug-ins. The required permissions for the plug-in is only required with MySQL 5.7. The INSERT privilege is required on the mysql.plugin system table.

  • To preserve the security of your data, and to monitor Oracle GoldenGate processing accurately, do not permit other users, applications, or processes to log on as, or operate as, the Oracle GoldenGate database user.

  • Keep a record of the database users. They must be specified in the Oracle GoldenGate parameter files with the USERID parameter.

  • The Oracle GoldenGate user requires read access to the INFORMATION_SCHEMA database.

  • The Oracle GoldenGate user requires the following user privileges.

    Privilege Source Extract Target Replicat Purpose

    SELECT

    Yes

    Yes

    Connect to the database and select object definitions

    REPLICATION SLAVE

    Yes

    NA

    Connect and receive updates from the replication master’s binary log

    CREATE

    CREATE VIEW

    EVENT

    INSERT

    UPDATE

    DELETE

    Yes

    Yes

    Source and target database heartbeat and checkpoint table creation, and data record generation and purging

    DROP

    Yes

    Yes

    Dropping a Replicat checkpoint table or deleting a heartbeat table implementation

    EXECUTE

    Yes

    Yes

    To execute stored procedures

    INSERT, UPDATE, DELETE on target tables

    NA

    Yes

    Apply replicated DML to target objects

    DDL privileges on target objects (if using DDL support)

    NA

    Yes

    Issue replicated DDL on target objects
    The MySQL command to grant these user privileges is: 
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, REFERENCES, INDEX, ALTER, SHOW
      DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION
      CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER,
      CREATE ROLE, DROP ROLE ON *.* TO ggadmin WITH GRANT OPTION

    Minimum User Privileges Required for Remote Capture

    The minimum user privileges needed to run Oracle GoldenGate for MySQL remote capture are:

    SELECT, REPLICATION SLAVE, REPLICATION CLIENT, and SHOW VIEW

    The MySQL command to grant the minimum user privileges is:

    GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.* TO ggadmin

    User Privileges Required for Local Capture

    To capture binary log events by the local capture, an Administrator must provide the following privileges to the Extract user:

    • Read and Execute permissions for the directory where the MySQL configuration file (my.cnf) is located.

    • Read permission for the MySQL configuration file (my.cnf).

    • Read and Execute permissions for the directory where the binary logs are located.

    • Read and Execute permission for the tmp directory. The tmp directory is /tmp.