Tuesday, December 23, 2008

RAC with SQLNET.AUTHENTICATION_SERVICES=(NTS)


when using "SQLNET.AUTHENTICATION_SERVICES=(NTS)" on RAC, We  should concern about Oracle Cluster to start RDBMS or ASM. 

Need to show the problem when added "SQLNET.AUTHENTICATION_SERVICES=(NTS)"  on sqlnet.ora to help about user authenticate  secutity [/ as sysdba] .

This case seperated ORACLE_HOME between Database and ASM. anyway used "SQLNET.AUTHENTICATION_SERVICES=(NTS)" on Database PATH.

When I ran ORA_CRS_HOME/bin/crs_start start crs

I found CRS alert log:

[crsd(30430)]CRS-1201:CRSD started on node hosttest01.
[crsd(30430)]CRS-1205:Auto-start failed for the CRS resource . Details in hosttest01.
[crsd(30430)]CRS-1205:Auto-start failed for the CRS resource . Details in hosttest01.
[crsd(30430)]CRS-1205:Auto-start failed for the CRS resource . Details in hosttest01.
[crsd(30430)]CRS-1205:Auto-start failed for the CRS resource . Details in hosttest01.

CRS can not Auto-start something:

$ crs_stat -t 
Name           Type           Target    State     Host        
----------------------------------------------------
ora.db.db   application    OFFLINE   OFFLINE               
ora....b1.inst application    ONLINE    UNKNOWN   hosttest01
    
$ srvctl stop  instance -d db -i db1
$ srvctl start  instance -d db -i db1
PRKP-1001 : Error starting instance db1 on node hosttest01
hosttest01:ora.db.db1.inst:
.
.
.
hosttest01:ora.db.db1.inst:
hosttest01:ora.db.db1.inst:Enter user-name: ERROR:
hosttest01:ora.db.db1.inst:ORA-01031: in
sufficient privileges
Instance cannot start by srvctl, why?

Check imon_{database_name}.log on ORACLE_HOME/log/{hostname}/racg PATH

Enter user-name: ERROR:
ORA-01031: insufficient privileges

So, Check attributes on resource (database + instance):

$ crs_stat -p ora.db.db
.
.
.
USR_ORA_CONNECT_STR=/ as sysdba
.
.
.

$ crs_stat -p ora.db.db1.inst
.
.
.
USR_ORA_CONNECT_STR=/ as sysdba
.
.
.

RAC have used "USR_ORA_CONNECT_STR=/ as sysdba" attribute, that make auto-start failed or can not use srvctl command-line with "SQLNET.AUTHENTICATION_SERVICES=(NTS).




3 comments:

dan said...

Hi,
Thanks for this thread.
We are implementing the security plolicy for our client.we need to revoke sys dba privilege.when i used sql.authentication_services=(NONE).
sys dba account is unable to switch logfile and getting 1031 priviliges related error. same is with NTS setting.
we have different path for asm and oracle home.
Could you please is there any way to implement the secirity policy.

Thanks

dan said...

Hi,

Thanks for this thread.

we are implementing security policy for our client. our databas is on RAC and have different asm and oracle home. As per the security, we need to revoke sys dba priviliges. when i used SQLNET.AUTHENTICATION_SERVICES=(NTS) in sqlnet.ora or SQLNET.AUTHENTICATION_SERVICES=(NONE). we are suucess with revoking sys dba privileges however we are facing other issue that is sys dba account is not able switch log file.I am getting an error the one whcih you are getting
Errors in file ORACLE_BASE/diag/rdbms/db/db1/trace/db1_arc0_2089.trc:
ORA-00313: open failed for members of log group 5 of thread 2
ORA-00312: online log 5 thread 2: '+DATA2/db/onlinelog/group_5.394.673269475'
ORA-17503: ksfdopn:2 Failed to open file +DATA2/db/onlinelog/group_5.394.673269475
ORA-01031: insufficient privileges
ORA-00312: online log 5 thread 2: '+DATA1/db/onlinelog/group_5.271.673269473'
ORA-17503: ksfdopn:2 Failed to open file +DATA1/db/onlinelog/group_5.271.673269473
ORA-01031: insufficient privileges
Errors in file ORACLE_BASE/diag/rdbms/db/db1/trace/db1_arc0_2089.trc:
ORA-00313: open failed for members of log group 5 of thread 2
ORA-00312: online log 5 thread 2: '+DATA2/db/onlinelog/group_5.394.673269475'
ORA-17503: ksfdopn:2 Failed to open file +DATA2/db/onlinelog/group_5.394.673269475
ORA-01031: insufficient privileges
ORA-00312: online log 5 thread 2: '+DATA1/db/onlinelog/group_5.271.673269473'
ORA-17503: ksfdopn:2 Failed to open file +DATA1/db/onlinelog

could you please is there any way that we can revole sys dba privileges.

Thanks and Regards

Surachart said...

Example from 10g
$ crs_stat -p ora.host01.ASM1.asm | grep sysdba
USR_ORA_CONNECT_STR=/ as sysdba

However... it need "/ as sysdba" to connect
if check Oracle Docs: SQLNET.AUTHENTICATION_SERVICES parameter in the SQLNET.ORA should be set to ALL.

However,If changed to be "ALL" but still error . I think you can find out Oracle support and review -> Unable To Connect To ASM Instance Due To ORA-01031 [ID 958282.1]