Setting up Optach environment variable

Setting up Optach environment variable :

For Korn / Bourne shell:

% export PATH=$PATH:$ORACLE_HOME/OPatch

For C Shell:

% setenv PATH $PATH:$ORACLE_HOME/OPatch

Simple password encryption package to demonstrate how

rem -----------------------------------------------------------------------
rem Purpose:   Simple password encryption package to demonstrate how
rem                  values can be encrypted and decrypted using Oracle's
rem                  DBMS Obfuscation Toolkit
rem Note:        Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------


---- create table to store encrypted data

-- Unable to render TABLE DDL for object ATOORPU.USERS_INFO with DBMS_METADATA attempting internal generator.
CREATE TABLE USERS_INFO
(
  USERNAME VARCHAR2(20 BYTE)
, PASS VARCHAR2(20 BYTE)
)users;

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

CREATE OR REPLACE PACKAGE PASSWORD AS
   function encrypt(i_password varchar2) return varchar2;
   function decrypt(i_password varchar2) return varchar2;
END PASSWORD;
/
show errors


CREATE OR REPLACE PACKAGE BODY PASSWORD AS

  -- key must be exactly 8 bytes long
  c_encrypt_key varchar2(8) := 'key45678';

  function encrypt (i_password varchar2) return varchar2 is
    v_encrypted_val varchar2(38);
    v_data          varchar2(38);
  begin
     -- Input data must have a length divisible by eight
     v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));

     DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
        input_string     => v_data,
        key_string       => c_encrypt_key,
        encrypted_string => v_encrypted_val);
     return v_encrypted_val;
  end encrypt;

  function decrypt (i_password varchar2) return varchar2 is
    v_decrypted_val varchar2(38);
  begin
     DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(
        input_string     => i_password,
        key_string       => c_encrypt_key,
        decrypted_string => v_decrypted_val);
     return v_decrypted_val;
  end decrypt;


end PASSWORD;
/
show errors

-- Test if it is working...

select password.encrypt('PASSWORD1') from dual;
select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select password.encrypt('PSW2') from dual;
select password.decrypt(app_password.encrypt('PSW2')) from dual;


--- Insert encrypted Password ---

insert into USERS_INFO values ('redddy',( select password.encrypt('REDDY1') from dual) );
select password.decrypt((pass)) from USERS_INFO where USERNAME='redddy';

update rows from multiple tables (correlated update)

Cross table update (also known as correlated update, or multiple table update) in Oracle uses non-standard SQL syntax format (non ANSI standard) to update rows in another table. The differences in syntax are quite dramatic compared to other database systems like MS SQL Server or MySQL.
In this article, we are going to look at four scenarios for Oracle cross table update.

Suppose we have two tables Categories and Categories_Test. See screenshots below.

lets take two tables TABA & TABB:

Records in TABA:

Records in TABB:

1. Update data in a column LNAME in table A to be upadted with values from common column LNAME in table B.

The update query below shows that the PICTURE column LNAME is updated by looking up the same ID value in ID column in table TABA and TABB.

 update TABA A
set (a.LNAME) = (select B.LNAME FROM TABB B where A.ID=B.ID);

2. Update data in two columns in table A based on a common column in table B.

If you need to update multiple columns simultaneously, use comma to separate each column after the SET keyword.

update TABA A
set (a.LNAME, a.SAL) = (select B.LNAME, B.SAL FROM TABB B where A.ID=B.ID);

How to Secure our Oracle Databases

How Secure can we make our Oracle Databases??

This is a routine question that runs in minds of most database administrators.  

HOW SECURE ARE OUR DATABASES. CAN WE MAKE IT ANYMORE SECURE.

I am writing this post to share my experience and knowledge on securing databases. I personally follow below tips to secure my databases:

 1. Make sure we only grant access to those users that really need to access database.

2. Remove all the unnecessary grants/privileges from users/roles.

3. Frequently audit database users Failed Logins in order to verify who is trying to login and their actions.

4. If a user is requesting elevated privileges, make sure you talk to them and understand their requirements.

5. Grant no more access than what needed.

6. At times users might need access temporarily. Make sure these temporary access are revoked after tasks are completed.

7. Define a fine boundary on who can access what??

8. Use User profiles / Audit to ensure all activities are tracked.

9.  Enforce complex password. Here is the Link on how to do it 

10 Use Triggers to track user activity.

11. Make sure passwords are encrypted in applications, this can be potential threat if you application code has been compromised.

12. Add password to your listener.

13. Allow access only from needed/known servers/clients. Use Valid_node_checking Link on how to restrict access to servers/clients.