Monday, May 2, 2011

What a OS user can do if it inside a OSDBA group

Setup:


[oracle1@vmxdb01 ~]$ ps -ef|grep tnslsnr|grep -v grep
oracle 1953 1 0 18:03 ? 00:00:00 /u01/app/oracle/product/11.2.0.2/db_1/bin/tnslsnr LISTENER -inherit
[oracle1@vmxdb01 ~]$ ps -ef|grep smon|grep -v grep
oracle 2041 1 0 18:03 ? 00:00:00 ora_smon_orcl

[oracle1@vmxdb01 ~]$ id oracle1
uid=501(oracle1) gid=100(users) groups=100(users),501(dba)

[root@vmxdb01 ~]# id oracle
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)


The user can stop and start the database instance through OS authentication


[oracle1@vmxdb01 ~]$ export ORACLE_SID=orcl
[oracle1@vmxdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon May 2 18:22:23 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle1@vmxdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon May 2 18:22:46 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 627732480 bytes
Fixed Size 1345992 bytes
Variable Size 411043384 bytes
Database Buffers 209715200 bytes
Redo Buffers 5627904 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle1@vmxdb01 ~]$ ps -ef|grep smon
oracle 3371 1 0 18:22 ? 00:00:00 ora_smon_orcl
oracle1 3477 3185 0 18:23 pts/1 00:00:00 grep smon


The user can NOT stop listener, because listener was started by user "oracle".
Even change the primary group to "oinstall", it still failed to stop the listener.



[oracle1@vmxdb01 ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 02-MAY-2011 18:23:25

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmxdb01.lab.dbaglobe.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 02-MAY-2011 18:03:13
Uptime 0 days 0 hr. 20 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/vmxdb01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmxdb01.lab.dbaglobe.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmxdb01.lab.dbaglobe.com)(PORT=1521)))
TNS-01190: The user is not authorized to execute the requested listener command


"root" user is able to stop the listener, when starting the listener using "root", the listener run as "daemon" user, instead of oracle, so the listener should always started by the software owner of the oracle home.