#1  
Old 15th March 2019, 01:41 AM
obeltrami obeltrami is offline
Member
 
Join Date: Feb 2011
Posts: 83
Default Readonly Query

Hi,

In my app I am adding a form where users can add free-style SQL to query aspects of the data that I might not have thought of (actually, I might be the one using this feature the most).

But I don't want them to be able to change the data.

I tried setting ReadOnly to true and RequestLive to false in the TnxQuery, but it still happily accepts and processes DELETE and UPDATE statements.

Is my only solution to parse the SQL string and abort if I find DELETE or UPDATE or ALTER or DROP ? If so, do you have a complete list of the words I should look for ?

Very best regards,

Olivier
  #2  
Old 15th March 2019, 01:54 AM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: Readonly Query

Run the queries on an account that only has READ privilige.

obeltrami wrote:

>
> Hi,
>
> In my app I am adding a form where users can add free-style SQL to query
> aspects of the data that I might not have thought of (actually, I might
> be the one using this feature the most).
>
> But I don't want them to be able to change the data.
>
> I tried setting ReadOnly to true and RequestLive to false in the
> TnxQuery, but it still happily accepts and processes DELETE and UPDATE
> statements.
>
> Is my only solution to parse the SQL string and abort if I find DELETE
> or UPDATE or ALTER or DROP ? If so, do you have a complete list of the
> words I should look for ?
>
> Very best regards,
>
> Olivier


  #3  
Old 15th March 2019, 04:29 PM
obeltrami obeltrami is offline
Member
 
Join Date: Feb 2011
Posts: 83
Default

Hi Thorsten,

Thank you very much for your reply.

I currently do not use accounts. Is there a basic built-in read-only account (similar to the Windows guest account) ?

If not, how can I create it it code at sites where accounts are not implemented ?

Very best regards,

Olivier
  #4  
Old 15th March 2019, 11:15 PM
Eivind Bakkestuen [NDD]
 
Posts: n/a
Default Re: Readonly Query

> I currently do not use accounts.

Another option would be to use a snapshot transaction (eg
StartTransaction(True).

--
Eivind Bakkestuen [NDD]
  #5  
Old 16th March 2019, 12:44 AM
Roberto Nicchi
 
Posts: n/a
Default Re: Readonly Query

Il 15/03/2019 06:29, obeltrami ha scritto:
> Hi Thorsten,
>
> Thank you very much for your reply.
>
> I currently do not use accounts. Is there a basic built-in read-only
> account (similar to the Windows guest account) ?
>
> If not, how can I create it it code at sites where accounts are not
> implemented ?
>
> Very best regards,
>
> Olivier
>
>


Try this code to create a readonly user:

// login as administrator
nxsession1.UserName:='administrator';
nxsession1.Password:='NexusDB';
nxsession1.open;

with nxSession1.LoggedInAdministrator do
begin
if not(userexists('newuser')) then
begin
adduser('newuser','apassword',false,true,false); // no admin,
read access, no write access

showmessage('The user has been created');
end
else showmessage('The user already exists');
end;

  #6  
Old 16th March 2019, 02:45 AM
Rodrigo G?mez
 
Posts: n/a
Default Re: Readonly Query

Eivind Bakkestuen [NDD] wrote:
>> I currently do not use accounts.

>
> Another option would be to use a snapshot transaction (eg
> StartTransaction(True).
>


Interesting, haven't thought of that either.

I ended up allowing my users (their IT staff, mostly) to run queries but
they need to send them before for us to sign, so we can be sure they are
not going to break something.

But if that's an easy way to provide a "readonly" execution then it is
very very good news.

Thanks for the tip.

--
Rodrigo G?mez [NDX]
M?xico, GMT-6
  #7  
Old 16th March 2019, 02:34 PM
obeltrami obeltrami is offline
Member
 
Join Date: Feb 2011
Posts: 83
Default

Thank you Roberto and Eivind.
I'll try the user creation code.
  #8  
Old 16th March 2019, 05:00 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: Readonly Query

obeltrami wrote:

>
> Thank you Roberto and Eivind.
> I'll try the user creation code.


If it works, simply doing a StartTransaction to start a snapshot transaction
(which by it's very nature is always readonly) before executing the TnxQuery
should be the easier solution.
  #9  
Old 17th March 2019, 03:27 AM
Rodrigo Gómez
 
Posts: n/a
Default Re: Readonly Query

Thorsten Engler [NDA] wrote:
> obeltrami wrote:
>
>> Thank you Roberto and Eivind.
>> I'll try the user creation code.

>
> If it works, simply doing a StartTransaction to start a snapshot
> transaction
> (which by it's very nature is always readonly) before executing the
> TnxQuery
> should be the easier solution.


I've already implemented the snapshot transaction solution following
Eivind's advise and it works perfectly for what I need.

I can do SELECT INTO #temp (which I do extensively to optimize queries),
and so on, but not touch the database tables.

I haven't tried modifying the #temp tables to see if it's allowed, but
that's something I don't usually do; I don't expect the end users to
have to do that either.

Thanks!

-- Rodrigo Gómez [NDX]
México, GMT-6
  #10  
Old 17th March 2019, 04:10 AM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: Readonly Query

Rodrigo Gómez wrote:

> I've already implemented the snapshot transaction solution following Eivind's
> advise and it works perfectly for what I need.
>
> I can do SELECT INTO #temp (which I do extensively to optimize queries), and
> so on, but not touch the database tables.
>
> I haven't tried modifying the #temp tables to see if it's allowed, but that's
> something I don't usually do; I don't expect the end users to have to do that
> either.
>
> Thanks!


I'm not sure without testing it, but you may have to make sure that you call
Commit on the snapshot transaction and not Rollback after you are done,
otherwise it might rollback the resultset of the still open query...


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Readonly table barry nexusdb.public.support 4 3rd February 2013 08:25 PM
Query result is readonly Michael Bickel nexusdb.public.support 23 16th July 2009 11:02 PM
TnxDataDictionary is readonly Lee Mc Cauley nexusdb.public.support 2 23rd January 2007 10:52 PM
OT: How to make a USB readonly S. Ben nexusdb.public.support 4 20th December 2006 09:58 PM
dataset readonly Pierre Demers nexusdb.public.support 8 4th April 2005 12:49 AM


All times are GMT +11. The time now is 11:56 PM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.