I have successfully set up a (test) environment for single-instance Grid Infrastructure and Oracle database using job role separation. So I have the recommended grid and oracle users, and the oinstall, dba, oper, asmadmin, asmdba and asmoper groups. I have the following directory structure for my Oracle Bases and Oracle Homes:
/u01/app/11.2.0/grid - GI home
/u01/app/grid - GI base
/u01/app/oracle - DB base
/u01/app/oracle/product/11.2.0/db_1 - DB home
Platform is OEL5, 188.8.131.52 GI and DB.
This all works fine.
What I now want to do (since what I'm trying to do is make this environment as secure as practical) is set up an additional sysoper operating system account, so that that user can connect to carry out sysoper tasks, amongst other things stopping and starting the instance.
So (as the oracle user) I do the following:
$ sqlplus / as sysdba
SQL> create user test identified by passwrod;
SQL> grant sysoper to test;
As root I do the following:
$ useradd -g oper testoper
$ su - testoper
Now as testoper:
$ sqlplus 'test/password as sysoper'
SQL> shutdown immediate
ORACLE instance shut down.
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/ORAX/spfileORAX.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/ORAX/spfileORAX.ora
ORA-27300: OS system dependent operation:open failed with status: 13
ORA-27301: OS failure message: Permission denied
ORA-27302: failure occurred at: sskgmsmr_7
As you can see, shutdown works, but startup doesn't. I have registered an SR with Oracle on this, but they're not being helpful. The suggestion was that I should give testoper the oinstall secondary group. But this is not a secure solution as this now gives testoper priveleges to do things in OB / OH that it really shouldn't be able to do. No other suggestions have been forthcoming. The reason for the suggestion however is the ownership / permissions on the Grid OH oracle executable:
[root@db03 ~]# ls -la /u01/app/11.2.0/grid/bin/oracle
-rwsr-s--x 1 grid oinstall 184286237 Aug 22 11:15 /u01/app/11.2.0/grid/bin/oracle
As you can see, it has group oinstall, so you can see why giving the user oinstall group would work. But in my view this is not satisfactory.
So, does anyone have any suggestions on this? Am I trying to do something unreasonable? One thing that occurred to me is that in this environment I should arguably in fact be using srvctl to stop / start instances. But that means setting up a user with asmoper role (presumably) rather than oper. That user would then (again presumable) be able to stop and start other GI resources, which is not what I want.
Does anyone have any thoughts (and ideally experience!) of how one should expect to manage this sort of environment, or resolution to the above problem?
In order to achieve what you want, I'd go for setting up sudo correctly where you can specify exactly what
a user may and may NOT do. You can then grant only the srvctl command. The problem is that your user
and group has no privileges on the asm-disk devices.
Instead of trying to grant privileges to files and folders I'd setup sudo.
I've actually now had a further response from Oracle to my SR, and they maintain that giving these users oinstall group is fine, because that in itself does not give them any priviliges to update or otherwise tamper with files in Oracle homes. I guess that's fair, though I don't find it 100% convincing.
Personally, I don't think Oracle have quite got their act together on this. For example, they recommend using srvctl to stop and start things in this sort of environment now. But if you can run srvctl to stop / start a database insance, surely you could also use it to stop / start other resources - listeners, disk groups, ASM for example. That doesn't seem to fit too well with role separation!