It would be nice, for end user report generators, to have an ExecSafe that could be used rather than ExecSQL.

The difference would be that ExecSafe would have a list of "safe" sql commands so if some nice user types "DROP * FROM ..." and DROP isn't in the list they are told no way and nothing happens.

Roy Lambert

Roy,

<< ExecSafe would have a list of "safe" sql commands so if some nice user types "DROP * FROM ..." and DROP isn't in the list they are
told no way and nothing happens. >>

A great idea! We faced exactly this problem with a software partner who was creating dictionary input for us in a DBISAM database which
we provided. We did include an interactive SQL window so they could query using SELECT how many translations were still blank, how
many records were updated in the last week, and so on. But if they tried an INSERT, UPDATE, DELETE, DROP, ALTER the software was
written so that it politely refused to execute the query.

Cheers,
Dan

Lexicon Software Ltd

Roy,

<< It would be nice, for end user report generators, to have an ExecSafe
that could be used rather than ExecSQL.

The difference would be that ExecSafe would have a list of "safe" sql
commands so if some nice user types "DROP * FROM ..." and DROP isn't in the
list they are told no way and nothing happens. >>

If you're referring to EDB, then just create a role for report users that
doesn't allow any modification to any of the tables in the database (only
grant the role SELECT privileges on the necessary tables or views).

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>If you're referring to EDB, then just create a role for report users that
>doesn't allow any modification to any of the tables in the database (only
>grant the role SELECT privileges on the necessary tables or views).

I was, but I'm not sure what you're proposing would have the same result/capability.

I haven't even looked at the built in security so I'm only going off guesses and what you've posted.

I have cases where a user is allowed to delete records via the application interface and under control, but I wouldn't want them typing sql in to drop rows. So I suppose I'd need to switch the use role before running the query, run it and then switch back again, without loosing context (ie tables open and position in them). I have no idea how easy or difficult that is.

If nothing else another interesting concept.

Roy Lambert

Roy,

<< I have cases where a user is allowed to delete records via the
application interface and under control, but I wouldn't want them typing sql
in to drop rows. >>

Why not ?  If they can delete a row in the application, then there's no harm
in allowing the same via SQL.  The two are exactly the same.

<< So I suppose I'd need to switch the use role before running the query,
run it and then switch back again, without loosing context (ie tables open
and position in them). I have no idea how easy or difficult that is. >>

Well, you could simply just use another session that is running as the
"Report User" user and hook the query up to it.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< I have cases where a user is allowed to delete records via the
>application interface and under control, but I wouldn't want them typing sql
>in to drop rows. >>
>
>Why not ? If they can delete a row in the application, then there's no harm
>in allowing the same via SQL. The two are exactly the same.

Malice! Believe it or not there are people out there who, if for instance, they were fired, would happily type DROP * FROM ... But having to expressly click on 14,000 company records and delete one at a time with the knowledge that there's an audit trail would quietly leave.

Then there's also the know-it-all (looks in mirror) who thinks they know what they are doing and either gets the WHERE clause of the DROP statement messed up or forgets it entirely.

Roy Lambert

Roy,

<< Malice! Believe it or not there are people out there who, if for
instance, they were fired, would happily type DROP * FROM ... But having to
expressly click on 14,000 company records and delete one at a time with the
knowledge that there's an audit trail would quietly leave. >>

Sure, but slap a trigger on the table and you *always* have an audit trail,
regardless of whether they are deleting from within your application or not.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


You seem to be thinking from a technologist's viewpoint rather than as a paranoid IT Manager (which I used to be). On this trust me it is not the same.

A little real life example (not to do with deleting records but similar principles). A few years ago when I had staff I had a consultant working for me. His IT knowledge was limited, when he was going to be moving on to another firm he started printing out the details from the database (in recruitment contacts are valuable). At that time for convenience I had a printer for each consultant and they were 2 or 3 to a room. The solution was to remove the printers from all consultants and only leave one with the secretary.

It didn't prevent anyone doing the same thing but it slowed them down, made it inconvenient and obvious and essentially stopped the guy before he printed out the entire database.

Roy Lambert