Golden Gate - OS User Permissions on *nix - DBA vs ETL Admin
Submitted by dave on Mon, 11/14/2011 - 13:11
Seems like Golden Gate is following me around. I used their tools in 2005 to replicate from OLTP databases to a central reporting database for my previous employer. Now that Oracle bought them the buzz in my current company is to leave Oracle's multiple and complicated incarnations of replication technologies in the dust and try moving forward with GG. Fine with me, I like the product, not a fan of the licensing as I think Oracle has been on the lagging side of vendor supplied replication tools, but hey what do I know, I just arrange zeroes and ones for a living. In my previous company the DBA did all of the Golden Gate config. This made things easy. Log in as oracle user to install Oracle RDBMS binaries, to install GG binaries, and to config/start/stop processes. At my current company the Silos are popping up daily it seems and we will have ETL admins who will engage in the care and feeding of Golden Gate. The DBA team concentrates on installation, operation, and performance issues. The current documentation for Golden Gate notes that the GG binaries must be owned by the same group that owns the Oracle binaries. (See Oracle Installation and Setup Guide -> Operating System Requirements -> Operating System Privileges section) This implies that even if I install GG as a different user (say ggadmin vs oracle user), they can still muck around with sensitive Oracle files since they will be part of the dba group. For our environment we defined 'mucking' as using the GGSCI SHELL command to drop to the OS. Here is how we are preventing that:
1) Create a ggadmin OS user:This user will own the GG binaries. This user can read/write their own files of course. Add them to dba group to satisfy installation requirements. Reset the password, lock the account, or otherwise disable the account for interactive logins.
2) Create a CMDSEC file in the Golden Gate home directory:
# GG Command Security File # command name / command object / OS group / OS user / YES | NO SHELL * * ggadmin NO SH * * ggadmin NOI suppose you could lock this file down and make it Readable by everyone but Writable only by Root for an added layer of 'warm fuzzies'. Basically this tells GGSCI that the ggadmin user it not allowed to run the SHELL command or SH shortcut.
3) Set up sudoGrant sudo to your ETL Admin's user account so they can run /home/ggadmin/ggsci. Your ETL User has their own Operating System user right? - wink wink nudge nudge - You may need to set up variables for your Oracle environment in the .profile for ggadmin user. This is what I have set up in our environment:
export TNS_ADMIN=/u01/app/oracle/network export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/220.127.116.11 export ORACLE_SID=ggpocsrc export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib
4) Try it out:
ssh etladminuser@mymachine sudo –u ggadmin ggsci Oracle GoldenGate Command Interpreter for Oracle Version 18.104.22.168 OGGCORE_11.1.1_PLATFORMS_110421.2040 Linux, x64, 64bit (optimized), Oracle 11g on Apr 21 2011 22:42:14 Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved. GGSCI (ggsource) 1> sh cat /etc/passwd ERROR: Command not authorized for this user.So there you have it. The ETL Admin can now get to GGSCI but they lose interactive use of the SHELL/SH command. If they happen to have scripts that leverage the SHELL/SH command I can log in as the oracle user and run the script through ggsci for them since oracle user does not have the SHELL/SH restriction set in GGSCI.