Which Delphi version is the right to compile v9.5?

Hi Ansgar!

I'm new on this forum but i'm experienced in HeidiSQL and Delphi for many years. And, as a side notice, i'm the "Codehunter" who has worked and noticed in the SynEdit sources. And as a further side notice, i think the ANSI/Unicode compile error on the SynEdit SVN sources some years ago was my fault - Ashes on my head ^^ ( Forum thread id 13773 ).

At the moment, i try to compile v9.5 sources with an XE4. Its my very first flirt with the HeidiSQL sources. There was some showstoppers. First, i'm commented out madExcept (not included in your distribution but its ok) and the include of RES files in heidisql.dpr at line 49 (cant compile the icon.RC because brcc says "Allocate failed"). I have included the mainicon.ico in the project options. Works fine.

After then, i'm changed grideditlinks.pas:637:NewValue from Integer to SmallInt (also in the decl). Then rebuilt the BPLs for the components to get the binaries fit to XE4. Possible an change by Embarcadero in Vcl.ComCtrls.TCustomUpDown.OnChangingEx decl between my XE4 and your Dxx? At this point, the brcc32 and brcc64 works through the project.

After run, i get an error in connection manager because libmysql.dll was not found. I renamed the libmysql32.dll to libmysql.dll. Then my own compiled HeidiSQL runs fine for the first time yaeh :-)

Okay, it was only a little experiment with XE4 because i'm unsure which Delphi version is the best one for HeidiSQL.

After clearifying that, i want to write a complete new replacement for the usermanager.pas. My goal is to have an VirtualTreeview as a hierarchical editor for (inherited) privileges starting from global privileges through scheme and table privileges down to column privileges. This should be very intuitive.

I think this supports a more common use of per-column or per-table privileges to increase the common security of mysql and mariadb databases. My observation is that most users of HeidiSQL are not so familar with security privileges. To get their software working instantly, they spend the DB users more privileges than is actually necessary.

@Ansgar: Do you have interests to such an privilege editor in the main HeidiSQL distribution?

Greets Cody

I saw your comments in the SynEdit sources. I guess CodehunterWorks is it. However, many thanks so far for the work on SynEdit! I believe it's been in Heidi even longer than VirtualTree.

cant compile the icon.RC because brcc says "Allocate failed"

Yes, that's some issue with the resource compiler. And the icon.rc is the only file for which you should (eh - must) use cgrc.exe instead of brcc32.exe.

For the TCustomUpDown.OnChangingEx problems, I recall I had the same problems when migrating from an earlier Delphi version to XE5. Then, I was glad that they changed the declaration of the Position property from Smallint to Integer, as this allowed much bigger values. But if you are on XE4, then this is surely a problem. I have no clue how to work around that. Updating to some newer Delphi is always expensive, but probably we can get it to work with some compiler switch?

According your idea on the user manager replacement: I didn't notice the current dialog is so unintuitive. I recall I rewrote that dialog in 2010, mainly in this and this commit, while in the recent 4 or 5 years it was not changed so much. So, I guess I'm open for your idea. Could you post some rough image on how you intend to make the look of the dialog?

Hi Ansgar,

yes "CodehunterWorks" is generally my Alias when i'm working on community projects. I'm worked also a little bit on the predecessor of VirtualTreeview, called TreeNT, in the very early days - but its not credited on the sources.

For the brcc32 issue i have opened a thread in the german DelphiPraxis. Let me see what will happen there. The issue is caused by the fact thats mainicon.ico contains 32 bit PNG graphics. As another workaround we have two options:

1. Include the mainicon.ico via project options in the .dproj instead via $R .res in the .dpr. Then the icon works fine. At the moment i cant say why, my Delphi uses BRCC32 also as resource compiler for the project but in this constellation it does work fine with PNG icons.

2. Put the .res file directly into the HeidiSQL source distribution instead of .rc. I guess it does not depend on the Delphi version which is used to compile the project.

For the SmallInt/Integer issue i think its the best solution to work around it with a compiler switch. It seems that this event handler is the only place where such an issue occurs. So it would be an easy job to make the HeideSQL sources backward compatible. I cant say why Embarcadero had restricted this value to -32768..32768 in early delphi versions. I think its very stupid. Eventually they had thinked no one clicks 32k times on a spin button?? ;-) But it would no problem in conjunction with a (possible) cooperation if i use XE4 and you use XE5. When the main HeidiSQL distribution is compiled with XE5, all is fine.

