Oracle Database Experts

If you are performing any kind of auditing on the database, we recommend that you move the aud$ tablespace from the system tablespace to another tablespace. In this example, we are moving the aud$ tablespace to a tablespace called SYSAUDIT_D.

If you are on ASM, you would change the file location to an ASM diskgroup such as ‘+data’.

def DB='&1'
def FS='&2'

CREATE TABLESPACE SYSAUDIT_D DATAFILE
  '/data/oracle/&DB/&FS/sysaudit_d_01.dbf' SIZE 200m AUTOEXTEND OFF
LOGGING ONLINE PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

create table audx tablespace SYSAUDIT_D as select * from aud$ where 1 = 2;
rename AUD$ to AUD$$;
rename audx to aud$;
drop index i_aud1;

create index i_aud1 on aud$(sessionid, ses$tid) tablespace SYSAUDIT_D;

set echo off

Posted by Charles Kim, Oracle ACE

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

First download the knoppix security tools distribution (STD) from the s-t-d.org website:
http://s-t-d.org/download.html

To change a windows password is simple. It is simple as following four little steps discussed below.

Step #1:

First boot off the Knoppix-std cdrom. From the Xwindows, Right Click and select XShells and Select Aterm to get an x-window.

Look at the /etc/fstab file and determine the ntfs file system to mount.

Step #2: Switch user to root and mount the windows ntfs device for read write as shown below:

Step #3: Change directory to the windows32\config directory:

Â

Using the chntpw utility (from Knoppix-std) cdrom, change the password for Administrator or any user. In this example, we will change the user of the Red Hat account.

Before, let’s list all the accounts available on the windows server:

Step #4: To change the password, change the –l to –u followed by a legitimate user account.

Set Current Schema
alter session set current_schema=appuser;

# ————————————————-
# Another powerful way
# This is the TRUE proxy user
# ————————————————-
SQL> create user su identified by oracle123;

User created.

SQL> alter user rodba grant connect through su;

DBATOOLS > sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Aug 5 17:36:04 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

05-APR-07 SQL> connect su[rodba]/oracle123;
Connected.
RODBA SQL> show user
USER is “RODBA”
Another security check that we have to do now is:
SYS SQL> select * from dba_proxies;

PROXY CLIENT AUT
—————————— —————————— —
AUTHORIZATION_CONSTRAINT ROLE PROXY_AUT
———————————– —————————— ———
SU RODBA NO
PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASE

Passwords hashed using the Secure Hash Algorithm (SHA) cryptographic hash function SHA-1. Oracle Database uses the SHA-1 verifier is to authenticate the user password and establish the session of the user. In addition, it enforces case sensitivity and restricts passwords to 160 bits. The advantage of using the SHA-1 verifier is that it is commonly used by Oracle Database customers and provides much better security without forcing a network upgrade. It also adheres to compliance regulations that mandate the use of strong passwords being protected by a suitably strong password hashing algorithm