read-only mode

[expired user #6792]'s profile image [expired user #6792] posted 11 years ago in General Permalink
Is there really no option to mark some databases or tables as read-only after opening HeidiSQL or even to open HeidiSQL in read-only mode (i.e., everything is read-only)?
jfalch's profile image jfalch posted 11 years ago Permalink
no.
ansgar's profile image ansgar posted 11 years ago Permalink
Well, you can do that very effectively by restricting your user privileges in that database to SELECT. That is not an option in HeidiSQL itself.
[expired user #9691]'s profile image [expired user #9691] posted 8 years ago Permalink

In my case I don't administrate the db users myself. I guess I could ask for the write privileges to be revoked from my user, but there's a good chance I will need those ocasionally. Typically though I don't need the rights, and it would be really great to have an application-level safety mechanism to prevent me from changing something unintentionally.

ansgar's profile image ansgar posted 8 years ago Permalink

Well, I could add a checkbox to the session manager's advanced tab, namely "Read only mode" or so. If activated, what happens then? I'd say

  • grid editing should be blocked with an error message
  • the "save" buttons on the table editor should always be displaying the same error instead of saving changes.
  • same goes for the views, procedures, events and triggers.
  • the "ok" button on the edit/create database dialog should display that error message
  • same for the user manager's ok button
  • same goes for the "ok" button on the CSV import dialog
  • same goes for the "ok" button on the "import files into blobs" dialog
  • same goes for the "execute" button in tools > Maintenance
  • same goes for the "execute" button in tools > SQL export, if output is database or server
  • query tabs will be a problem. Simple queries starting with UPDATE, INSERT or DELETE could be blocked, but complex queries with subqueries and so on cannot be really parsed by HeidiSQL for detecting write operations. So that would be still left up to the user.

This is a whole bunch of changes for a non-reliable feature, huh?

[expired user #9821]'s profile image [expired user #9821] posted 8 years ago Permalink

Hi,

I use HeidiSQL a lot. And in the past 6 months, I've made 2 accidental modification to DB. For the 1st time I accidently double clicked on a row, edited its data to other value, and when I move mouse cursor away, it updated the DB. For the 2nd time, I accidently hit Ctrl+C, Ctrl+V while my mouse focus was in select result, ending up inserting a new row to database.

I understand a readonly mode may be too complex. So maybe a confirmation promp mode would help. In that mode, all modifications to DB will prompt a Message box, saying "Do you want to make this chagge to database?"

This will greatly reduce the chance of accidental user mistake.

An alternative solution, maybe just allow user to disable edit via select result panel will do.

Regards, Kai

[expired user #10058]'s profile image [expired user #10058] posted 8 years ago Permalink

I'll add two my cents to this issue. I just started using HeidiSQL (With MS SQL) and first thing I'd like to have is ReadOnly mode, in addition to that fact I'm using read-only user on SQL.

The main problem - I'm used-to CTRL-INS, SHIFT-INS as copy-paste (not CTRL-C, CTRL-V), but CTRL-INS works as duplicate row.

My suggestion for this feature implementation, if You decide to:

  1. In read-only mode it both CTRL-C and CTRL-INS do just copy-paste, not "duplicate row". Also in read-only mode there will be no GUI commands to delete, insert, etc. For SQL commands, I think it's enough, to find words in whole SQL sentence and ban them like "SQL command UPDATE do not work in read-only mode".

Regards, CaptAm

[expired user #10278]'s profile image [expired user #10278] posted 7 years ago Permalink

@ansgar I think it should work exactly as you described in your post.

OR just disable all those controls in the interface which would not work for all of them.

I personally need this feature because I have projects with MySQL configured in a master/slave config. I'd like to be able to set it up so that I cannot by mistake write something to any slave. I actually went into the Advanced tab in the Session Manager to search for this first.

Thanks.

[expired user #10278]'s profile image [expired user #10278] posted 7 years ago Permalink

@ansgar I just thought that you can do this in a funny way.

  1. Add the read-only checkbox in the Advanced menu.
  2. If clicked advice the user to set up their user with the RDBMS' priviledge system as read only
  3. Uncheck the checkbox :)
benf-tge's profile image benf-tge posted 1 year ago Permalink

The need to open a database connection read-only, is very different from restricting a user's permissions in the database.

So:

Well, you can do that very effectively by restricting your user privileges in that database to SELECT. That is not an option in HeidiSQL itself.

This misses the issue, I believe.

What I need (and I think the original poster asked for) is a way to open a read-only connection to the database. That is, while the database connection is in this read-only mode, HeidiSQL will not do any operations via that connection that modify the database.

This is analogous to having permission to modify a large document on the filesystem, but choosing to switch one's text editor to read-only mode. So that, while in that editor, I can be confident that none of the mutating operations will do anything, even if I accidentally select one.

What's being requested is, yes, a way to have a "turn off all the mutating operations, for this one database connection" mode.

ansgar's profile image ansgar posted 1 year ago Permalink

That's exactly what a SELECT-only permission does, or?

What's the difference between such a mutating operation and the creation of a new row in a table grid?

benf-tge's profile image benf-tge posted 1 year ago Permalink

Howdy Ansgar,

That's exactly what a SELECT-only permission does, or?

No, that's not the same.

Changing permission of the user in the database is very different: that would change what the user could do no matter what application and what database connection was used. It would not be in control of any user who is not already privileged to change user permissions.

What's being asked for (by me, and I believe by the original poster) is: Leave the database user permissions unchanged. Instead, allow any HeidiSQL user to select a mode for the client so that regardless of the database user's permissions, the client will not perform mutating operations on this connection specifically.

This puts such a read-only mode in the hands of the user, ad hoc while the session is active, without any changes to the database and without special privileges to change user permissions.

So, a user who has some "write" permissions in the database can temporarily and ad-hoc switch HeidiSQL to a "read only" mode, making it safe to browse and construct queries etc. knowing that the powerful permission to mutate the data will not be used in this connection.

The user might even want multiple connections to the same database, with e.g. one of them being read-only and another being unrestricted. The read-only connection becomes a "browse the data and experiment with queries" session, and the unrestricted connection is used only when they know exactly what changes need to be made.

This can be a great help in avoiding tragic mistakes while rapidly exploring to troubleshoot an urgent problem, for example.

ansgar's profile image ansgar posted 1 year ago Permalink

So the idea is to downgrade privileges without asking your server administrator. Or how would you phrase that in one short sentence?

benf-tge's profile image benf-tge posted 1 year ago Permalink

So the idea is to downgrade privileges without asking your server administrator.

The idea is to ignore the database user privileges (which are the responsibility of the database server). If the database knows the user doesn't have permission to do something, that remains the same.

The change is to how HeidiSQL behaves: what operations it will permit in the current connection.

Or how would you phrase that in one short sentence?

Implement a read-only mode (a toggle) that can be set or unset separately for each active connection, that tells HeidiSQL to allow only operations that do not mutate the database and data.

Once implemented, that toggle would be useful both as an ad-hoc toggle for an active connection; and as a configuration for each stored connection to specify whether it will open by default in read-only mode.

Please login to leave a reply, or register at first.