All apps dba Blog is the blog contributed by Doyensys Employees, With the view to share the knowledge out of their experience.
Doyensys Is a Fast Growing Oracle Technology Based Solutions Company Located in the US And Offshore Delivery Centers in India. With rich experience In Oracle E-Business, Oracle Database, Oracle Application Express (APEX), Oracle J Developer, Oracle OBIEE and E-Business Suite

DOYENSYS Knowledge Portal

Sunday, January 8, 2017

Oracle Database and Applications Security Audit Part 2

This post is continuation of "Oracle Database and Applications Security Audit Part 1"There are small correction in Part 1, Instead of netstat -an , better to use netstat -anp , if you any questions or comments, please do comment on the respective posts, I will reply back as soon as i can.One more point would like to mention is that My experiences related to security are around Oracle Database and Oracle E-Business Suite (/ Oracle Applications), So I will be focusing mainly on Oracle Database and Oracle E-Business Suite (/Oracle Applications).So..Let us focus on the next areas to be audited (/paid attention) , which is/are a key for data security. Oracle Database : Few things to recollect from previous post (part 1) related to Oracle Database ... 1. Default Port 1521 should not be used.2. Highly recommended to set TCP.INVITED_NODES in sqlnet.oraGuidelines:A. Auditing:1. Most of Us reading this post , know that : Related to Data - There are Insert, Update, Delete , Truncate , Select , execute Pl/Sql objects access.Related to Database object - There are Create , Alter , Drop objects access.Related to Database System level privileges related to data access - Update any table, Insert any table, delete any table , select any table , execute any package, create any table,alter any table and drop any table.All the above access rights should be tracked and audited ( Select - Up to you ) on key sensitive objects , which stores and retrieves Companies financial - Asset , Customer , Supplier , Banks , Credit Card and/or any Sensitive data.

2. When you design custom schema for applications, identify the key objects which are going to store sensitive data , and design to create and store them in a separate schema (different than custom Applications schema), This will help to control the access , configure , track and audit the schema.3. No user should have direct access to applications schema , specifically related to Oracle Applications - No users should have direct access to APPS schema in production. All users should have named users like NKARANAM , HSINGH. All named user should be audited , access to shared to any users should be controlled , approved and reported very frequently.4. APPS schema/Custom schemas password should be changed every Quarter, and should be reported. Password sheet/software which hold the latest password should be controlled.

5. Database Roles should be created based on the categories of users like role to view the data, role to modify the data, role to execute the packages,role to alter and drop the objects. This will help to ensure which list of access has been provided to whom , though this point is not related to security - it will help in implementing the above points.6. Considering Performance , Auditing should be carefully planned , and access on point 1 should not be audited on all objects on schema which holds sensitive data. We need to plan and track operations based on the importance of data it holds.

B. AvailaibilityHow availability is related to database security: One of key feature of data security is that Data should be available to us for access.We have lot of options and recommendations for Database Availability.All options are best for certain situations and reasons, each options has its own trade offs , hence I'M not going to say which is best and which are not.1. Backup : This is Most common, budgeted options. From the Security Point of View, Database and transaction/archive logs backups should be planned in such a way that the database should be restored till point in time. database parameters related to archive logs generation should be tuned to met the recovery requirement. (FAST_START_MTTR_TARGET). (i will be write a separate series on backup configuration and best practices). Frequent restoration should be planned, tested and documented. So that we can ensure that data can restored at any point of time.Backups should be monitored and in event of backup failures, the restoration plan should be re-looked , backup schedule should be changed and restored.Backup set should also be planned to send of offsite, so that the database can be restored in case of natural calamities.2. Physical Standby : This is one of the advanced options, through which database can be mirrored, and synced up in remote site (basically via archivelogs) from the production database . This option requires additional database license as new oracle home and standby will be created in remote server. Through Physical Standby, database can be brought up point in time in read write mode within few mins of crash occurrence in production database, whereas in backup option, We need to wait till the time the backups gets restored and archivelog get applied to bring up the database to read write and point in time.Standby should be monitored such that it is getting synced up with production database , (archive logs are getting copied and getting applied).Frequent switchovers should be planned, tested and documented. So that we can ensure that data can restored at any point of time.

3. Clusters (Real Application Clusters) : This is one of the advanced option through the database will be stored in shared storage and will be accessible from multiple server, This option also need additional investment. Clusters mainly useful in load balancing the performance of production database and in case of crash of any server , the other available servers can be used for accessing the data , which leads to 99% availability. RAC + STANDBY (Option 2) are generally considered best combo, as it ensure data availability when events occurred between server and within data center.C. Patching : Oracle release Critical Patch Updates (Security patches) and Patch Set updates every Quarter. It is highly recommended and mandatory from security update to be applied to production as soon as it released after testing it in non-production.D. MASKING :Whenever lower instances/ non-production database getting refreshed/cloned using production database , Tables holding Sensitive data should be masked either via sql/plsql/program or via oracle tools , as the lower instance will be accessed by all developers, contractors/external vendors and project teams.

E. Reporting related to database security :

Below are the list of reports related to database security recommended to be created and reviewed frequently.1. List of named users and their operations in the database.2. List of named users, their system and object level privileges they have. ensure that they have only the approved privileges.3. List of changes made to tables holding Sensitive data.4. Result of last backup and Switch over (when using physical standby) performed, and any proactive changes needs to be done.5. When and what was the latest security patch applied, and delay of any to be reviewed.6. When was the last time APPS Schema/Database custom schema password got changed.7. (take a baseline of database parameter before) list of changes made to any database parameters.Next Post : Application Security--Narasimha Rao