Re: "oracle" lockdown

From: De DBA <dedba_at_tpg.com.au>
Date: Thu, 27 Feb 2014 12:23:49 +1000
Message-ID: <530EA1B5.2060208_at_tpg.com.au>

The database will run just fine, I suppose. But here's something that I tend to run into on Linux servers (all distros) where login into the oracle (or just a shared) account seems to be required (listing from Debian Wheezy, all oracle variables set - this is on my test box):

$ groups
... dba

$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-FEB-2014 11:48:08

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) TNS-01190: The user is not authorized to execute the requested listener command

## This error is because the tnslistener currently runs under another account, oracle in this case.

$ sudo -u oracle lsnrctl
sudo: lsnrctl: command not found

$ su - oracle
Password:

$ which lsnrctl
/u00/oracle/11g/bin/lsnrctl

$ logout
## Note that Oracle has the complete set of ENV variables set when logged into the shell!

$ sudo -u oracle `which lsnrctl` stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-FEB-2014 11:50:00

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Message 1053 not found; No message file for product=network, facility=TNSMessage 1052 not found; No message file for product=network, facility=TNS

$ sudo -u oracle `which lsnrctl` start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-FEB-2014 11:50:07

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Message 1070 not found; No message file for product=network, facility=TNSTNS-12545: Message 12545 not found; No message file for product=network, facility=TNS   TNS-12560: Message 12560 not found; No message file for product=network, facility=TNS    TNS-00515: Message 515 not found; No message file for product=network, facility=TNS     Linux Error: 2: No such file or directory

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 27-FEB-2014 11:50:16

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) TNS-12541: TNS:no listener
  TNS-12560: TNS:protocol adapter error
   TNS-00511: No listener
    Linux Error: 2: No such file or directory Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wallaroo.internal.skyrider)(PORT=1521))) TNS-12541: TNS:no listener
  TNS-12560: TNS:protocol adapter error
   TNS-00511: No listener
    Linux Error: 111: Connection refused

The listener is stopped, but won't start using sudo. Of course it can now be started using the dba account, but that will prohibit other accounts (including the oracle account) from stopping it later. Note that he necessary environment is not set when using "sudo -u oracle", nor is it inherited from the calling shell. This causes the Oracle message files not found errors, amongst other things. I'm sure there are more interesting & varied problems with the java-based tools... java security limitations for instance - think of enforcing running executables by the "owner" only.

To start the listener from OEM, if memory serves well, a login shell (via ssh) is needed...

Cheers,
Tony
/
/
On 27/02/14 11:27, Hans Forbrich wrote:
> I'm not sure what the problem is - Oracle database was designed to be administered by administrators using their own OS userid with checks that they are in the appropriate Administration OS group and this has been available since half of forever.
>
> Other than installation and some patching, there generally should be no reason to log in as Oracle.
>
> <rant>
> Indeed, the common habit (by many shops I visit) for DBAs to log in as user Oracle is terrifying from a security perspective - everyone in the DBA team, and potentially others, has the keys to the kingdom and there is absolutely no non-repudiation. No way at all to tell which admin did which command; everyone scrambling to get the changed password; getting an exception to the corp password change lifecycle policy; etc. And when a DBA leaves the group, the common-knowledge super user password needs to be changed, but often is not ...
>
> Even in my lab, I have a non-'oracle' Oracle Administrator which I use for most admin (which seems silly in a one-man lab).
>
> There is a reason we are forced to run root.sh during installation - mainly to chown files and setGID and config for 'sysdba' and 'sysoper' - to support DBAs and other support personnel using their own IDs. With 12c, they have given us additional groups to further refine the granularity.
>
> ASM and GI take this a major step forward as well. In big shops with division of responsibility, that can be very useful although for small shops it get very cumbersome - "to run this I log in as A, but to run that I log in as B'. At that point Cloud Control and it's security model becomes very interesting.
>
> A secondary variation on this - I also install an ORACLE_HOME specifically for the Oracle Client (sqlplus, etc) under user 'oraclient' (under a different tree - /u01/app/oraclient) which is not in OSDBA group to support batch jobs and non-admin users who need access to the machine. Users who log in then set up the environment for that and use Oracle Networking tcp, rather than bequeath adapter. That, however, can be a challenge for some things.
> </rant>
>
> I would encourage the OP to set up a test user in the OSDBA group and check out the core scripts they run. Based on my experience, most of them should run just fine as long as the user is created properly and the environment is set up properly. I'd be interested in hearing under which circumstances this fails.
>
> /Hans
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Thu Feb 27 2014 - 03:23:49 CET