RE: Oracle lockdown, agents, and EM Jobs

From: Herring, David <HerringD_at_DNB.com>
Date: Mon, 23 Jun 2014 11:12:40 -0500
Message-ID: <AD8FE6616C097545A4C9A8B0792909AC3838616B30_at_DNBEXCH01.dnbint.net>



Pete,

Thanks for jumping in and being willing to pull in others for more input. I'm sure there is plenty I haven't considered yet.

Right now I've got 2 outstanding items that I'd love to hear how others are addressing:

  1. Management agent user. I've been told mulitple times to NOT use "oracle" for installing the agent. What account are others using? Are you just creating something like "ora-agent" and then setting GID to "oinstall"?
  2. How are you handling user equivalency? We're not allowed to connect directly as "oracle" nor have a shell as "oracle". On RAC Oracle's need for user equivalency goes against this since for installs, cluster tests, etc. Oracle needs connections to all servers in the cluster without password. I explained this to our security team, had to give links to install documentation and eventually this was allowed. Of course in allowing user equiv. now I can connect as "oracle" on other nodes once I connect to the cluster. Our SA said there's no way around this. This seems to be a major hole. Has anyone addressed this or perhaps is our SA missing something?

Dave Herring

-----Original Message-----
From: Peter Sharman [mailto:pete.sharman_at_oracle.com] Sent: Thursday, June 19, 2014 4:09 PM
To: Herring, David; Seth Miller
Cc: dedba_at_tpg.com.au; oracle-l_at_freelists.org Subject: RE: Oracle lockdown, agents, and EM Jobs

Folks

