doing 'read-only' querys

doing 'read-only' querys

Post by Mike » Fri, 31 Aug 2001 18:22:08



I have an application where I wish to let people type their own 'read-type'
querys (i.e. restrict querys to selects only) but prevent them from doing
any type of updates or table modification.

If I use the Statement.executeQuery(String)  method this will throw
exceptions when attempting any type of update e.g delete, alter table, drop
etc.

 Catching these exceptions and doing Connection.rollback() prevents deletes
and drops taking effect but alter statements still take effect.

My Statement is of type forward-only, concur-read-only. Does this have any
effect on the 'writability' of the executeQuery method ?

Are there any ways to guarantee that a Statement sent to the
executeQuery(String) method will not modify any data ?

thanks in advance,

MD

 
 
 

doing 'read-only' querys

Post by AV » Fri, 31 Aug 2001 21:28:53


You can try:
-- parse sql before execution and reject query on any
occurrence of UPDATE, INSERT, DELETE, BEGIN, END,
DROP, ALTER, CREATE, SET...
--setAutocimmit(false) before, and do explicit rollback after query...
(this will not work for, ex. MySQL)

Expecting an exception from executeQuery() may fail if driver
is not properly follow standards.

AlexV


> I have an application where I wish to let people type their own
'read-type'
> querys (i.e. restrict querys to selects only) but prevent them from doing
> any type of updates or table modification.

> If I use the Statement.executeQuery(String)  method this will throw
> exceptions when attempting any type of update e.g delete, alter table,
drop
> etc.

>  Catching these exceptions and doing Connection.rollback() prevents
deletes
> and drops taking effect but alter statements still take effect.

> My Statement is of type forward-only, concur-read-only. Does this have any
> effect on the 'writability' of the executeQuery method ?

> Are there any ways to guarantee that a Statement sent to the
> executeQuery(String) method will not modify any data ?

> thanks in advance,

> MD


 
 
 

doing 'read-only' querys

Post by Daniel Dittma » Fri, 31 Aug 2001 23:08:08


Quote:> I have an application where I wish to let people type their own
'read-type'
> querys (i.e. restrict querys to selects only) but prevent them from doing
> any type of updates or table modification.

Create a separate database user and GRANT only SELECT privileges to this
user.

Daniel

 
 
 

1. Querys to 'Navigate' through records

Hi all,

The JDBC drivers I am using don't have scrolling cursors :(

to get the 'next', 'prev'...etc record, I have had to resort to some
creative SQL statements...but they seem to execute slowly..too slowly.
Here is an example method from the orders object

// public method to get the previous record of this type
 public void prev()
 {

    ...//other search methods removed

    else
    {
    nav_id = navQuery("select id from "+tableName+" where id < "+db_id+"
order by id DESC");
    }
     if (nav_id !=0)
       load(nav_id); //
 }

the id column is indexed but I think the 'order by id DESC' part of the
statement takes the most time.

is there a better way to do this type of search?

Thanks,

~Scott

--
________________________________________
Scott Ahten
Code Wrangler
Silent ) Planet

http://www.stage22.com

2. Urgent! - Error on Enterprise Manager

3. ADO thinks DBMS is done, but DBMS ain't done

4. PDOXWIN 5.0 Dynamic Setting of Tab Stop Property?

5. Inordinate growth in DB when doing backup's and restore's

6. Data Warehousing on the WWW

7. **************!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Help me !!!!!!!!!!!!!!!!!!!!!!!!'''''''''''''''''''''''*************

8. Action Querys doesn't reflect emidiately

9. another try: Filemaker 'capture'/read/store imported file name

10. 'Process couldn't read the file .....

11. Making a user 'read only'

12. Reduce Logical Read's or make a INDEX with Word's of one Field