Re: Validating dynamic SQL

I will not question the idea... others have sufficiently done that.

Couple of things come to mind:

1. Dynamically enabled roles with passwords might help, but probably not. Do users connect to the database from any other tool? If so, might not work.
2. Always connect as a user that is the only user with privs on tab1 . Of course all the other privs needed would have to be granted to this 1 user.
3. Sort of like 2, maybe do an "alter session set current_schema = userwithtab1_priv " . Not sure if this would prevent what you want to prevent. Just an idea and it is late ... so this may not work either.

Good luck.

-- 

"Jeremy" <newspostings_at_hazelweb.co.uk> wrote in message
news:MPG.195401191e5f0c5a989735_at_news.cis.dfn.de...


> Loking to put a feature into an app which allows a bit of SQL, written

> by a 'developer', to be stored in a table and then executed (via execute

> immediate) from another pl/sql procedure. The idea is that this allows

> good deal of flexibility in a certain area of the app.

>

> The purpose is to allow the developer to, via this piece of 'dynamic'

> sql, populate a named table let's call it 'TAB1'.

>

> The logic that the developer could use to populate the table may be

> complex - so we are going to allow for the 'dynamic' sql to be a pl/sql

> block.

>

> What we need to be able to do is to parse this 'dynamic' SQL when it is

> initially written to ensure that it doesn't drop / alter / create

> objects nor delete/update or otherwise interfere with the database.

>

> So the question is: is there an easy way to establish that the only

> objects subjected to DDL or DML in the 'dynamic' SQL is TAB1?

>

> Even of there is, I suppose there could be calls to OTHER procedures

> which perform other DML....

>

> Alternatively, I wonder if there is a way of setting a restriction

> immediately prior to execution that, until the restriction is unset, no

> DDL is permitted and the only object on which DML may be performed is

> TAB1?

>

> Incidentally, TAB1 will be defined as a temporary table will all rows

> deleted upon commit.

>

> Working in an 8.1.7 environment.....

>

> TIA

>

>

> -- 

>

> jeremy