Oracle DBA – A lifelong learning experience

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

Posted by John Hallas on May 6, 2012

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.
  1. The testuser user can only read the dba views if the data has been cached in the shared pool.

 

  1. Environment – The testuser user cannot login as sysasm

 

  1. 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.

 

  1. The unix testuser user cannot login as sysasm

 

  1. 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

2 Responses to “Test Case for 11gR2 Role Separation issue on HP-UX – help wanted”

  1. Pete said

    Hi John,
    When the queries fail for testuser, what OS account owns the shadow process that is accessing the database?

    Regards
    Pete

  2. Balaji said

    Hi,

    We are testing the same scenarios in our environment. You said this works in Linux. However it is not working for us even on Linux.

    I tested connecting as / as sysdba. It gets connected without any problem.

    But when I tried sqlplus sys@ as sysdba with the right password, it is saying ‘invalid username/password; logon denied.

    we tried different user in database with sysdba privileges, this time it is throwing ‘insufficient privileges’

    Regards
    Balaji

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 184 other followers

%d bloggers like this: