Friday, July 14, 2017

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);

Friday, June 2, 2017

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.  

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.