In my application, I want to be able to give users the flexibility of SQL-based queries but only through the use of the SELECT statement. I don't
want them to be able to use ALTER, DROP, etc.

I looked at the events a TQuery fires, in hopes that I could try to abort any 'destructive' type operations. But it didn't look like I could get 100%
coverage doing that.

Does DBISAM have a way to 'pre parse' an SQL input string?  If there were a pre-parse capability, I could ask DBISAM to parse an input
statement and then I could remove any non-SELECT statements before executing the SQL.

Any input / suggestions would be appreciated.

With thanks,
KM

<< In my application, I want to be able to give users the flexibility of
SQL-based queries but only through the use of the SELECT statement. I don't
want them to be able to use ALTER, DROP, etc.

I looked at the events a TQuery fires, in hopes that I could try to abort
any 'destructive' type operations. But it didn't look like I could get 100%
coverage doing that.

Does DBISAM have a way to 'pre parse' an SQL input string?  If there were a
pre-parse capability, I could ask DBISAM to parse an input statement and
then I could remove any non-SELECT statements before executing the SQL. >>

Are you using 4.x ?  If so, then just write some triggers for inserts,
updates, and deletes that raise exceptions when the user isn't the "correct"
user.  You can find out more information here:

http://www.elevatesoft.com/manual?action=mantopic&id=dbisam4&product=d&version=7&category=1&topic=8

under "Triggers".

--
Tim Young
Elevate Software
www.elevatesoft.com

"KM" <info@microlynxsystems.com> wrote in message
news:4B9A3737-E8C1-4411-9CD6-F626D2B02F32@news.elevatesoft.com...
> In my application, I want to be able to give users the flexibility of
> SQL-based queries but only through the use of the SELECT statement. I
> don't
> want them to be able to use ALTER, DROP, etc.
>
> I looked at the events a TQuery fires, in hopes that I could try to abort
> any 'destructive' type operations. But it didn't look like I could get
> 100%
> coverage doing that.
>
> Does DBISAM have a way to 'pre parse' an SQL input string?  If there were
> a pre-parse capability, I could ask DBISAM to parse an input
> statement and then I could remove any non-SELECT statements before
> executing the SQL.
>
> Any input / suggestions would be appreciated.

Seems to me that if the first word in query.text is not SELECT, you don't
want the whole query. Or if you find any of the other commands (;UPDATE
ALTER etc).You might need a bit more logic if you allow comments in the
query, but nothing that can't be done with a few lines of code.

Robert

>
> With thanks,
> KM
>

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:4299A522-1356-4F75-935C-040BC492F865@news.elevatesoft.com...
>
> << In my application, I want to be able to give users the flexibility of
> SQL-based queries but only through the use of the SELECT statement. I
> don't want them to be able to use ALTER, DROP, etc.
>
> I looked at the events a TQuery fires, in hopes that I could try to abort
> any 'destructive' type operations. But it didn't look like I could get
> 100% coverage doing that.
>
> Does DBISAM have a way to 'pre parse' an SQL input string?  If there were
> a pre-parse capability, I could ask DBISAM to parse an input statement and
> then I could remove any non-SELECT statements before executing the SQL. >>
>
> Are you using 4.x ?  If so, then just write some triggers for inserts,
> updates, and deletes that raise exceptions when the user isn't the
> "correct" user.  You can find out more information here:
>
> http://www.elevatesoft.com/manual?action=mantopic&id=dbisam4&product=d&version=7&category=1&topic=8
>
> under "Triggers".

Tim, aren't triggers table specific? Have not used them, so this is just
from reading the docs. If they are, they would not do the trick, unless you
put a ton of code in. The user SQL could conceivably modify ANY  table, or
for that matter drop it altogether.

Robert

All,

Thanks for your feedback. I think I might just scan the SQL input string for any 'forbidden words' - seems like the easiest thing to do.

KM

KM


My recommendation would be to use one of the query builder tools out there so the average user doesn't even get the chance of typing UPDATE.

If you want you can have a copy of my homebrewed one for V4.

Roy Lambert

Robert,

<< Tim, aren't triggers table specific? >>

Not in DBISAM, no.  The table name is passed into the trigger.

<< The user SQL could conceivably modify ANY  table, or for that matter drop
it altogether. >>

DROPs are an issue, yes, but the triggers will cover any updates.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:7D2CDD10-A4E4-4544-9B28-D20BED88CDFC@news.elevatesoft.com...
> Robert,
>
> << Tim, aren't triggers table specific? >>
>
> Not in DBISAM, no.  The table name is passed into the trigger.
>

Let me try the question a different way. I have to specifically ask for the
trigger to be applied to this table, true? IOW I can't have an update
trigger at the database level, that gets fired on ANY table being updated.

Related question: do you have in Elevate DB security at the user level such
that I can specify a user never gets to do updates?

Robert

Robert

>Let me try the question a different way. I have to specifically ask for the
>trigger to be applied to this table, true? IOW I can't have an update
>trigger at the database level, that gets fired on ANY table being updated.

Its more that you have to work out in the trigger which tables to ignore. From the pdf manual

procedure TMyForm.EngineBeforeDeleteTrigger(Sender: TObject;
TriggerSession: TDBISAMSession; TriggerDatabase: TDBISAMDatabase;
const TableName: String; CurrentRecord: TDBISAMRecord);

So my understanding (from a while ago now) is any time there's a delete this gets fired and you have to work out in the trigger if you want to do anything.

>Related question: do you have in Elevate DB security at the user level such
>that I can specify a user never gets to do updates?

YUP Look at a recent thread "How i can to create a readonly user ?" in elevatedb.general

Roy Lambert [Team Elevate]

Robert,

<< Let me try the question a different way. I have to specifically ask for
the trigger to be applied to this table, true? >>

No, the triggers are global in DBISAM by their very nature.

<< IOW I can't have an update trigger at the database level, that gets fired
on ANY table being updated. >>

Yes, you can.  In fact that's all you get in DBISAM.

<< Related question: do you have in Elevate DB security at the user level
such that I can specify a user never gets to do updates? >>

Yes, see here:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=0&topic=8

--
Tim Young
Elevate Software
www.elevatesoft.com