For the redesign of the user manager you have misunderstand me a little bit. Its not a critic to the design of your user manager. Its more a consequence of how Mysql/MariaDB handles the user privileges. Many hobbyists, PHP coders and so on do not deal with the pitfalls of security restrictions at DB level. So many PHP projects are designed unsecure because they use the same full-privileged DB user for the common user frontend and the admin backend. This is so stupid.

My intention about this: HeidiSQL is the de-facto standard administration tool for MariaDB and also commonly used by many Mysql users, with the exception of Mysql Workbench. And Mysql WB user manager is also not really intuitive. I want to give the Mysql/MariaDB users a tool to make the deal with database level security as easy as possible - in HeidiSQL, my favorite program. Let me build a mockup demo application to show what my imagination is.

I have a question to you in conjunction with user privileges: Do you have any plans to support administrative roles in HeidiSQL? At the moment, i cant see such options in the user manager. I guess that roles a very elegant way to deal different user privileges "inline" in SQL scripts and in consequence thereof more protection against SQL injections and so on. But unfortunately there are some differences between MariaDB and Mysql with administrative roles. At the moment i'm unsure for details about the differences because i'm use always MariaDB.

And finally, a wish from me. Imagine you have two db server instances. One for development purposes, one for productive use. Both with identical db schemes, but different data. You define very complex privileges on the dev server. It consumes a lot of time to define these rules. Finally you want to deploy your privilege definitions to the productive server. I would love it if HeidiSQL have a feature to transfer privilege definitions from one server to another, similar to the table export from host to host.

Greets Cody

Hello again,

here is a basic demonstration of my idea: h t t p s://www.file-upload.net/download-12940770/HeidiSQL_NewUserManager_Mockup.zip.html (remove the spaces)

Its only a mockup, no real function. It should only demonstrate my idea for a hierarchical privilege matrix based on VirtualTreeview.

The source is written from scratch and does not use any source from HeidiSQL except the main image list. For all readers who not able to compile this project i have included an precompiled exe.

@Ansgar: Hope you will find the basic concept worthy enough to discuss as a future project.

Greets Cody

I just looked at your mockup and I think I got the idea behind it. All databases and tables are displayed in the box, not only those for which the user has privileges on. That's a very nice approach, because it is more... "explicit" than displaying only those where you have privileges. And yes, I think that's very worth a try.

The TUpDown issue worries me a bit - I guess you will get even more compiler errors than this one. Just try with that compiler switch.

I should also add you as a contributor to the GitHub project, right? But you definitely need to tell me your username there, as there are a lot of "Codehunter"s.

Regarding to Github: Hehe, i was never on Github. All of these "Codehunters" are fakes ;) I have registered instantly a new account called "CodehunterWorks". This is now the one and only Codehunter on Github :D

Regarding to Updown issue: This is really the only compiler error i get on XE4, even if i make a full project build. Then came a lot of runtime errors described in the opening post above, but no showstoppers. But i have not tested my own built HeidiSQL intensively. Very possible that there are more runtime issues with XE4. I have currently all Delphi licenses from D7 up to 10.0 Seattle. Installed are only XE4 and 10.0. This is the cause why i asked which Delphi version fits best to HeidiSQL.

Regarding to the mockup: My experience tells me thats always better to have all relevant options in a compact overview. The less clicks, the better. I'll think through the exact way it works. In particular, vertical inheritance of grants and denies. The vertical caption of the privileges fixed header is intended as a synonym for this.

In addition: The mysterious behavior regarding to the brcc32 error and the Delphi IDE is clarified: Delphi uses cgrc internal and tells "brcc32". Very irritating ;-)

Ok, please see at h t t p s://www.file-upload.net/download-12942735/HeidiSQL_NewUserManager_Mockup.zip.html

I have implemented the vertical inheritance logic. Lets play with the checkboxes! And in the meanwhile have a look at the object icon in the privileges tree grid. Objects who changed will be marked with a little yellow lamp. Bequeathed privileges will not affect the change status of an object. Only if inheritance is disabled (the left violet column), privilege changings will affect to objects "Changed" status.

As side notice, i have implemented a horizontal and vertical hot track. This supports the visualization.

The hot tracking in your new mockup looks cool - somehow dynamic.

I'm now going to add you to the GitHub project.

Eventually you can help me about applicable privileges. I had found these days a very useful overview at Mysql or MariaDB docu, which privileges are applicable to global, scheme, table, columns and so on. But unfortunately i've forgotten to bookmark it. Do you know such an docu page there? Thanks....

I guess this is it: https://mariadb.com/kb/en/library/grant/#global-privileges

