User Management in GoldenGate: Exploring Aliases, Certificates, Roles, and Privileges
In the GoldenGate Microservice Architecture, communication between services relies on REST calls. This applies regardless of whether it's a WEB UI, AdminClient, or any other script or programming language that directly incorporates REST calls, such as curl or Python. All REST calls require Authentication (AuthN) and Authorization (AuthZ).
For the Authentication (AuthN), you have the following choices:
Currently, there is IDCS/OCI IAM for an Oracle Cloud and OAM as an On-Premises solution. With those solutions the general password management policy is part of the external IdP. This blog does not cover external Identity Providers in detail. Additional blogs are available if you are interest in this topic.
For the Authorization (AuthZ), there are fixed hierarchical roles that can be used:
This blog walks you through major deployment and configuration topics while having a focus on user management within GoldenGate. We’ll delve into the authentication and authorization, exploring the following topics:
GoldenGate Deployment and Administrator Accounts
When you first deploy a GoldenGate, you'll set up two deployments: the Service Manager and the User Deployment. Each deployment has its own security user, and it's essential to define the credentials (username and password) for the Service Manager Security user.
The credentials (username/password) you provide are used to connect to GoldenGate. In the Microservice Architecture, you can connect remotely to the GoldenGate using the WEB-UI, AdminClient commands, or REST API calls. This represents a significant advantage over the legacy GoldenGate Architecture, where connections had to be made directly on the server using the ggsci program.
The Security User for the Service Manager holds the highest level of authority, functioning as a "Super User" with extensive control. This role manages the services for each user deployment. You can either use the same credentials across all deployments or choose to set up unique credentials for each one.
Each user deployment includes a Security user, which you define during the deployment process. With the GoldenGate Micro Service Architecture, additional Administrator Accounts are introduced to manage the deployment. This new feature means that, in addition to Database accounts, there are now specific Administrator Accounts within the GoldenGate deployment itself.
After deploying the GoldenGate, you can add more users directly through the WEB-UI, which includes a dedicated User Management section.
You also have the option to manage users by using REST API calls directly.
Here are three engaging ways to handle user authentication:
|
Let me introduce you to a compelling feature that strengthens security by helping you avoid using clear text passwords in AdminClient scripts. If you are connecting to the GoldenGate with AdminClient, you are usually prompted by a password: |
linux> adminclient
OGG (not connected)> CONNECT https://server-north:8000 DEPLOYMENT ServiceManager AS ggma
Password for 'ggma' at 'https://server-north:8000': ****
OGG (https://server-north:8000 ServiceManager)> INFO ALL
Deployment Name Service Name Service Status
depl_north ADMINSRVR RUNNING
depl_north DISTSRVR RUNNING
depl_north PMSRVR RUNNING
depl_north RECVSRVR RUNNING
OGG > CONNECT https://server-north:8000 DEPLOYMENT depl_north AS ggma
Password for 'ggma' at 'https://server-north:8000': ***
OGG (https://server-north:8000 depl_north)> INFO ALL
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
RECVSRVR RUNNING
PMSRVR RUNNING
As you see from the previous output, you will be prompted for the password. Theoretically, you could also use the username/password within the connect command, such as
OGG> CONNECT https://server-north:8000 DEPLOYMENT depl_north AS ggma password *** !
However, using passwords in clear text within scripts is not a good idea. For AdminClient, there is the ADD CREDENTIALS command where you store the credentials in $HOME/.goldengate
OGG> ADD CREDENTIALS credentials_depl_north USER ggma
Password: ****
OGG> CONNECT https://server-north:8000 DEPLOYMENT depl_north AS credentials_depl_north !
In addition to authentication, GoldenGate offers various authorization options through Role-Based Access Control (RBAC).
The user with the Security role, as previously discussed, holds the highest level of privilege, while the User role is more limited, typically restricted to "information-only" services. I find Role-Based Access Control (RBAC) particularly effective because it allows precise management of user privileges. For example, junior team members may be assigned the User role, more experienced team members the Operator role, and senior team members the Administrator role. This approach ensures that each user has access tailored to their responsibilities and expertise.
In addition to managing operational and administrative users, user management also extends to the Distribution Path. Typically, you’ll use either the secure Web Socket protocol (WSS) or the standard Web Socket protocol (WS). There is also an legacy OGG protocol for connecting with classic GoldenGate architectures, though this Blog does not cover that aspect.
For the cases using UserIDAlias, I want to highlight that in GoldenGate 23ai, there is a new additional section about Path Connections.
At this point, the concept of the GoldenGate users should be clear. Now, I would like to head over to the Database user Management. Here, I am focusing on the Oracle Database.
Database Users for GoldenGate and the connection to the database
Within the Oracle Database, you need a privileged GoldenGate Administration user. I usually call the database GGADMIN (BTW: this is also the preexisting user account on autonomous databases that you must unlock if you use it). This Administration user needs specific privileges in the database as it captures changes at the source and applies them to the target. Since Database 23ai, you grant privileges with a SQL statement – in the simplest case, grant the database role to the user:
SOURCE_SQL> GRANT ogg_capture TO ggadmin;
TARGET_SQL> GRANT ogg_apply TO ggadmin;
You used the PL/SQL package/procedure DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGES in earlier Oracle Database releases at source and target.
As Replicat is applying changes to the base business schema at the target database, you also need privileges for DML, and depending on whether you are also using DDL Replication, DDL operations are required.
|
Within Database 23ai, there is a new feature of Schema privileges that is suitable for schema-wise Replication: |
TARGET_SQL> GRANT SELECT ANY TABLE ON SCHEMA hr TO ggadmin;
TARGET_SQL> GRANT INSERT ANY TABLE ON SCHEMA hr TO ggadmin;
TARGET_SQL> GRANT UPDATE ANY TABLE ON SCHEMA hr TO ggadmin;
TARGET_SQL> GRANT DELETE ANY TABLE ON SCHEMA hr TO ggadmin;
TARGET_SQL> GRANT CREATE ANY TABLE ON SCHEMA hr TO ggadmin;
TARGET_SQL> GRANT ALTER ANY TABLE ON SCHEMA hr TO ggadmin;
TARGET_SQL> GRANT DROP ANY TABLE ON SCHEMA hr TO ggadmin;
TARGET_SQL> GRANT CREATE ANY INDEX ON SCHEMA hr TO ggadmin;
TARGET_SQL> GRANT ALTER ANY INDEX ON SCHEMA hr TO ggadmin;
TARGET_SQL> GRANT DROP ANY INDEX ON SCHEMA hr TO ggadmin;
With the new schema privilege concept, you can decide how to control privileges on the database, schema, or object level.
In general, privileges are managed within the database. Let's focus on the GoldenGate user now. A USERIDALIAS manages any database connection from GoldenGate to the database. For fetch operations or DownStream Database, the FETCHUSERIDALIAS or MININGDBUSERIDALIAS comes into play. In every configuration, there is no way to connect directly with a USERID/PASSWORD. Using any USERID/PASSWORD was never recommended and is even desupported in GoldenGate 23ai. With the deployment of GoldenGate, a CREDENTIALSTORE is already existing, and you have to add the database user with the ALTER CREDENTIALSTORE command:
OGG> ALTER CREDENTIALSTORE
ADD USER ggadmin@dbnorth
ALIAS ggnorth DOMAIN OracleGoldenGate
[PASSWORD ***] !
If you don't use the password option within the AdminClient command, you will be prompted to add it secretly. The USERIDALIAS can be used within the AdminClient commands (DBLOGIN) or within the parameter files for Extract and Replicat.
Within this blog, I discussed the user management for GoldenGate deployment which is new in the Micro Service Architecture and the user connecting to the database. Both, authorization (how to validate the identity) and authorization (which privileges the user has) are described. For further information, you might check the GoldenGate 23ai documentation.
Volker is a Senior Principal Product Manager working in the GoldenGate Development group.
His primary focus is on the GoldenGate Core Product, mainly GoldenGate for Oracle. Key topics are Performance, High Availability, Security, and Resilience.
Volker has worked for more than 20 years in the field of database technology and data replication.
He has supported customers worldwide in different industries to develop & manage distributed database systems/applications and build Data Integration Solutions.
Previous Post
Next Post