Oracle SQLNET.ORA and TNSNAMES.ORA Settings to Make Wallet Work

You would need to open up your Database connection settings to allow Wallet connection.  These settings are done through Oracle SQLNET.ORA and TNSNAMES.ORA configuration file.

Applies to Oracle Wallet Manager, Wallet, Security, Weblogic, Oracle Database

Sample:

1. sqlnet.ora:

# sqlnet.ora Network Configuration File: /mysupport/etc/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT, ONAMES, HOSTNAME)

SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/mysupport/home/users/shiva/mydir)))

2. tnsnames.ora

#TNSNAMES to point to the wallet name - ORACLE2

ORACLE2 =
  (DESCRIPTION =
    (ADDRESS = (PROTO = TCP)(HOST = myhost)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORACLE)
    )
  )

Create a wallet for external identification:

Step 1:

Syntax: mkstore -wrl <wallet_location> -createCredential <tns alias> <user_name> <password>

Example:

mkstore -wrl /opt/oracle/mydir/wallet -createCredential ORACLE2 shivak password

[myos]/mysupport/home/users/shiva/mydir> mkstore -wrl /mysupport/home/users/shiva/mydir/test -create
Enter password: wallettest
Enter password again: wallettest

======================================

Step 2:

Syntax: mkstore -wrl <directory where the wallet needs to be created> -createCredential <tns_alias> <existing_user> <existing_passwd>

[myos]/mysupport/home/users/shiva/mydir>  mkstore -wrl /mysupport/home/users/shiva/mydir/test -createCredential SHIVAV1023W shivak dbpw

Enter password:  wallettest
Create credential oracle.security.client.connect_string1

======================================

Step 3: Check if WALLET is created

[myos]/mysupport/home/users/shiva/mydir> ls -l
total 36
-rw-------  1 mysupport udba  8316  Sep 30 13:27  cwallet.sso
-rw-------  1 mysupport udba  8288  Sep 30 13:27  ewallet.p12
-rw-r--r--  1 mysupport udba   470  Sep 30 21:03  listener.ora
-rw-r--r--  1 mysupport udba   318  Sep 30 13:16  sqlnet.ora
-rw-r--r--  1 mysupport udba   196  Sep 30 20:57  tnsnames.ora

======================================

Step 4: Connection from SQLPLUS to test WALLET

[myos]/mysupport/home/users/shiva/mydir> sqlplus /@SHIVAV1023W

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 30 13:29:38 2013

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
...

Reference / Read More: 

1. Oracle Database Security Guide
10g Release 2 (10.2)
B14266-09

Chapter 9 Secure External Password Store
URL: http://docs.oracle.com/cd/B19306_01/network.102/b14266/cnctslsh.htm

2. Oracle Fusion Middleware Administrator's Guide
11g Release 1 (11.1.1)
Part Number E10105-13

Chapter 8 Managing Keystores, Wallets, and Certificates
URL: http://docs.oracle.com/cd/E23943_01/core.1111/e10105/wallets.htm#CIHIHGJG

3. Oracle Database Advanced Security Administrator's Guide
11g Release 2 (11.2)
E40393-02

Section F orapki Utility
URL: http://docs.oracle.com/cd/E11882_01/network.112/e10746/asoappf.htm

Oracle Wallet - Creation and Usage Example

Applies to Oracle Database, Web Logic, Wallet, Oracle Wallet Manager.

Understanding is that you already have Oracle Wallet Manager installed, this is part of full Oracle Database installation.

1. To create wallet: 

1.1. Enter a new password as part of wallet creation.

[youros]/testcases/mydir> mkstore -wrl /testcases/mydir/mytest -create

Enter password:  mytest123
Enter password again:  mytest123

2. Wallet Created Directory:

Upon created of wallet you will find files like cwallet.sso, ewallet.p12 in your directory where the wallets were created:

[youros]/testcases/mydir> cd mytest
[youros]/testcases/mydir/mytest> ls
cwallet.sso   ewallet.p12

3. To create wallet credential: 

