DATA GUARD SECURITY

In this post  I want to point out  new feature  of  Oracle Database 11g  Enterprise Edition that have caught my eye.

Because its mention about "physical standby database security" and sounds good !

With this new feature : You can make log shipping to continue work without copying password file from primary to physical standby.

• You dont have to set  the same SYS password  for production  and standby databases, also you dont have to copy password file on all nodes for Rac enviroments.

• No more  password mismatch error messages, no more un "ORA-16191: Primary log shipping client not logged on standby" error messages.

• You can manage archivelog downstream / realtime downstream via redo transport user.! ( Realtime Datawarehousing ) 

Coding:

Step 1:

sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 21 11:01:26 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL>
SQL>
SQL>
SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
 
SQL> create user DG_USER identified by "PSSWD4$" ;
User created.
 
SQL> GRANT SYSOPER TO DG_USER;
Grant succeeded.
 

SQL> show parameter redo_transport_user
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
redo_transport_user                  string

SQL>
SQL>
SQL> ALTER SYSTEM SET REDO_TRANSPORT_USER='DG_USER' SID='*';
System altered.
SQL>

SQL>  show parameter redo_transport_user
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
redo_transport_user                  string      DG_USER

SQL>
SQL> select * from V$PWFILE_USERS ;
 
USERNAME                    SYSDB    SYSOP   SYSAS
------------------------------    -----         -----        -----
SYS                                   TRUE     TRUE     FALSE
DG_USER                        FALSE   TRUE      FALSE
 

Step 2 :

[oracle@server]$ cd $ORACLE_HOME/dbs

[oracle@server dbs]$ ls -ltr
-rw-r----- 1 oracle oinstall     2048 Jan 21 10:05 orapwtest

[oracle@server dbs]$ cp orapwtest orapw_backup

[oracle@server dbs]$ orapwd file=orapwtest  password="PSSWD4$"  ignorecase=y entries=5

[oracle@server dbs]$ ls -ltr
-rw-r----- 1 oracle oinstall     2048 Jan 21 10:15 orapwtest


Step 3 ( important notice /  revoke user):

•  If you need to change password for REDO_TRANSPORT_USER (DG_USER) , you need to copy password file from primary to standby. But we are accepting that we dont change redo_transport_user password as often as SYS password.  You check the SEC_CASE_SENSITIVE_LOGON parameter at dataguard pfile  for case sensitive databases.

• If you dont want to redo_transport_user  ; You must revoke sysoper from user and set redo_transport_user parameter to null with alter system command and then drop user.

ref:  ID [1416595.1]