Just to let you know I have asked our security PM for comment on all of this as well. Unfortunately both she and I are in the midst of a lot of work for a meeting all next week so it may take some time to get back to you on this. Two quick points I would add in the meantime:

  1. Seth's comments are pretty much spot on, as I would expect. :) 2. I would just add to Tony's comment about the overzealous security. I know this is a hard one to take on, Dave, but have they given you a reason for the -u? That pretty much is the toughest issue to work around and I really don't see a reason for doing that other than making everybody's life a damn sight more difficult than it should be. :(

Pete

Pete Sharman
Principal Product Manager
Enterprise Manager Product Suite
33 Benson Crescent CALWELL ACT 2905 AUSTRALIA Phone: +61262924095 | | Fax: +61262925183 | | Mobile: +61414443449 

"Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook

"Oh no, it's not, it's much harder than that!" Bruce Pihlamae, long term Oracle DBA

-----Original Message-----
From: Herring, David [mailto:HerringD_at_DNB.com] Sent: Friday, June 20, 2014 6:42 AM
To: Seth Miller
Cc: dedba_at_tpg.com.au; oracle-l_at_freelists.org Subject: RE: Oracle lockdown, agents, and EM Jobs

> $AGENT_HOME/bin/emctl needs to be run as the owner of the executable.
> It does not and should not be owned by oracle. It is very easy to change the owner of the agent and since the agent has no authority to do anything outside of itself without sudo, there should be no problem with giving everyone access.

[herringd]: Out of curiosity, what account name do you always install the agent under? And is it's GID the same as for all DBA accounts? Deinstalling/reinstalling agents isn't a huge deal. As long as I can clearly explain why we need a shared account on each server I could do this for each server converted to the new rules. I'll need to dig up doc where this is specifically recommended since the security won't budge unless I can prove the request with documentation from Oracle.

> Do you not use password files? Why would you need access to the dba
> group for sysdba? Once you move into multi-tenant, os authentication goes away for PDBs so you may want to ween yourself off of that anyway. Using the oracle user to login as sysdba also has the added consequence of not being able to track actions against users in the database.

[herringd]: yes, we use password files and I assume your question was somewhat rhetorical in that you're expecting all my responses on this. Rarely do we use SYSDBA and definitely not in any scripts. It'd be great if every database followed a standard of having a DBA account that's been granted SYSDBA. As for the tracking issue, I'm sure that was one of the reasons for group "dba" 's removal.

> Sudo has a parameter called "env_reset". Disabling this parameter is less secure than leaving it enabled. Has your Unix group explained why they are denying you this parameter?

[herringd]: I've never asked but my guess is the added complexity. If there ever is a real reason to run something as "oracle", that command/binary would need a wrapper since the environment under which it'd execute would be bare. And unfortunately we don't have a wrapper like this yet.

Dave Herring

From: Seth Miller [mailto:sethmiller.sm_at_gmail.com] Sent: Thursday, June 19, 2014 1:14 AM
To: Herring, David
Cc: dedba_at_tpg.com.au; oracle-l_at_freelists.org Subject: Re: Oracle lockdown, agents, and EM Jobs

David,

$AGENT_HOME/bin/emctl needs to be run as the owner of the executable. It does not and should not be owned by oracle. It is very easy to change the owner of the agent and since the agent has no authority to do anything outside of itself without sudo, there should be no problem with giving everyone access.

Do you not use password files? Why would you need access to the dba group for sysdba? Once you move into multi-tenant, os authentication goes away for PDBs so you may want to ween yourself off of that anyway. Using the oracle user to login as sysdba also has the added consequence of not being able to track actions against users in the database.

Sudo has a parameter called "env_reset". Disabling this parameter is less secure than leaving it enabled. Has your Unix group explained why they are denying you this parameter?

Seth

On Wed, Jun 18, 2014 at 9:58 PM, Herring, David <HerringD_at_dnb.com> wrote: Seth,

Now I feel REALLY bad because I had such grand plans for blogging yet never seem to find time for it.  I was just glad to finally read through the lastest posts on this list!

In terms of what needs "sudo" and what doesn't, I'm not so sure things are as clean.  Nearly everything was installed over time using defaults, so at this point I'm assuming permissions and ownership hasn't been messed with.  But what I've found is even simple things like $AGENT_HOME/bin/emctl needs to run as "oracle".  Most binaries under $ORACLE_HOME are fine but our team has been denied group "dba", so we have to run as "oracle" to use "sysdba".  I'll definitely have to find time or rope another DBA into reviewing our scripts to see which commands within them really need to run as "oracle" and then comment them accordingly so that it's documented which way is required.

Concerning "sudo" and variables, we're only allowed "sudo -u oracle <cmd>".  So it's not whether or not sudo is set up right but what we've given.  This method runs the command as "oracle" but doesn't change the environment so the whole inheritance of settings comes in to play.  This has caused some rather confusing debugging sessions for me, such as when I restarted an agent and my env had a custom setting for $TNS_ADMIN.  "% strings /proc/<agent pid>/environ" becomes very handy in this case!

As for the credential comment to Tony, this is again a security issue.  We're not allowed a shared/common account at the OS.  That means if credentials are needed at the OS level then they must be individual DBA credentials.  I at least have been allowed to have a common EM admin so this user then owns everything in the Job Library and is used for all submitted Jobs.  But this is a good reminder to me to see what alerts can be sent us from the RH LDAP server on upcoming password expirations.

Dave Herring

From: Seth Miller [mailto:sethmiller.sm_at_gmail.com] Sent: Wednesday, June 18, 2014 8:47 PM
To: dedba_at_tpg.com.au
Cc: Herring, David; oracle-l_at_freelists.org Subject: Re: Oracle lockdown, agents, and EM Jobs

David,

Kudos to your organization for taking this on. I hope you will share this experience with the community through blogs and presentations. I could see these topics easily being accepted as a presentation at any of the conferences.

With the exception of a very few (and by very few I mean less than five) situations, there should not be any scripts that need to run as oracle. I am including the EM agent as one of those things that does not require oracle. Those few things that are required to be run as oracle should be done through sudo. If executing something using sudo is leaving environment variables in place that shouldn't be there, then sudo is not set up properly.

Your messages were quite complex but you are asking very good questions here. I suggest you break it down into one or two issues per post and hash them out before moving to the next issues.

Tony,

You're quite right that jobs that need to persist across employees should never be run with credentials of an individual. Jobs should be run under a common system user. As long as no one can use this user for anything other than jobs (no local access to the servers), security would not be an issue since authorization and auditing is taken care of with EM. If separation of duties is the issue, each individual could have their own system user that could just be reassigned to someone else if necessary.

Seth

On Wed, Jun 18, 2014 at 7:59 PM, De DBA <dedba_at_tpg.com.au> wrote: This is becoming a very interesting story indeed. How about putting the common oracle environment in a file in a common location, e.g. /var/opt/oracle/oraenv.sh, and having each script and perhaps .bashrc/.bash_profile source that as appropriate? Access to this directory should already be allowed. We tend to do this, even where security is not as overzealous as what you're facing. It just makes managing environments more easy when everything is in one common location, whilst allowing DBAs to choose whether to use the common environment or not.

I wonder how you are going to set up EM jobs that need os logon credentials. If you use a DBA's credentials, then those jobs will start to fail when the DBA in question leaves the organisation and his account gets disabled or removed from the directory.. Could be an administrative nightmare waiting to happen?

Cheers,
Tony

On 19/06/14 08:18, Herring, David wrote: Options:

  1.  Every DBA has their own .bash_profile created as a copy of Oracle's.  This is what I started with thinking it was simple but after one week I've found 2 examples where DBAs didn't bother to follow this rule.  I can't control them and have no authority to force them to do anything other than the bare minimum of their job.
  2.  Open permissions on /user/oracle (710) and /user/oracle/.bash_profile (750) so that each EM Job could issue at the start: ". ~oracle/.bash_profile".  So whether daveh or markb or timg have their credentials set for the target, all can execute Oracle's .bash_profile and we're fine.

Everything else I've come up with is a variation on the above 2 options, either modifying DBA .bash_profiles in some way or something within Oracle's home directory that requires permissions to be relaxed a bit.

Does this make sense?

Am I making this overly complex?

Anyone else in a similar situation and have a better solution?

Dave Herring
--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Mon Jun 23 2014 - 18:12:40 CEST

Original text of this message