Some privileges are available only in newer versions, as written in usermanager.pas:

if Version >= 40002 then begin
    PrivsGlobal.Add('REPLICATION CLIENT');
    PrivsGlobal.Add('REPLICATION SLAVE');
    PrivsGlobal.Add('SHOW DATABASES');
    PrivsGlobal.Add('SUPER');
    PrivsDb.Add('CREATE TEMPORARY TABLES');
    PrivsDb.Add('LOCK TABLES');
    PrivsRoutine.Add('EXECUTE');
  end;
  if Version >= 50001 then begin
    PrivsTable.Add('CREATE VIEW');
    PrivsTable.Add('SHOW VIEW');
  end;
  if Version >= 50003 then begin
    PrivsGlobal.Add('CREATE USER');
    PrivsDb.Add('CREATE ROUTINE');
    PrivsRoutine.Add('ALTER ROUTINE');
  end;
  if Version >= 50106 then begin
    PrivsDb.Add('TRIGGER');
    PrivsDb.Add('EVENT');
  end;
  if Version >= 50404 then begin
    PrivsGlobal.Add('CREATE TABLESPACE');
  end;

Your're right and that is whats irritating me. I wonder why HeidiSQL shows CREATE VIEW in usermanager in the table context. At https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html and https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html I read that CREATE VIEW is in context of views and not tables. Is this a difference between Mysql and MariaDB? Projected to my "privilege matrix" is the question, on which object type such privilege checkboxes should be available and on which not.

Currently i'm completing the list of object types. In the yesterday mockup was only tables and triggers included, as examples. I've added now views, procedures, functions and events (similar to "Group objects by type" in HeidiSQL).

Oh i see, your forum script eats my Links placed with the "Link to website" button...

Your're right and that is whats irritating me. I wonder why HeidiSQL shows CREATE VIEW in usermanager in the table context. At h t t p s://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html and h t t p s://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html i read that CREATE VIEW is in context of views and not tables. Is this a difference between Mysql and MariaDB? Projected to my "privilege matrix" is the question, on which object type such privilege checkboxes should be available and on which not.

Currently i'm completing the list of object types. In the yesterday mockup was only tables and triggers included, as examples. I've added now views, procedures, functions and events (similar to "Group objects by type" in HeidiSQL).

Sorry, the forum expects some text in the brackets after putting a link somewhere. If you selected some word before that is automatically taken as the link text.

Ah, i understand :) I'm just used to BBCode and was a little confused here.

I have seen an courios behavior during my tests with vertical inheritation of privileges. Test server is an MariaDB 10.1.19, HeidiSQL is 5.0.5196. Can you reproduce the following:

(A) Log in as root and create an new db scheme "sch_a" and a new table "t_a" in it. Create two columns in "t_a" as "a1":INT(11):Autoinc and "a2":VARCHAR(50). Insert a new row to table "t_a" and fill column "a2" with a random string. Create an new user "test" with no global privileges, SELECT and UPDATE privilege on scheme "sch_a" and only SELECT privilege on table "t_a".

(B) Open a second instance of HeidiSQL and log in as user "test". Perform an UPDATE sch_a.t_a SET a2='any other string' WHERE a1=1 LIMIT 1; The server denies the query. Its correct because the user "test" does have only the SELECT privilege on table "t_a".

(C) In the HeidiSQL instance logged in as root, add the UPDATE privilege on table "t_a" for user "test".

(D) In the HeidiSQl instance logged in as "test", retry the UPDATE query above. It should be done successful because the new UPDATE privilege for user "test" on table "t_a". This is also correct.

(E) As root, revoke all privileges on table "t_a" for user "test". The entire table object "t_a" disappears in the HeidiSQL user manager. The server should now inherit the SELECT and UPDATE privileges from scheme "sch_a" object downto the table "t_a".

(F) As user "test", retry the UPDATE query above. The server denies the query. This is IMHO NOT correct.

(G) At this point, disconnect the session of user "test" and re-login as user "test". Retry the UPDATE query one more time. The server performs it now successful. This is IMHO correct.

Note thats NO changes to user "test" was made by root between the steps F and G. It seems like HeidiSQL does not flush the revokation in step E to the database, but the query log in the main window shows an FLUSH PRIVILEGES.

Changes to privileges for a running session are not effective until the session is closed (and restarted). I think that's what you're encountering between E and F, although I'm a bit unsure - this is quite complex.

Hi Ansgar,

during my work on it, i think i have found a bug in v9.5 - usermananager.pas:297. You wrote:

