[Koha-devel] SQL reports [error]

[Paul]

At 05:44 PM 4/30/2012 +0200, Fischer, Katrin wrote:
>I really don’t like the idea. I think if you want someone to make 
>changes to the database, you should give them a proper tool and training 
>to do that (outside of Koha).

Respectfully, we might be talking apples and oranges.  *All* staff and many 
users "make changes to the database" - not fundamental structural changes, 
but add, modify and delete data records.  And it was the latter point that 
I raised earlier today.

You are of course correct that the various options allowed by UPDATE, 
DELETE, DROP, INSERT, and CREATE *can* modify/damage the structure of your 
database; but they can also be incredibly useful (see my earlier email that 
uses UPDATE.)

I don't know how you are setup, but in my organization for any intervention 
that requires CLI, I personally have to go to our server room (I am the 
only keyholder) as I have locked the Koha server down fairly tightly from 
remote access. I decide (after consultation with staff/volunteers) what 
scripts can|cannot be used in the "normal course of business." I have 
tested a certain script required by staff (hundreds of times every year) by 
running it some seventy times to ensure it does what it is supposed to and 
does not do anything else.

It is ready to go into useful production, but "as-is" Koha 3.6 does not 
allow me to make it available to staff -- and every time they need it (ad 
hoc basis) it takes me ten minutes, instead of staff "clicking a 
button."  This is because the design of Koha includes very 
well-intentioned, but arbitrary nonetheless, hard code denying the use of 
UPDATE.

The chances of a major catastrophe are much greater if I try and train our 
cataloguers in the intricacies of MySQL and allow them access to the server 
room, than if I develop a secure script and make it available on the staff 
interface -- and that was the only reason I raised it on this list and will 
implement it on our Koha server. YMMV.

Best - Paul

>  The interface for statistics is very limited and does not give feedback 
> when your SQL statements have errors or produce no result sets. Also it 
> seems like a big security risk to me.
>
>
>
>Katrin
>
>
>
>From: koha-devel-bounces at lists.koha-community.org 
>[mailto:koha-devel-bounces at lists.koha-community.org] On Behalf Of Jared 
>Camins-Esakov
>Sent: Monday, April 30, 2012 5:21 PM
>To: Paul Poulain
>Cc: koha-devel at lists.koha-community.org
>Subject: Re: [Koha-devel] SQL reports [error]
>
>
>
>Paul,
>
>
>
>
>
>On Mon, Apr 30, 2012 at 11:17 AM, Paul Poulain <paul.poulain at biblibre.com> 
>wrote:
>
>Question to all = could it be a good idea to let superlibrarians execute
>dangerous SQLs like the one forbidden by the test ?
>Otherwise asked: could we add a
>unless permission eq 'superlibrarian'
>condition ?
>
>( ie: "with great power comes great responsibility" - at spiderman uncle- )
>
>
>
>We were actually just discussing that on #koha a few days ago. I argued 
>that only the database user (i.e. user 0) should be allowed to do it. If 
>you have the direct login, there's nothing you can't do with the system 
>just by logging into the database.
>
>
>
>Regards,
>
>Jared
>
>
>
>--
>Jared Camins-Esakov
>
>Bibliographer, C & P Bibliography Services, LLC
>
>(phone) +1 (917) 727-3445
>
>(e-mail) jcamins at cpbibliography.com
>
>(web) http://www.cpbibliography.com/