Setup ssh user equivalence for all RAC nodes

This Example Done on Two Node SSH Setup:

Log on as user ORACLE on nodes. Do not give pass phrase to any of the methods below.

ON NODE 1

Step 1

[root@rac1pub ~]# su - oracle

Step 2

[oracle@rac1pub ~]$ ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_dsa):
Created directory '/home/oracle/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_dsa.
Your public key has been saved in /home/oracle/.ssh/id_dsa.pub.
The key fingerprint is:
b1:24:2f:8f:5a:27:4b:ce:aa:09:ce:13:bd:d8:b1:3e oracle@rac1pub.kasb.com

Step 3

[oracle@rac1pub ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
68:0f:79:13:76:a7:a1:2e:ce:6e:1f:a6:2b:1a:15:f3 oracle@rac1pub.kasb.com

Step 4

[oracle@rac1pub ~]$ cd /home/oracle/.ssh

Step 5

[oracle@rac1pub .ssh]$ ls -ltr
total 16
-rw-r--r--  1 oracle oinstall 613 Oct 16 23:06 id_dsa.pub
-rw-------  1 oracle oinstall 668 Oct 16 23:06 id_dsa
-rw-r--r--  1 oracle oinstall 233 Oct 16 23:06 id_rsa.pub
-rw-------  1 oracle oinstall 883 Oct 16 23:06 id_rsa

Step 6

Note: The file name should be (authorized_keys) their is no chance for spelling mistake.
otherwise your ssh will be failed.

[oracle@rac1pub .ssh]$ cat id_dsa.pub >> authorized_keys

[oracle@rac1pub .ssh]$ cat id_rsa.pub >> authorized_keys

Step 7
Now we are sending node1 ssh keys to node 2 in temporary file because to get this keys in authorized_keys file of Node 2
 
[oracle@rac1pub .ssh]$ scp authorized_keys  rac2pub:/tmp/rac1keys.tmp

The authenticity of host 'storage (192.168.1.192)' can't be established.
RSA key fingerprint is 97:b4:a8:13:a1:76:57:44:e2:0b:60:c1:b8:13:db:27.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'rac2pub,192.168.1.192' (RSA) to the list of known hosts.oracle@rac2pub's password:
authorized_keys                                                   100% 1692     1.7KB/s   00:00
 
ON NODE 2
Now you can verfiy on Rac node 2 in tmp directory file(rac1keys.tmp) should be their

[oracle@rac2pub ~]$ cd /tmp
[oracle@rac2pub tmp]$ ls -ltr
-rw-r--r--  1 oracle oinstall  1692 Oct 17 00:06 rac1keys.tmp

STEPS ON NODE 2==================================================
Note: Follow same Steps(1 to 6) done on RAC node 1

Step 7
Now we are sending node2 ssh keys to node 1 in temporary file because to get this keys in authorized_keys file of Node 1

[oracle@rac2pub .ssh]$ scp authorized_keys  rac1pub:/tmp/rac2keys.tmp
The authenticity of host 'storage (192.168.1.191)' can't be established.
RSA key fingerprint is 97:b4:a8:13:a1:76:57:44:e2:0b:60:c1:b8:13:db:27.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'rac1pub,192.168.1.191' (RSA) to the list of known hosts.oracle@rac1pub's password:
authorized_keys                                                   100% 1692     1.7KB/s   00:00


ON NODE 1
Now you can verfiy on Rac node 1 in tmp directory file (rac2keys.tmp) should be their
[oracle@rac1pub ~]$ cd /tmp
[oracle@rac1pub tmp]$ ls -ltr
-rw-r--r--  1 oracle oinstall  1692 Oct 17 00:15 rac2keys.tmp


After you have done all above now proceed further

ON NODE 1

[oracle@rac1pub ~]$ cd /home/oracle/.ssh/
[oracle@rac1pub .ssh]$ cat /tmp/rac2keys.tmp >> authorized_keys

ON NODE 2

[oracle@rac2pub ~]$ cd /home/oracle/.ssh/
[oracle@rac2pub .ssh]$ cat /tmp/rac1keys.tmp >> authorized_keys


Gather ssh fingerprints of all RAC nodes.
Now get fingerprints of all possible interfaces / nodes of this RAC setup using ssh.

NOTE: You need to exit after each successful logon to avoid confusion.

On Node 1:
ssh rac1pub.kasb.com
ssh rac1pvt.kasb.com
ssh rac2pub.kasb.com
ssh rac2pvt.kasb.com
ssh rac1pub
ssh rac1pvt
ssh rac2pub
ssh rac2pvt

On Node 2:
ssh rac1pub.kasb.com
ssh rac2pub.kasb.com
ssh rac1pvt.kasb.com
ssh rac2pvt.kasb.com
ssh rac1pub
ssh rac1pvt
ssh rac2pub
ssh rac2pvt

I put two practical example for your more uderstanding that what I am trying to say in Gather ssh fingerprints of all RAC nodes.
============================================================================
[oracle@rac1pub .ssh]$ ssh rac1pub.kasb.com
The authenticity of host 'rac1pub.kasb.com (192.168.1.191)' can't be established.
RSA key fingerprint is 97:b4:a8:13:a1:76:57:44:e2:0b:60:c1:b8:13:db:27.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'rac1pub.kasb.com,192.168.1.191' (RSA) to the list of known hosts.
[oracle@rac1pub ~]$ exit
logout
Connection to rac1pub.kasb.com closed.

[oracle@rac1pub .ssh]$ ssh rac1pvt.kasb.com
The authenticity of host 'rac1pvt.kasb.com (10.0.0.1)' can't be established.
RSA key fingerprint is 97:b4:a8:13:a1:76:57:44:e2:0b:60:c1:b8:13:db:27.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'rac1pvt.kasb.com,10.0.0.1' (RSA) to the list of known hosts.
Last login: Sun Oct 16 23:34:57 2011 from rac1pub.kasb.com
[oracle@rac1pub ~]$ exit
logout
=================================================================================

Verfication of Passwordless SSH connection
Here your passwordless connection established between both node

ON NODE 1You can see date of both nodes will be appeared without password prompt

[oracle@rac1pub ~]$ ssh rac1pub date
Sun Oct 16 23:41:29 PKT 2011
[oracle@rac1pub ~]$ ssh rac2pub date
Sun Oct 16 23:41:03 PKT 2011
[oracle@rac1pub ~]$ ssh rac2pvt date
Sun Oct 16 23:41:20 PKT 2011

ON Node 2
[oracle@rac2pub ~]$ ssh rac1pub date
Sun Oct 16 23:42:18 PKT 2011
[oracle@rac2pub ~]$ ssh rac1pvt date
Sun Oct 16 23:42:26 PKT 2011
[oracle@rac2pub ~]$ ssh rac2pvt date
Sun Oct 16 23:41:59 PKT 2011
[oracle@rac2pub ~]$ ssh rac2pub date
Sun Oct 16 23:42:06 PKT 2011
[oracle@rac2pub ~]$ ssh 192.168.1.191 date
Sun Oct 16 23:42:52 PKT 2011


Congratualtion your SSH have now Configured for Oracle Rac 10g.

LODING DATA INTO CLOB COLUMN BY SQL LOADER

THIS EXAMPLE IS FOR LODING DATA INTO CLOB COLUMN BY SQL LOADER

STEP 1  Creating lob table;

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

SQL> conn scott/tiger

Connected.

SQL>

SQL> create table test_clob(

    id number,

    ename varchar2(10),

    resume clob);

STEP 2 first create 2 text file on OS and enter some data in it.

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

[oracle@ocs sqlloader]$ vi resume1.txt

name Syed faraz Ahmed

fname Ahmed

dob 01 Jan 1975

======save file =======

[oracle@ocs sqlloader]$ vi resume2.txt

name zaman ahmed tatari

fname naseem ahmed tatari

dob 29 mar 1980

======save file =======

STEP 3 NOW CREATE CSV data file

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

[oracle@ocs sqlloader]$ vi emp.csv

1,zaman,resume1.txt

2,faraz,/home/oracle/sqlloader/resume2.txt

===========save file ================

STEP 4 Now create Control file

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

[oracle@ocs sqlloader]$ vi testclob.ctl

load data

infile '/home/oracle/sqlloader/emp.csv'

into table test_clob

replace

fields terminated by ',' trailing nullcols

(

id integer external(3),

 ename char(10),

 clob_file_loc filler char(100),

 resume lobfile(clob_file_loc) terminated by EOF

)

===========save file ================

NOTE: In above control if you use Blob column the syntax is same.

clob_file_loc filler char(100) this filler column in sqlloader file which will not load into the table. This is use to get filename that use in next line.

STEP 5 Load data by  SQL Loader

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

sqlldr scott/tiger control=testclob.ctl

STEP 6 Verify  Load data by sqlplus

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

SQL> conn scott/tiger

Connected.

SQL>

SQL>

SQL> select * from test_clob;

        ID ENAME

---------- ----------

RESUME

------------------------------------------------------------------------------

         1 zaman

name Syed faraz Ahmed

fname Ahmed

dob 01 Jan 1975

         2 faraz

name zaman ahmed tatari

fname naseem ahmed tatari

dob 29 mar 1980

Understanding on Fine Grained Auditing (FGA)

 Follow the steps for understanding on FGA.

LOGIN FORM SYS or SYSTEM USER to sqlplus

Creating Fine Grained Auditing Policy on Scott emp table

begin
dbms_fga.add_policy(
object_schema=>'SCOTT',
object_name=>'EMP',
policy_name=>'EMP_SAL_CHK'
 ,audit_condition=>'deptno=10'
 ,audit_column => 'sal'
 ,enable=>TRUE
 ,Statement_types=>'SELECT,INSERT,UPDATE,DELETE'
);
end;

RUN BELOW QUERIES ONE BY ONE TO GENERATE AUDIT

run queries from scott user

QUERY NO 1
SQL> select * from emp where deptno=10;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
      7839 KING       PRESIDENT            17-NOV-81       5000
      7934 MILLER     CLERK           7782 23-JAN-82       1300

QUERY NO 2
SQL> select * from emp where sal>6000;

no rows selected

QUERY NO 3
SQL> select * from emp where sal=950;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- --------------------
      7900 JAMES      CLERK           7698 03-DEC-81        950                30

QUERY NO 4
SQL> select ename , job from emp where empno=7839;

ENAME      JOB
---------- ---------
KING       PRESIDENT

QUERY NO 5
SQL>  select ename , job, sal from emp where empno=7839;

ENAME      JOB              SAL
---------- --------- ----------
KING       PRESIDENT       5000

QUERY NO 6
SQL> select ename , job, deptno from emp where empno=7839;

ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10

QUERY NO 7
SQL> select ename , job, deptno from emp where deptno=10;

ENAME      JOB           DEPTNO
---------- --------- ----------
CLARK      MANAGER           10
KING       PRESIDENT         10
MILLER     CLERK             10

QUERY NO 8
SQL> select ename , job, sal from  emp where deptno=20;

ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK            800
JONES      MANAGER         2975
SCOTT      ANALYST         3000
ADAMS      CLERK           1100
FORD       ANALYST         3000

QUERY NO 9
SQL>  select ename , job, sal from  emp where deptno=10;

ENAME      JOB              SAL
---------- --------- ----------
CLARK      MANAGER         2450
KING       PRESIDENT       5000
MILLER     CLERK           1300


FROM SYS USER CHECK AUDIT QUERIES

SQL> select sql_text from dba_fga_audit_trail;
or

SQL> select lsqltext from fga_log$;

LSQLTEXT
---------------------------------
select * from emp where deptno=10
select ename , job, sal from emp where empno=7839
select ename , job, sal from  emp where deptno=10


REASONyou can see that ONLY QUERY no  1,5,9  were Audited.

WHY

The above 1,5,9 queries only audited because we selected sal column and
result of rows belongs to deptno 10.

additionally, if you avoid sal column in select list  & select any column of emp table and records still belongs to deptno 10 than audit will not be genrated because your audit column is SAL in policy. So both condition must be matched.

One more understanding

audit_condition=>'deptno=10'

above parameter in audit policy doesn't mean that this value present in where clause. it means that audit generate when SAL column selected its records belongs to department no 10.



Dropping  Policy

from sys or system user

begin
dbms_fga.drop_policy(
object_schema=>'SCOTT',
object_name=>'EMP',
policy_name=>'EMP_SAL_CHK'
);
end;

 

Secure Application Role

Step 1: Create separate security user

SQL> create user sec identified by sec;

User created.

SQL> grant dba to sec;

Step 2: Connect with security user and developed these objects

1. package and package body
2. create role
3. add some objects privileges to above role
4. grant execute privileges on package to a user.


sql> conn sec/sec

create or replace package sec_role_pkg authid current_user is
  procedure sec_role;
end ;

 create or replace package body sec_role_pkg is
 procedure sec_role is
 begin
  if sys_context('userenv','session_user')='HR' then
   dbms_session.set_role('MYROLE');
   end if;
  end ;
 end ;


SQL> create role Myrole identified using sec_role_pkg;

Role created.

SQL> grant select on scott.emp to myrole;

Grant succeeded.

SQL> grant execute on sec.sec_role_pkg to hr;

Grant succeeded.

Step 3: Now Connect form HR user to test role

SQL> conn hr/hr
Connected.

SQL> select * from session_roles;

ROLE
------------------------------
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE

ROLE
------------------------------
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA

16 rows selected.

Execute the procedure and check what role now HR have

SQL> exec sec.sec_role_pkg.sec_role;

PL/SQL procedure successfully completed.

SQL>  select * from session_roles;

ROLE
------------------------------
MYROLE

SQL>

SOME TEST OF HR USER
SQL> create table test1 (id number);
create table test1 (id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        17


SQL> select count(*) from employees;

  COUNT(*)
----------
       107


SQL> select count(*) from scott.dept;
select count(*) from scott.dept
                           *
ERROR at line 1:
ORA-00942: table or view does not exist