Last modified: 2014-01-03 15:59:51 UTC
I'm not sure why the OTRS admins currently do not have access to this feature. By default, it is available (see attached URL). This option has been unavailable on our installation since 2007 and prior (perhaps it was never enabled). If there is no reason not to, I'd like it enabled so that we may run our own queries, without having to poke ops to do it.
Seems like a reasonable request to me, but CCing Chris to make sure there are no security problems with this.
The admin would obviously be able to impersonate other users, and have full access to everything in the db. I assume we trust our admins at that level. The other issue is what rights the db user that OTRS runs with on the db server. If the user had file permission, it could allow the admin (or anyone who gets the admin to run an xss/csrf, or brute forces their password) to start attacking the database server also. It looks like there are other critical services on that server/db, so the impact would be significant if an attack was successful. If there is a significant need for this to be enabled for a short period of time, and ops has the database user well restricted, and everyone is comfortable with the admins having that level of access to the data, then I think the benefit would probably outweigh the risk.
As for the impersonation issue, the admins already have the ability to change the passwords of users. As for your other concerns, the URL I linked to states that "The "SQL Box" link opens a screen that lets you query the content of the tables in the OTRS database. It is not possible to change the content of the tables, only queries are allowed". I assumed this meant that it would only allow for read-only access - I'm not sure if that alleviates those concerns at all, or not.
Any reply to my above comment? I hope that would resolve any concerns. If not, I would be fine with temporary access (what kind of window are we talking about? There are a number of things we need done at the moment, but there are also regular routine maintenance queries). However, I would rather get everything ready ahead of time if we only have a small window to do everything in. Like I said, there are a number of queries that we'd like to get done, as part of our regular and routine maintenance. For instance, RT ticket #4430 (or possibly 3513 - I don't have RT access so I'm not sure) (a request for one query that we run regularly) has still not been done. This is why it would be so much simpler if OTRS admins could use the SQL box, as the bug requests. I understand security concerns but please take into consideration comment #3 above and lets see where we can go from there.
Not commenting further on the security issues atm, maybe will tomorrow; just clarifying that there's no open OTRS query tickets waiting to be run AFAIK. (RT #4430 is done; sending RD more about that elsewhere) There may be some flaw in the way the RT #4430 query was constructed but that is fixed by fixing the query not by getting access to this new query interface. (I have personally verified that the results of the query do not match what was intended. Apparently uses the last person to modify a ticket and doesn't check for who currently owns it.)
I can't speak for the specific queries, but generally-speaking I suspect that db access is not going to prove as useful as Rjd and others might hope. Speaking, in this case, as someone who has wandered around the OTRS db. Mostly while looking at things in horror. Objectively, the OTRS db structure allows for many different metrics to be gathered and checked - although it's terribly structured, which introduces a fairly high learning curve. However, our implementation *of* OTRS, and the way that agents use it in practise, means a lot of this data simply isn't available; ownership automatically reverting to the admin account, for example, makes it difficult to gather reliable data on ticket handling numbers, historical usage, etc, etc. I don't have any particular perspective on whether SQL box access is justified, but I think admins are going to be disappointed by what data they can get and rely on.
(In reply to comment #3) > As for the impersonation issue, the admins already have the ability to change > the passwords of users. > > As for your other concerns, the URL I linked to states that "The "SQL Box" > link > opens a screen that lets you query the content of the tables in the OTRS > database. It is not possible to change the content of the tables, only > queries > are allowed". I assumed this meant that it would only allow for read-only > access - I'm not sure if that alleviates those concerns at all, or not. From the docs that I found on it, it looks like they let you input a full query, but they probably have some filtering to try and keep you from updating the database. I doubt the filters are perfect, but will hopefully keep you from accidentally modifying something. So yes, I think it's fine to give the feature to a limited number of people for a limited amount of time. And if the data is as bad as Oliver anticipates, then we can turn it off even sooner. If the data is useful, and admins want to build running raw sql commands into their workflow, then we'll need to do a proper assessment of the module, and make sure that only a small whitelist of query formats are allowed.
(In reply to comment #7 by csteipp) > So yes, I think it's fine to give the feature to a limited number of people > for a limited amount of time. So could somebody go ahead, or is this waiting for bug 22622 to get fixed?
(In reply to comment #8) > (In reply to comment #7 by csteipp) > > So yes, I think it's fine to give the feature to a limited number of people > > for a limited amount of time. > > So could somebody go ahead, or is this waiting for bug 22622 to get fixed? Well, I have had no update on progress on bug 22622 in a while (since just just before WMCON). Ideally, they'll have it enabled in 3.2. But, as I've not received update, I don't know what the time frame there is, so depending on that... It would be helpful if it could be enabled on our current installations for the admins, on a temporary basis.
A new version of OTRS was recently made available (see bug 22622). Once dust has settled a bit this is probably something to try now, if it's still wanted?
Access to this module was given to the OTRS admins at the time of upgrade - we will relinquish when no longer needed, if necessary.
