Test Case for 11gR2 Role Separation issue on HP-UX - help wanted

On any HPUX (11:31) system where grid infrastructure has been applied with 2 software owners – in our case grid and oracle  – oracle’s best practise for GI implementations – RAC or standalone systems.

 The standard  unix account we are using  is , testuser (although in this case he is in the DBA group).

 This user is a member of the following groups :

 uid=664(testuser) gid=500(dba)

 Test Server       = server

Test database     = dbatest

 Login to the testuser user

1. setsid to set Oracle environment to dbatest

 The ASM luns are owned by the grid user as below :

  

[testuser@server][dbatest]/dev/grid $ls -ltr
total 0
crw-rw----   1 grid       asmdba      13 0x00000a Apr 27 12:38 disk002
crw-rw----   1 grid       asmdba      13 0x000009 Apr 27 22:00 disk001
crw-rw----   1 grid       asmdba      13 0x00000b Apr 28 12:44 disk003

 logon onto unix server as testuser

Login into the database as “sqlplus / as sysdba”

Set database environment variables as appropriate (OH, SID,PATH)

 Database bounced to clear cache out (shared pool flush would also work)

 

select from various v$ views

 

SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '+DATA/dbatest/controlfile/current.263.781706309'
ORA-15081: failed to submit an I/O operation to a disk

SQL> select name from v$datafile;
select name from v$datafile
*
ERROR at line 1:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '+DATA/dbatest/controlfile/current.263.781706309'
ORA-15081: failed to submit an I/O operation to a disk

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

SQL> l
1* select * from v$controlfile
SQL> /

STATUS  NAME                                               IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------- --- ---------- --------------
+DATA/dbatest/controlfile/current.263.781706309    NO       16384            594
+FRA/dbatest/controlfile/current.1775.781706309    YES      16384            594

select from dba views

 

SQL> select username from dba_users;
select username from dba_users
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 1: '+DATA/dbatest/datafile/system.269.781706107'
ORA-15081: failed to submit an I/O operation to a disk

SQL> select tablespace_name from dba_tablespaces;
select tablespace_name from dba_tablespaces
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 1: '+DATA/dbatest/datafile/system.269.781706107'
ORA-15081: failed to submit an I/O operation to a disk

SQL> select file_name from dba_data_files;
select file_name from dba_data_files
*
ERROR at line 1:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '+DATA/dbatest/controlfile/current.263.781706309'
ORA-15081: failed to submit an I/O operation to a disk

 

With the current groups (dba as the primary group)

 

1. When logged in as the unix testuser as sysdba into the database not all the v$ views can be read. However there are some views including the v$controlfile, v$pwfile_users view that can be read by the testuser user.

2. The testuser user can only read the dba views if the data has been cached in the shared pool.

 

3. Environment – The testuser user cannot login as sysasm

 

4. Environment – The testuser user cannot  log into ASMCMD

 

 Login as the unix testuser and then login to the database as sys user as sysdba (same as earlier) using tnsnames – on the unix server, not using a sqlplus client

[testuser@server][dbatest]/home/testuser $sqlplus sys@dbatest as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 30 16:19:55 2012

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>

SQL> select name from v$database;

NAME
---------
DBATEST

SQL>

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/dbatest/datafile/system.269.781706107
+DATA/dbatest/datafile/sysaux.260.781706109
+DATA/dbatest/datafile/undotbs1.265.781706109
+DATA/dbatest/datafile/users.264.781706109

SQL>

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

SQL>

SQL> select username from dba_users;

USERNAME
------------------------------
SYSDEVPG
SYSTEM
SYS
OUTLN
SI_INFORMTN_SCHEMA
ORDPLUGINS
ORDSYS
XDB
EXFSYS
ANONYMOUS
ORDDATA

28 rows selected.

SQL>

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

SQL>

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/dbatest/datafile/users.264.781706109
+DATA/dbatest/datafile/undotbs1.265.781706109
+DATA/dbatest/datafile/sysaux.260.781706109
+DATA/dbatest/datafile/system.269.781706107

 
 

1. If a connection is made as “sys / as sysdba” using a tnsnames entry, then all v$ views and all dba views are readable. And no problem is experienced.

 

 

Login as the unix testuser and then login to the database as sys user as sysdba (as earlier) and run select from v$ views and dba views again

Select name from v$database;  – fails

Select name from v$datafile;  – fails

 The select from the v$ views above still fails.

 Select * from v$pwfile_users; - success

 

The select from the dba views is successful, as the data has been cached when selecting using the tnsnmaes connection – this seems to be the crux of the issue

 

Select username from dba_users;            – success

Select tablespace_name from dba_tablespaces – success

Select file_name from dba_data_files       – success

[testuser@server][dbatest]/home/testuser $sqlplus sys@dbatest as sysdba

Flush the shared pool

SQL> alter system flush shared_pool;

System altered.

SQL> select username from dba_users;
select username from dba_users
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 1: '+DATA/dbatest/datafile/system.269.781706107'
ORA-15081: failed to submit an I/O operation to a disk

 

 

This means that if the dba views have been cached, then selecting from them as the unix testuser as sysdba user will work. After flushing the shared_pool the datafile error is reported again as above. Look at the dba_users select as a good example above.

 

 

Login as the oracle unix account and login to the database as “sqlplus / as sysdba”  – this to get it back into the shared_pool

 Select username from dba_users;     – success

 The data has been cached again.

Login as the testuser unix account and login to the database as “sqlplus / as sysdba”

Select username from dba_users;     – success

 This select is a success as the data has already been cached.

Add the asmdba group as a secondary group for the unix account testuser

uid=664(testuser) gid=500(dba) groups=1064(asmdba)

 

With asmdba as the secondary group and dba as the primary group

 

1. The unix  testuser user can read all v$ views and all dba views.

 

2. The unix testuser user cannot login as sysasm

 

3. The unix testuser user cannot  log into ASMCMD

 

So if the testuser user is assigned the asmdba group as a secondary group, then all data dictionary views can be read, whilst at the same time denying access to login as sysasm and asmcmd.

 

However the key point is that  the asmdba group should not be required just to read the oracle data dictionary. That is the whole point of GI and role separation.
If anybody can assist with this test case then please feel to add a comment

 

Note that on Linux it works OK

Linux does behave differently to HP.

 

The sysoper user is only in the dba group and it can read all v$ views and dba views. ASMCMD and sysasm access is denied.

 

The lun ownership is as below owned by root :

 brw-r—– 1 root disk 8, 49 Jan 10 23:39 sdd1

brw-r—– 1 root disk 8, 33 Jan 10 23:39 sdc1

brw-r—– 1 root disk 8,  1 Jan 10 23:39 sda1

brw-r—– 1 root disk 8, 65 Jan 10 23:40 sde1

brw-r—– 1 root disk 8, 81 Jan 10 23:40 sdf1

brw-r—– 1 root disk 8, 97 Jan 10 23:40 sdg1

 

The reason for this is probably because we use ASMLIB on linux which seems to get round the os privileges issue we are experiencing on HP.   

About these ads