Re: dbms_sql package security question

Kirill Richine (kirill_at_cs.ualberta.ca) wrote:
: 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.

Yes.

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

Yes again.

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

No. This is an obvious and inevitable situation: procedures execute in the security domain of the owner of the procedure.

You have to fix the "security hole" yourself -- by putting code in the procedure to check who is calling it, and what table they are trying to drop, and then either dropping the table or (if your security validation fails) returning an error.

Depending on your security requirements, this can be a fairly complicated validation. That's unfortunate, but (as I said) inevitable.

: 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?

Well ... you're not opening up quite the *same* can of worms as "granting the privileges to U". The privilege required to drop a table in someone else's schema are, if I recall correctly, "DROP ANY TABLE." By creating a stored procedure to allow U to drop any of O's tables, you are avoiding the horrible security consequences of allowing U to drop *any* table (including, for example, sys.tab$...).

Generally, if you can allow users to perform required actions using the normal system and table-level privileges, it is better to do that than to create a procedure that uses DBMS_SQL. But in this case you cannot.

-- 
-bn
random_at_interaccess.com	(PGP 2.6.2 public key available on request)
"There is no .signature -- only ZUUL!"