Add new comment

We have many fingers in the pie where I work. Silos, silos everywhere. The DBA Group is responsible for operations and performance. ETL Admins are responsible for data movement. For a number of reasons we cannot allow the ETL Admins to log in directory as Oracle user. This is what I came up with to make everyone happy.

What is an ETL admin?

Our ETL Admins are users who do not need DBA or ROOT type access, they just need to run ggsci, start stop processes, edit config files, and check status of how things run.

How do we get there from here?
Basically it involved making a separate but equal GGADMIN user. This user could own the binaries, run processes that interact with the oracle OS user's server processes, and allow ETL Admins access to GGSCI via a sudo configuration by the sysadmins. Did I mention the sysadmins? Yeah they are another group :)

We want them to log into the machine with their own personal account, and then sudo to run /home/ggadmin/ggsci . We also want to leverage the Golden Gate CMDSEC security feature so we can let them run ggsci for admin purposes but not drop to the Unix shell and possibly do any damage.

If we check out the Oracle®
GoldenGate Oracle Installation and Setup Guide e29797.pdf pages 5-6 we see some bullet points about OS user privileges:

• The Oracle GoldenGate Extract, Replicat, and Manager processes must operate as an operating system user that has privileges to read, write, and delete files and subdirectories in the Oracle GoldenGate directory. In addition, the Manager process requires privileges to control the other Oracle GoldenGate processes.DM - make GGADMIN able to read/write files in the GG home directory - hopefully this is a no-brainer
• ...the Extract process reads the redo logs directly and must operate as an operating system user that has read access to the log
  files, both online and archived. On UNIX systems, that user must be a member of the group that owns the Oracle instance. DM - make GGADMIN a member of Oracle/Dba group

Create GGADMIN User

Add a user GGADMIN and make them a member of the same group as Oracle OS account.

The GGADMIN user will own the GG binaries and config files.

In our environment this user is not intended for interactive logins except for by DBA personnel. Remember it is part of the same group as the Oracle/DBA user. If the user can login interactively to GGADMIN user then they can overwrite any files owned by the Oracle user (data files, redo logs, executables, etc). Not that they would, but they can. And that is enough to get managers in a tizzy. So at present only DBAs can log in directly to install/upgrade GG binaries. In production we have the account locked to all unless we are in a maintenance period which requires updating GGADMIN files.

Note: You may need to update the GGADMIN .profile to set appropriate environment variables so it can successfully run ggsci after logon.

Install binaries as GGADMIN

Just follow normal install procedures but logged in as GGADMIN user.

Set up CMDSEC
Set up the CMDSEC file in the GoldenGate home to not allow SHELL command from anyone except Oracle user. Here is an example:

# Command_Name Command_Object  OS_Group  OS_User Allow
STATUS         *               dba       *       YES
START          *               dba       *       YES
STOP           *               dba       *       YES
SHELL          *               *         ggadmin NO
SH             *               *         ggadmin NO
*              *               root      root    YES

Allow sudo access for your employees that play the ETL Admin role

Todo: go over the magic a sysadmin would to do allow Joe User access to /u01/app/oracle/gg_home/ggsci and other executables in the directory. I will have to trek over to the Sysadmin silo to get details on this. But if you tell your sysadmin that JoeUser should be granted access to run /u01/app/oracle/gg_home/ggsci they should be able to hook you up.

Give it a whirl!
Now when JoeUser logs in, they can sudo /u01/app/oracle/gg_home/ggsci and run the command line interface.
Once inside the GGSCI interface JoeUser should be able to run the commands allowed in the CMDSEC file but they will get an error when trying to run SHELL or SH.