Using SSH to forward ports

Ports on remote machines can be forwarded over a SSH tunnel using the ssh command line tool. Using the ssh config file it is possible to set up many ports to be forwarded at the same time running one command. Putty can be used to achieve similar results on Windows.

To forward a port the syntax is the ssh command followed by the local port then the local host then remote port and host
ssh -L 8080:me.local.org:80 myuser@s1.remote.org

To forward many ports at once created a config file in ~/.ssh/config as follows

host localme
  HostName s1.remote.org
  User myuser
  LocalForward 8080 me.local.org 80
  LocalForward 8443 me.local.org 443

Then run ssh localme and give the password for myuser

Specifying comments in SQL to support auditing in Oracle

Adding comments to an SQL statement in Oracle is a good way of providing context to auditing and sql forensic activities directed against an Oracle database audit log. The Oracle Audit trail, and related software like Oracle Audit Vault will capture any comments embedded within an sql statement as part of its normal activities. This, then, provides a way for applications built on Oracle to provide metadata that can assist in analytics performed on the audit record.

As an example, an online shopping cart may audit searches by keyword and may audit individual product pages. At the database level the sql run to generate such pages can be marked with a comment and a textual description of the functionality of the application that uses such a query. This permits application level auditing to be performed in the database, and not require that the auditor is educated in the full set of statements that may be performed by the application. This method of auditing can be done in addition to application level auditing functionality.

Comments are also useful in the enterprise manager to look up statements that are candidates for automated sql tuning.

To add comments to the statement use the following syntax -

SELECT /* [text] */ .....

When the statements require the use of hints ensure the comments do not get in the way of the correct hint definition. Ensure the comments are of the form


{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint [text]]... */

Adding User Accounts to OpenLDAP

To add user accounts to an LDAP repository determine what account objects are provided by the LDAP software and ensure the correct schema are enabled. In openLDAP, as an example, I've used the posix account object to create a user with a defined dn and added that user with a password to a posixGroup using the users dn as memberuid.


In slap.conf add:

include     /usr/local/etc/openldap/schema/core.schema
include         /usr/local/etc/openldap/schema/cosine.schema
include         /usr/local/etc/openldap/schema/nis.schema

access to attr=userPassword
by self =xw
by anonymous auth
by * none

access to *
by self write
by users read
by * none


suffix "dc=us,dc=ldap,dc=com"
rootdn "cn=Manager,dc=us,dc=ldap,dc=com"

rootpw {SSHA}Bh/flrQNsDkzFV9c1C6uKkY7UuoPvuKU

(root password created using slappasswd)

create an initial structure in initial.ldif -

dn: dc=us,dc=ldap,dc=com
objectClass: dcObject
objectClass: organization
o: us.ldap.com
dc: us

dn: ou=People,dc=us,dc=ldap,dc=com
objectClass: organizationalUnit
objectClass: top
ou: People

dn: ou=Groups,dc=us,dc=ldap,dc=com
objectClass: organizationalUnit
objectClass: top
ou: Groups

run

ldapadd -x -D cn=Manager,dc=us,dc=ldap,dc=com -W -f initial.ldif
(Use password from slapd.conf)

Create a user in adduser.ldif

dn: uid=user5,ou=People,dc=us,dc=ldap,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
cn: user5
uid: user5
uidNumber: 3000
gidNumber: 3000
homeDirectory: /home/user1
#userPassword: {SSHA}4t7lrhU5hIbMyqQMMYoRK35+hhC6FZtB
userPassword: welcome1



ldapadd -x -D "cn=Manager,dc=us,dc=ldap,dc=com" -W -f adduser.ldif

Create a group in addgroup.ldif

dn: cn=admingroup,ou=Groups,dc=us,dc=ldap,dc=com
objectClass: top
objectClass: posixGroup
cn: admingroup
userPassword: <password>
gidNumber: 3000

ldapadd -x -D "cn=Manager,dc=us,dc=ldap,dc=com" -W -f addgroup.ldif

add the user to the group in adusertogroup.ldif

dn: cn=admingroup,ou=Groups,dc=us,dc=ldap,dc=com
changetype: modify
add: memberuid
memberuid: uid=user5,ou=People,dc=us,dc=ldap,dc=com
# this must be the dn of the user to work with WebLogic query for
# Static Group DNs from Member DN filter - (&(memberUid=%M)(objectClass=posixGroup))