if Version >= 50706 then begin PasswordCol := 'authentication_string'; end;

But if we use MariaDB, TDBConnection.ServerVersionInt is e.g. "100119" and PasswordCol changes from "password" to "authentication_string. In MariaDB 10.1.19 is the password column in mysql.users named as "Password". In consequence, the current usermanager shows an wrong error message in lblWarning when the user hovers over an entry in the listUsers tree: "This user has an empty password."

Greets Cody

Addendum: I think we have a fundamental problem with version detection. Since release 10.0, the integer version numbers at MariaDB are higher than what MySQL delivers as version. I think we would have to start making a distinction between MySQL and MariaDB in some places. With MySQL 8.0 at the latest, incompatibilities will increase.

One more addition: I seem to have found another bug in connection with MariaDB. If I set the Tree Style Options to Group objects by type at runtime in the main form and then look at the information_schema section, all elements are displayed there as TABLES, even though they are SYSTEM_VIEWS.

The very first executable version of HeidiSQL with the new user manager has just been compiled! :-)

I have created a project page in DelphiPraxis, because I hope to get some technical feedback from the experts there.

The Project Page in DelphiPraxis.net

I agree with you in most things from above. However, I'm a bit lost here in this thread. Could you please create issues at GitHub, for things you think that needs to be fixed?

:-) Most of these issues i had solved in my "personal" branch. To solve it, i have a little bit radical suggestion: Separate MariaDB and MySQL to each own TDBConnection descendant. I've done this partially in my beta, downloadable at DelphiPraxis. You can see the effect in the Connection manager window in the connection type combo. Move and concentrate more logic from the form units to the dbconnection unit and granulate it finer to each DBMS.

And... you said some weeks ago you want to add me to the github project. But, i cant see thats happen ;-)

I invited you in Github as a contributor - you should have received a mail. I try again...

Sadly, no email. Even not in the spam folder. And no messages inside my github account. Are you shure that you had the right "CodehunterWorks" choosen and not one of the "fakes" there?

Github says its waiting for you. And the "sharable" invitaion link is https://github.com/HeidiSQL/HeidiSQL/invitations

Please create issues before changing any code (if there is no issue yet). And add the issue id to the git comment. This way changes are documented and they can be discussed before they get into the code.

Also, for radical changes in units like you mentioned for TDBConnection, you need to push such changes into a separate branch, besides "master".

Hey, this link works! :-) Invitation accepted. And at the other side, no mail was came in my inbox.

Don't worry about radical changes. I've created a local experimental branch. It already works very well with MariaDB 10.1.19 (my local testerver). My plan is to complete this branch for MariaDB and MySQL. If I look at the MySQL 8 documentation, there will be considerable differences. These are already available in some places.

Your code design is actually very good, only in some places you notice that it comes historically from the corner of MySQL. This preference would only have to be resolved slightly and made more modular. Then it should be easier to support other DBMS in the future.

I will not integrate my changes directly into the main branch. HeidiSQL is your project, so keep it that way. But maybe you find my suggestions useful, then we can talk about how to incorporate them into the main branch.

Short question: What do you think about support for very outdated DBMS like MySQL 3.x and 4.x? It is admissible to drop the support for such old DBMS?

I think support should never be dropped, unless it gets too cumbersome to keep it. There are still users of these old versions.

Kein Problem. Aber in diesem Fall hoffe ich auf deine Erfahrung mit diesen alten Systemen. Meine Kenntnis beginnt erst mit Mysql 5.

(The wrong copied ^^) No problem. But in this case, I'm hoping for your experience with these old systems. My knowledge begins with Mysql 5.

Can you create a branch (call it "new_usermanager" or so) in your HeidiSQL repo? I'm not able to do it and do not want to fork the project in my own account.

I just changed your permission level from "read" to "write". You should be able to push now.

Please just create an issue at first, with the things you try to enhance in the upcoming code.

Thank you! I have created a corresponding enhancement issue there. We should discuss everything else on the subject there. And I will be careful with changes, promised: -)

I just went through your commits, and I think you do great stuff here! Especially the approach of making TMariaDBConnection a descendant of TMySQLConnection looks nice (due to very few additions with big advantages).

I wrote some questions and advices into the commit comment sections. Please - no offense meant - I'd like to be constructive here, but there are some minor rules which I want to keep up in the code. Basically these are:

• be careful not to commit accidental dfm changes (often done by the IDE, I know)
• push fixes to the master branch if they are unrelated to the new-usermanager branch
• don't let the dpr file create forms on application start, but on demand, when the user needs it