Syntax: mkstore -wrl <wallet_location> -createCredential <alias> <user_name> <password>

Enter wallet password: <password that was previously used for wallet creation>

[youros]/testcases/mydir/mytest> mkstore -wrl /testcases/mydir/mytest/ -createCredential SHIVAV1024U.yours.mine.com scott tiger

Enter wallet password: mytest123
Create credential oracle.security.client.connect_string1

Now you have both wallet and wallet credential ready.

4. To Test Wallet: 

You can use Oracle server side packages like utl_http to request external site.  You may need to use proxy servers if you are behind proxy.

Case 1: To make utl_http.request call using Proxy Server

Syntax: 

SELECT utl_http.request('<url>', '<proxy server:port>') FROM DUAL; 

Example: 

SQL> SELECT utl_http.request('http://www.formsite.com', 'www-proxy.yours.mine.com') from dual;

UTL_HTTP.REQUEST('HTTP://WWW.FORMSITE.COM','WWW-PROXY.YOURS.MINE.COM')
--------------------------------------------------------------------------------
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/x
html1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"><!-- Instance
Begin template="/Templates/Full.dwt" codeOutsideHTMLIsLocked="false" -->

<head>
        <meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
...
<removed rest of the contents>

Case 2: To make utl_http.request call using Proxy Server with Wallet

Syntax: 

SELECT utl_http.request('<url>', '<proxy server:port>', 'file:<wallet directory reference>', '<wallet password>') FROM DUAL; 

Example: 

SELECT utl_http.request('http://www.formsite.com', 'www-proxy.yours.mine.com','file:/testcases/mydir/mytest/','mytest123') FROM DUAL; 

