|
Home > Archive > ASE Database forum > October 2005 > making sure an arbitraty sql string does not update data
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
making sure an arbitraty sql string does not update data
|
|
| Ashish 2005-10-27, 8:21 am |
| Hi,
I want to allow users to give queries against multiple
servers at one time. So, for this I am making an intranet
page.
I want the users to only be able to read data but not update
data. So, for example, a "select @@version" string submitted
for 100 servers should be acceptable; but a "delete ..."
string submitted for so many servers should not be
acceptable.
I want to provide flexibility in that they can give any read
query or execute any procedure which does not update tables.
At the same time, I want to restrict any updates to the
database.
Do you think the following is a foolproof method :
If a user gives a query which I have in a variable called
$query (perl syntax), can I give something like this :
begin tran
$query
rollback tran
Thanks for your ideas and suggestions.
Regards,
Ashish
| |
| mpeppler@peppler.org 2005-10-27, 8:21 am |
| > Hi,
>
> I want to allow users to give queries against multiple
> servers at one time. So, for this I am making an intranet
> page.
>
> I want the users to only be able to read data but not
> update data. So, for example, a "select @@version" string
> submitted for 100 servers should be acceptable; but a
> "delete ..." string submitted for so many servers should
> not be acceptable.
> Do you think the following is a foolproof method :
>
> If a user gives a query which I have in a variable called
> $query (perl syntax), can I give something like this :
>
> begin tran
> $query
> rollback tran
This will roll back any changes, but will also most likely
bring your server to a stand-still because of the locks that
you are likely to take out for any long running operation.
You might be able to mitigate this by limiting the number of
rows that can be affected by any query (set rowcount XXX),
which is something you should do in any case: you don't want
a "select * from foo" to kill your web server because the
select retrieves a couple of million rows!
Actually the best way to allow read-only access is to create
a read-only user/login. This user should only have SELECT
permission on any table/view, and should NOT have EXECUTE
permission on any proc that updates the system.
Michael
|
|
|
|
|