Oracle

Some of the queries in the table below can only be run by an admin. These are marked with (PRIV) at the description.

Version

SELECT banner FROM v$version WHERE banner LIKE 'Oracle%';
SELECT banner FROM v$version WHERE banner LIKE 'TNS%';
SELECT version FROM v$instance;

Comments

SELECT 1; -- comment

Current User

SELECT user FROM dual;

List Users

SELECT username FROM all_users ORDER BY username;
SELECT name FROM sys.user$;

List Password Hashes (PRIV)

#Oracle version <= 10g
SELECT name, password, astatus FROM sys.user$. astatus tells you if acct is locked

#Oracle version 11g
SELECT name,spare4 FROM sys.user$

List Privileges (PRIV)

SELECT FROM session_privs;
SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS;

#List a user's privs
SELECT FROM dba_sys_privs WHERE grantee = 'DBSNMP';

#Find users with a particular priv
SELECT grantee FROM dba_sys_privs WHERE privilege = 'SELECT ANY DICTIONARY'; 

List DBA Accounts (PRIV)

SELECT DISTINCT grantee FROM dba_sys_privs WHERE ADMIN_OPTION = 'YES';

Current Database

SELECT global_name FROM global_name;
SELECT name FROM v$database;
SELECT instance_name FROM v$instance;
SELECT SYS.DATABASE_NAME FROM DUAL;

List Databases

#List schemas (one per user)
SELECT DISTINCT owner FROM all_tables;

List Tables

SELECT table_name FROM all_tables;
SELECT owner, table_name FROM all_tables;

List Columns

SELECT column_name FROM all_tab_columns WHERE table_name = 'blah';
SELECT column_name FROM all_tab_columns WHERE table_name = 'blah' and owner = 'foo';

Find Tables from Column Name

#NB: table names are upper case
SELECT owner, table_name FROM all_tab_columns WHERE column_name LIKE '%PASS%';

Hostname, IP Address

SELECT UTL_INADDR.get_host_name FROM dual;
SELECT host_name FROM v$instance;

#Gets IP address
SELECT UTL_INADDR.get_host_address FROM dual;

#Gets hostnames
SELECT UTL_INADDR.get_host_name(’10.0.0.1′) FROM dual;

Location of DB Files

SELECT name FROM V$DATAFILE;

Get all tablenames in One String

#When using union based SQLi with only one row
SELECT rtrim(xmlagg(xmlelement(e, table_name || ',')).extract('//text()').extract('//text()') ,',') from all_tables