1. Populate Audit Columns using database triggers – As a general rule, 4 Auditing columns (who the row was created by, when the row was created, who modified it and when it was modified):AUDIT_REC_UPDT_DTS TIMESTAMP(6)
    AUDIT_REC_UPDT_DB_USER_NM VARCHAR2(256)
    AUDIT_REC_CREATE_DTS TIMESTAMP(6)
    AUDIT_REC_CREATE_DB_USER_NM VARCHAR2(256)
    These row auditing triggers should be thorougly tested in development, test and pre-production database environments before it is implemented in production.
  2. NO TOAD in QA / PROD unless in restricted access list – Toad will be restricted in QA and Production environments. Key QA and development staff will have read-only access to QA and Production environments. All read activity will be audited. During business hours, QA and Development read-only accounts will be governed by resource consumption groups.
  3. AUDIT and restrict DBA access – DBA access should be closely monitoring and audited. Except for SEV 1 and 2 situations, DBAs are not to directly login using SYS or SYSTEM accounts. They must use their own individual _DBA accounts. Except for maintenance and system outages, DBAs are not to login as the oracle unix account. DBAs must login with their individual unix IDs and utilize sudo for oracle-specific commands. Audit On-Connect to capture who is logging on and from where/when. Capture DDL activity for ALL schemas. Produce Daily changed objects report. Additional auditing requirements will be coordinated with Application Owner and user. Setup Audit of the SYS account.
  4. Enable database on-logon trigger – Do not allow TOAD to connect to production database unless you are assigned DBA or RO_TOAD role with limited profiles. Also, application user accounts can only connect from authorized application servers listed in the ORACLE.EDBA_APPLICATION_USER table. The columns that will house the data are APPLICATION_USERNAME and APPLICATION_SERVER. FULL Database audit should be enabled for every account except for schema owner accounts. You do not have to enable it for the application user id because the ON-LOGON trigger will enforce login only from VALID list of application servers.
  5. Application accounts only allowed connection from application servers – Please acquire application server names to all of the environments, DEV, QA and PROD. As part of the security measures, only the application users should be allowed to connect from the authorized list of application servers. The list of application servers should be listed in a table and used as verification as an on-logon database trigger
  6. Minimum 25 character password restriction for application user passwords- Application user ID password will not be less than 25 characters. We want to minimize a person’s ability to memorize the password.
  7. Protect access to database dictionary views – SELECT_CATALOG_ROLE generally will not be granted to NON-DBA members. Instead, we can create them web pages to run whatever queries they are trying to capture. NO account will have SELECT ANY TABLE except for the RODBA account. NO account will have select access to DBA_USERS or sys.link$
  8. Password aging and verification for non-application user id for QA / PROD – USERID and PASSWORD cannot be the same. We can download or purchase a dictionary and hash out the password hash_value and enforce password requirements (such as must have a 0-9 or meta character)
  9. FOR QA and PROD environments only: We recommend password aging for NON-application user and schema owner accounts. In addition, we recommend password lockout for NON-application user and schema owner accounts.
  10. Implement ROLE based security. First, you should consider implementing grants to database roles. Second, create a hierarchy of roles to grant to application users. You can also consider creating database roles for reporting accounts
  11. A script should be created to detect missing table/view/sequences synonyms to user account(s). Another script should also be created to detect missing table/view/sequences synonyms to database role(s). Use public synonyms as the first option
  12. Application user lockdown – First, revoke access of baseline schema. Start implementing security standards on development databases.

Posted by: Charles Kim @ DBAExpert.com