dbms_sql package security question

From: Kirill Richine <kirill_at_cs.ualberta.ca>
Date: 1998/02/19
Message-ID: <6ci9ve$a3o$1_at_scapa.cs.ualberta.ca>#1/1

Hi!

We have Oracle 7.1 and PL/SQL 2.1
[Quoted] [Quoted] It appears to be possible to include ddl in a stored procedure by using sql_dbms package.

Suppose, owner O owns table A.
When user U tries to issue ddl on table A like:

drop table A;

the user U has to have the necessary privilege on a non-owned object.

However, if O writes a stored procedure drop_table as:

create or replace procedure drop_table (   p_TableName in all_tables.table_name%TYPE ) as

  v_Cursor      NUMBER;
  v_NumRows     NUMBER;

begin
  v_Cursor := dbms_sql.open_cursor;
  dbms_sql.parse (v_Cursor, 'drop table ' || p_TableName, dbms_sql.v7);   v_NumRows := dbms_sql.execute (v_Cursor);   dbms_sql.close_cursor (v_Cursor);
end;
/

[Quoted] and grants execute on it to U, then U can drop any of O's tables!

[Quoted] Is this considered to be a security hole, and if yes, has there been any motion to fix it?

[Quoted] Is this considered to be a good programming practice when this functionality is intended, i.e., instead of granting the privileges to O explicitly, we just write a procedure that does it?

Thank you.
k& Received on Thu Feb 19 1998 - 00:00:00 CET