Friday, April 29, 2016

Rdbms/ASM, seperation of roles, interpretations of the linux permissions

If we want  to implement seperation of roles for Oracle on Linux, which means having two seperate users for Grid and RDBMS, then the linux permissions, file owners and group owners of ASM disk devices, as well as the oracle binaries in both Grid Home and Oracle Home becomes crucial..
Let's examine this from Linux perspective by going through a working example, where the Asm device files are owned by grid:asadmin group, oracle binary in Grid Home is owned by grid:oinstall diskgroup and where oracle binary in Oracle Database Home is owned by oracle:asmadmin.

In this working example, we have the permission of oracle binary in RDBMS_HOME/bin as follows;

permissions  Links     owner     group        file
-rwsr-s--x       1           oracle    asmadmin   oracle

What does the columns mean?

Permission column: Permission of the file and the type of the file (directory, file etc.. )
Links column: number of links, the links to that file.
Owner: Owner of the file
Group:Group owner of the file
File: file name

What does that actually mean?

oracle binary file is owned by an OS user named oracle, the group owner of the oracle binary file is asmadmin. There are no links to that file. 
When we look at the permissions, which is the most crucial part of it, it says: any Os user can execute this binary. 
The users in asmadmin group can read and execute the oracle binary, and the user oracle can read, write and execute the file oracle. 
When the file oracle is executed by any OS user who has the permission to execute it(actually anyone can execute it), it is executed with the permissions of asmadmin group and the permission of oracle user.
What basically it says to the OS operating system is; when oracle binary is executed, execute it like it is executed by the user oracle and by the group asmadmin.

The file permissions of the oracle binary located in GRID HOME is the same as the permissions of oracle binary in RDBMS_HOME, but the owner and the groups of it is different(grid:oinstall)

permissions  Links     owner     group        file
-rwsr-s--x        1           grid      oinstall  /u01/app/11.2.0.4/grid/bin/oracle

So , this basically means, everyone can execute oracle binary stored in GRID HOME and when this binary file is executed, it is executed like it is executed by grid user and by the oinstall group (in terms of permissions)

The file permissions of the super/device files that are used to reach the ASM disks, are

[oracle@daroravmsrv1 ~]$ ls -al /dev/mapper/HDD_E0_S*
permissions  Links     owner     group        file
brw-rw----      1             grid   asmadmin /dev/mapper/HDD_E0_S00_1461220864

"b" means, it is a block device which can be accessed randomly.
So, the disks are owned by grid:asmadmin and in order to able to read or write them, one should have grid user or asadmin user privileges or let's say, only the grid user or the users in asmadmin directory can read from or write to these files.

What does this configuration provides us?
By using this configuration, we  have different users(oracle and grid) which can access the ASM disks. That is, when we create a file from an Oracle Database running in the oracle home, it can access to ASM devices and create the file there. On the other hand, if we want to do a Grid administration stuff using the same user that is the owner of the Oracle Database Oracle Home, then we will not be able to. 
So, by having asmadmin as the group owner of the oracle binary located in Oracle home and as we use the suid and guid bits set for the oracle binary, our database or anything that uses the oracle binary stored in Oracle Home can reach the Asm Disk Groups without any problems. (can read and write to the ASM disk devices) 
The oracle binary in Grid Home, which is owned by grid can already access the ASM Disk groups, as the owner of the ASM disks is grid user.
So seperation of duties are in place without effecting the oracle database's mandatory ASM works.

We may ask, why we use the SUID an GUID bits in general? We see them in standard single node Non-ASM Oracle installations as well.
Well, the answer again : is they are required seperation of duties.
Suppose we want the OS user erman to be able make "sqlplus / as sysdba"  local connections in an environment, where the RDBMS owner is the OS user named oracle. In such a case, the OS user "erman" should be act like OS user "oracle" and this can be accomplished by the SUID an GUID bits of the permissions of the $ORACLE_HOME/bin/oracle file.

Without SUID and GUID bits, we end up with the following; (altough erman is in dba group)

[root@demoorcl ~]# su - erman
[erman@demoorcl ~]$ id
uid=54323(erman) gid=54322(dba) groups=54322(dba),54321(oinstall)
[erman@demoorcl ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 26 15:12:32 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved
ERROR:
ORA-12547: TNS:lost contact

A workaround for this is to make a TNS listener based sysdba connection like;

sqlplus sys@PROD as sysdba... This way, listener will bind us to a LOCAL=NO shadow process and we will not need the suid ang guid based permissions. (In case of a local connection; we need the permissions for executing the oracle binary because the oracle binary is executed by our processes in a local connection)
Almost the same effect will be there when we change the permission of oracle binary to "700". Local connections will end with ORA-12547, but the configuration will  actually be more secure, as no one except oracle os user will be able to oracle binary, and if someone(in dba group) wants to connect to the database using sysdba connection, he/or she should provide the password..

Here is a demo:

[oracle@demoorcl bin]$ ls -al oracle
-rwx------ 1 oracle oinstall 232891110 May 19  2015 oracle

[oracle@demoorcl bin]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 29 19:37:11 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit

[oracle@demoorcl bin]$ exit
logout
[root@demoorcl ~]# su - erman
[erman@demoorcl ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 29 19:37:21 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ERROR:
ORA-12546: TNS:permission denied

[erman@demoorcl ~]$ sqlplus sys/manager@CLONE as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 29 19:37:33 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 


Well, this post become a little unstructured, but still it gives lost of valuable info. Hope you ll get benefit from that.

No comments :

Post a Comment