Re: dbms_sql package security question

Kirill Richine wrote:
>
> Hi!
>
> We have Oracle 7.1 and PL/SQL 2.1
> 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;
> /
>
> and grants execute on it to U, then U can drop any of O's tables!
>
> Is this considered to be a security hole, and if yes, has there been
> any motion to fix it?
>
> 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&

NOTE: dbms_sql is the exception to normal procedure execution. It executes as the EXECUTOR of the procedure, whereas others execute as the OWNER of the procedure...

In this way, it can avoid the problems you have correctly foreseen..

Cheers

-- 
==========================================
Connor McDonald
BHP Information Technology
Perth, Western Australia
"These views mine not BHP..etc etc"

"The only difference between me and a madman is that I am not mad."