ldapadd -x -D "cn=Manager,dc=us,dc=ldap,dc=com" -W -f addusertogroup.ldif

Now search using

ldapsearch -x -w secret1  -D "cn=Manager,dc=us,dc=ldap,dc=com"  -b
"dc=us,dc=ldap,dc=com" '(objectclass=*)'

#search using user5 pwd welcome1
ldapsearch -x -w welcome1  -D "uid=user5,ou=People,dc=us,dc=ldap,dc=com"  -b
"dc=us,dc=ldap,dc=com" '(objectclass=*)'
# returns directory entries

Oracle 11g Auditing Failed Statements

To find out what queries cause ORA-00942 - table or view does not exist errors you can use the auditing features of Oracle. You can audit successful as well as unsuccessful attempt to run particular queries against the Oracle database.

audit select table by access whenever not successful; -- also insert table, delete table, update table

To read the audit trail -

select * from dba_audit_trail

To make sure auditing is on do -
SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \DB10G\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
SQL>
Auditing is disabled by default,
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

as SYSDBA
alter system set audit_trail = 'db_extended' scope=pfile; -- or none, os, db, xml, xml_extended

db_extended audits sql_text and bind values;

SHUTDOWN
STARTUP


Otherwise do this from
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2975793633621#284628100346267414

create table t( id number, seq number, msg varchar2(4000) ); create sequence s; create or replace trigger failed_to_extend_temp after servererror on database declare l_sql_text ora_name_list_t; l_n number; begin if ( is_servererror(942) ) then insert into t values ( s.nextval, 1, 'ora_sysevent = ' || ora_sysevent ); insert into t values ( s.currval, 2, 'ora_login_user = ' || ora_login_user ); insert into t values ( s.currval, 3, 'ora_server_error = ' || ora_server_error(1) ); l_n := ora_sql_txt( l_sql_text ); for i in 1 .. l_n loop insert into t values ( s.currval, 3+i, 'l_sql_text(' || i || ') = ' || l_sql_text(i) ); end loop; end if; end;

Configuring Database Vault in Oracle 11gR2 after database install

Database Vault is an additional option on the Oracle database that allows management of sensitive data to be performed by a data manager that is separate and distinct from the database administrator role. It allows the DBA to perform database maintenance tasks without ever having rights to view sensitive data. The option is critical where privacy of data is required for PII or PHI requirements.

Some scripts and products that are designed to run on Oracle will require the database vault to be disabled.

To enable and disable database vault follow the commands below:

After installing Oracle 11gR2 Enterprise Edition with Database Vault option  :

Checking if Oracle Database Vault is enabled

select * from v$option where parameter = 'Oracle Database Vault';

PARAMETER VALUE
----------------------------- -----------------------
Oracle Database Vault FALSE

emctl stop dbconsole

Shut down the database instance.

For single-instance installations:
sqlplus sys as sysoper
Enter password: password
SHUTDOWN NORMAL
EXIT

For Oracle RAC installations:
srvctl stop database -d db_name

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_on ioracle

For Oracle RAC installations, run these commands on all nodes.
sqlplus sys as sysoper
Enter password: password
STARTUP
EXIT

select * from v$option where parameter = 'Oracle Database Vault';

PARAMETER VALUE
----------------------------- -----------------------
Oracle Database Vault FALSE


Then run dbca and configure the database This will add the db owner and db admin accounts.

Then log in to EM as dbowner.

To disable audit vault so you avoid insufficient privilege errors when running RCU for OBIEE or other middleware components do -


emctl stop dbconsole

sqlplus sys as sysoper
SHUTDOWN NORMAL
EXIT

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off ioracle

sqlplus sys as sysoper
STARTUP

Audit unsuccessful login in Oracle 11gR2

Oracle can audit access by object, statement and by privilege as well as via the fine grained auditing capability. This is the command to enable auditing of unsuccessful login which might indicate an attack on the database.


audit session whenever not successful;

Unlock Locked SYSDBA

Commands for unlocking a locked SYSDBA account in Oracle.


SET ORACLE_SID=orcl
sqlplus / AS SYSDBA
ALTER USER SYSTEM ACCOUNT UNLOCK
or ALTER USER SYSTEM IDENTIFIED BY <NEW PASSWORD> ACCOUNT UNLOCK