UTL_HTTP.REQUEST('HTTP://WWW.FORMSITE.COM','WWW-PROXY.YOURS.MINE.COM','FILE:/TEST
--------------------------------------------------------------------------------
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/x
html1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"><!-- Instance
Begin template="/Templates/Full.dwt" codeOutsideHTMLIsLocked="false" -->
...
<removed rest of the contents>


If the call fails, you might get errors like: 

ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-29024: Certificate validation failure
ORA-06512: at line 1

Reference / Read More

1. Oracle Fusion Middleware Administrator's Guide
11g Release 1 (11.1.1)
Part Number E10105-13

Chapter 8 Managing Keystores, Wallets, and Certificates
URL: http://docs.oracle.com/cd/E23943_01/core.1111/e10105/wallets.htm#CIHIHGJG

2. Oracle Database Advanced Security Administrator's Guide
11g Release 2 (11.2)
E40393-02

Section F orapki Utility
URL: http://docs.oracle.com/cd/E11882_01/network.112/e10746/asoappf.htm

Auditing - Oracle 12c Security Feature - What is New?

Applies to Software Auditing, Database Auditing, Database Application Auditing, Oracle 12c.

Key features of Oracle 12c Security are Auditing, Privileges, Data Redaction, Database Vault.

We will discuss about Auditing in more detail.   Privileges, Data Redaction, and Database Vault will be discussed as separate blogs.

1. Auditing Overview 

Let's see what features are available in existing Oracle releases up to Oracle 11g Release 2.

There are different types of Auditing that you can perform at Oracle Database level.  In general, auditing is performed to keep safe Database and to have better user control.  It can also be used monitor any activities at Database level.  For example: To monitor suspicious activity. At Database parameter level, we can use AUDIT_TRAIL, AUDIT_SYS_OPERATIONS and use package DBMS_FGA.ADD_POLICY for fine grained auditing.

2. Audit Trail Implementation Packages 

Where Audits are Stored?

How Audit Data are Secured?

Audits are secured within the Oracle database at syslog audit: AUDIT_SYSLOG_LEVEL= local1.info
and by using Oracle Audit Vault.

3. Basic Audit Information (BAI) vs Extended Audit Information (EAI) 

4. Extended Audit Information (EAI) 

EAI is from SYS object view (SYS.UNIFIED_AUDIT_TRAIL) that has columns like FGA_POLICY_NAME, DP_TEXT_PARAMETERS1, DP_BOOLEAN_PARAMETERS1, RMAN_OPERATION, RMAN_OBJECT_TYPE, RMAN_DEVICE_TYPE, RMAN_xxx, OLS_POLICY_NAME, OLS_xxx, DV_xxx

5. Fine Grained Audit (FGA) Policy 

FGA policy is added by using package DBMS_FGA.ADD_POLICY and any changes are recorded in UNIFIED_AUDIT_TRAIL which can be queried. 

6. Data Pump Audit Policy

Audit policy can be created at Data pump using component datapump.

7. Sample UNIFIED_AUDIT_TRAIL Query 

8. RMAN Audit Information 

Audit policy can be created at RMAN level by flushing the audit data using object SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL.

9. Database Vault Audit Policy 

Audit policy can be created at Database Vault using component dv.  Any configuration changes at Database Vault can be audited.

10. OLS or RAS Audit Policy

Oracle Label Security (OLS)
Real Application Security (RAS)

Audit policy can be using component OLS.

11. Unified Audit Implementation (UAI) 

Unified Audit Implementation can be achieved using UNIFIED_AUDIT_TRAIL and uniaud_on binary. 

12. Security Challenges - Roles

Packages like DBMS_AUDIT_MGMT can assist with AUDIT_ADMIN (Audit Administration) challenges.  AUDIT_VIEWER can get UNIFIED_AUDIT_TRAIL views.  AUDSYS is a dedicated schema at Database level.

13. Security Challenge - SYS Auditing 

Non Unified Audit gets recorded in OS audit directory and Unified Audit can be performed at SYS.UNIFIED_AUDIT_TRAIL.

14. Simplicity Challenges - Audit 

No longer need parameters like AUDIT_xxx.  Audit policies can simply be created using CREATE AUDIT POLICY command and viewed at UNIFIED_AUDIT_TRAIL.

15. Steps to Create AUDIT Policy (STEP 1)

Using 'Create Audit Policy' command, create system privileges, actions, roles individually or all in one command.

16. Creating Object Specific Actions

Using 'Create Audit Policy' command, you can also create object-specific actions such as LOCK on a specific table or executing a grant on a specific object (For example: PL/SQL Procedure or Package).

17. Creating Audit Policy - Condition and Evaluation

Using 'Create Audit Policy' command, you can also add Conditions and Evaluations per session, statement and instance basis.  Actions e.g., RENAME, ALTER on a particular Table.

18. Steps to Create AUDIT Policy (STEP 2)

To enable or to disable the audit policy.  AUDIT POLICY Command can be used to enable or disable the audit policies.

19. How to view Audit Policy?

Use views like AUDIT_UNIFIED_POLICIES and AUDIT_UNIFIED_ENABLED_POLICIES.

20. Predefined Audit Policies

Policies like ORA_ACCOUNT_MGMT, ORA_DATABASE_PARAMETER, ORA_SECURECONFIG can be used to check on different audit options.

Auditing Requirements

Commonly used auditing requirements are done for compliance and certifications reasons.  Regulatory bodies with common auditing requirements:

Sarbanes-Oxley Act (SOX)

Health Insurance Portability and Accountability Act (HIPAA)

Financial Services Agency (FSA)

Payment Card Industry (PCI)

Data Security Standard (DSS)

Personally Identifiable Information (PII)

International Convergence of Capital Measurement and Capital Standards: a Revised Framework (Basel II) (ICCM) (BCBS)

Bank for International Settlements (BIS)

Japan Privacy Law / Japanese Hitoshi

Japanese Requirement Sarbanes-Oxley Act Section 302 (JSOX) Management Certification

European Union Directive on Privacy and Electronic Communications  EU (DPEC)

Reference / Read More 

1. Oracle Database 2 Day + Security Guide
12c Release 1 (12.1)
E17609-15

Chapter 9 Auditing Database Activity

http://docs.oracle.com/cd/E16655_01/server.121/e17609/tdpsg_auditing.htm

2. Oracle Database Security Guide
12c Release 1 (12.1)
E17607-19

Chapter 21 Introduction to Auditing
and
Chapter 22 Configuring Audit Policies

http://docs.oracle.com/cd/E16655_01/network.121/e17607/auditing.htm

3. Security Inside Out, Plug into Defense-in-Depth with Oracle Database 12c
https://blogs.oracle.com/securityinsideout/entry/plug_into_defense_in_depth
https://blogs.oracle.com/securityinsideout/

RMAN - Configuration Check and Tests

Applies to Oracle Database (9i ~ 12c) - RMAN (Recovery Manager).

To check RMAN Configuration:

1. Current RMAN Configuration :

RMAN> show all;

2. To Test RMAN:

Test 1 - Backup to disk:

$ rman target  /   log=rman_disk.log

RMAN> set echo on;
RMAN> show all;
RMAN> run
{
allocate channel d1 device type  disk;
backup datafile 1 ;
}

Look for rman_disk.log file for the results.

Test 2 - Backup to tape:

$ rman target  /   log=rman_tape.log

RMAN> set echo on;
RMAN> show all;
RMAN> backup datafile 1 ;

Look for rman_tape.log file for the results.

3. Execute the following RMAN Command to make sure RMAN checks what physical files exist or not :

$ rman target  / log=crosscheck_out.log

RMAN> set echo on;
RMAN> crosscheck backup ;

4. If you are using rman script for backup, try running that script with log option.

$ rman target  / log=crosscheck_out.log

RMAN> set echo on;
RMAN> <execute your RMAN Script>

Look for crosscheck_out.log file for the results.


Reference / Read More:

1. Oracle Database Backup and Recovery Reference
11g Release 2 (11.2)
E10643-07

http://docs.oracle.com/cd/E11882_01/backup.112/e10643/toc.htm

2. Oracle Database Backup and Recovery User's Guide
11g Release 2 (11.2)
E10642-06

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/toc.htm

3. Oracle Database Recovery Manager Reference
10g Release 1 (10.1)
Part Number B10770-01

Section: ALLOCATE CHANNEL

http://docs.oracle.com/cd/B12037_01/server.101/b10770/rcmsynta4.htm

XML File Transformation - Multiplication Sample

Applies to Oracle 9i Release 2 or later, SOA.

Sample code to demonstrate xml transformation using two string multiplication.

Within a SOA composite, xml transformation using two string multiplication.
The strings contain numeric values, so there is no exception.

1. Create the input file named: input.xml

<?xml version="1.0" encoding="UTF-8" ?>
<singleString xmlns="http://xmlns.oracle.com/singleString">
  <name>35</name>
  <surname>2</surname>
</singleString>

2. Create the .xsl file transformation.xsl: 

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
               xmlns:inp1="http://xmlns.oracle.com/singleString">
 <xsl:template match="/">
   <xsl:variable name="gfhh"
                 select="/inp1:singleString/inp1:name * /inp1:singleString/inp1:surname"/>
   <inp1:singleString>
     <inp1:surname>
       <xsl:value-of select="$gfhh"/>
     </inp1:surname>
   </inp1:singleString>
 </xsl:template>
</xsl:stylesheet>

3.  Make sure you have your jdk bin directory in your path.  

For example;  /refresh/home/Oracle/jdk1.6.0_30/bin directory (Java Compiler)

4. Execute the following command:

java -cp ./xmlparserv2.jar oracle.xml.parser.v2.oraxsl -v input.xml transformation.xsl

Please note xmlparserv2.jar is a .jar file which is part of Oracle XDB/XML Parser library ($ORACLE_HOME/lib/xmlparserv2.jar).

5. Execution of above command will produce xml output of:

<?xml version = '1.0' encoding = 'UTF-8'?>
<inp1:singleString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:inp1="http://xmlns.oracle.com/singleString"><inp1:surname>70</inp1:surname></inp1:singleString>

This demonstrates the transformation, and the use of the variable types within the SOA composite.

SOA relies on the transformation code within the xmlparserv2.jar file.

The expected output is 70, if the input is not a whole number the output will be rounded to 2 digits.

Reference:

XSLT Transformations - Peachpit Press

http://www.peachpit.com/articles/article.aspx?p=21845&seqNum=3

How to Handle Java Serialized Object in Oracle?

Applies to Java, JEE Environments.

Take the serialized object, which is a byte array, convert it to a string and write to a file instead of storing it in Oracle.

Error Cases:

1. If you try to store it in a varchar2 column in Oracle Database you might get error when retrieving the object and deserialize it.  Error could state that 'serialVersionUID had changed'.

2. If you store the serialized object (a byte array) into a blob column.  And when trying to retrieve the blob to deserialize the object, you might get an error saying that the object had been corrupted.  That is because of the array size difference.  i.e., stored was 102 bytes and the one retrieved was 203 bytes.

Reference / Read More:

java.io
Interface Serializable
http://docs.oracle.com/javase/7/docs/api/java/io/Serializable.html

Serializable Objects
http://docs.oracle.com/javase/jndi/tutorial/objects/storing/serial.html

Java Object Serialization
http://docs.oracle.com/javase/7/docs/technotes/guides/serialization/

http://docs.oracle.com/javase/7/docs/platform/serialization/spec/serialTOC.html

http://docs.oracle.com/javase/7/docs/platform/serialization/spec/serial-arch.html

SQL Tuning - Oracle 12c - What is New?

Applies to Oracle 12c.

SQL Tuning has three major modules in Oracle 12c.

- Adaptive SQL Plan Management

- Adaptive Execution Plans

- Optimizer Statistics Management

1. Adaptive SQL Plan Management

Summary of SQL Plan Management (SPM):

SQL Plan Baseline:

SQL Management Base Enhancements:

2. Adaptive Execution Plans

Adaptive Execution Plans:

2.1 Dynamic Adaptive Plan:

Using Dynamic Plans:

Dynamic Plan - Adaptive Process:

Dynamic Adaptive Plan Example:

2.2 Reoptimization Adaptive Plan:

3. Optimizer Statistics Management

3.1 SQL Plan Directives

Creating SQL Plan Directives

Using SQL Plan Directives:

SQL Plan Directives - Example:

3.2 Statistics Gathering Performance Improvements

3.2.1 Online Statistics Gathering:

3.2.2 Concurrent Statistics Gathering:

Concurrent Statistics Gathering - Creating Jobs at Different Levels:

3.2.3 Incremental Statistical Gathering Improvements:

Incremental Global Statistics - Workflow:

3.2.4 Session Private Statistics for Global Temporary Tables: 

3.3 Histogram Enhancements: 

3.3.1 Top Frequency Histograms: 

3.3.2 Hybrid Histograms: 

Hybrid Histogram Example: 

3.4 Enhancements to Extended Statistics: 

3.4.1 Column Group: 

Example:

3.5 Dynamic Sampling Enhancements: 

Automatic Dynamic Sampling - optimizer_dynamic_sampling:

Summary of SQL Tuning in Oracle 12c: 

•Adaptive SQL Plan Management

•Enhancements to the SQL management base

•How to use Dynamic plan to improve query performance

•Reoptimization for adaptive execution plans

•How to use SQL plan directives to generate a better plan

•Statistics Gathering performance improvements

•How to use new histograms

•Enhancements to extended statistics and how to detect useful column groups for a specific workload

•Enhancements to dynamic sampling

Reference / Read More: 

Oracle Database 11g: The Top Features for DBAs and Developers
By Arup Nanda
Adaptive Cursors and SQL Plan Management
http://www.oracle.com/technetwork/articles/sql/11g-sqlplanmanagement-101938.html

SQL Plan Management with Oracle Database 12c
Oracle White Paper, June 2013
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf

SQL Plan Management (Part 4 of 4): User Interfaces and Other Features
By Maria Colgan on Feb 02, 2009
https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_4_of_4_user_interfaces_and_other_features

How do adaptive cursor sharing and SQL Plan Management interact?
By Allison on Feb 11, 2013
https://blogs.oracle.com/optimizer/entry/how_do_adaptive_cursor_sharing