Restrict or limit user access to certain tables

What is sought: The admin user sees all the tables. The other users are limited only to their tables and cannot view the admin table.

The issue: I've tried playing with users and permissions, both server-side and in HeidySQL, but it gave me no luck. Even if I limit the user to a certain table, the admin table is still browseable. I might be erroneous since I'm a newbie to MySQL, but on the server-side I have set up the limited user account to a specific table.

The idea: admin table pulls up the data from user tables via MySQL REPLACE command and, thus gathers all the data from sub-users.

Would appreciate any aid.

Addition: I didn't find any REVOKE options in HeidiSQL or server, which are bespoken on stackoverflow.

Or is it better to place the code in PHP file than to deal with MySQL?

Just use the user manager in HeidiSQL. Uncheck a privilege to internally fire a REVOKE, and give the user read (and write) privilege to the desired table.

Ansgar, can you please delve into more detail? A screenshot would do. I've tried adding obkect in user management assigning it to a specific table, but upon logging the user however can see all the tables.

Then there is probably a second or third object in the user manager, labelled "Global privileges", or whatever. Just uncheck it and save the user.

May look like this:

Ansgar, All global privileges were disabled. The same persists. The user cannot see other databases, but can see all the tables within one database, which I want to avoid.

p.s. no comment post editing opportunity. Uh.

You should post a screen of the user manager, with the privileges.

Here they are: both user privileges for User_1, which should have limited access, and User_1 view upon logging.

I see.

I have exactly the same situation with one colleague here, which is restricted to one table in a database which has 10 tables. The privileges look exactly the same as in your screenshot, only SELECT on that table. The difference is, the colleague just sees that one table, not the 9 others. So I guess that's a MySQL issue, probably version specific or related to some setting.

Forgot to say: I have MySQL v5.6.25 here. Which one is it on your side?

MySQL version 5.5.47. Fearing to update and won't. Since my general rule (and some of the users) is to wait till a stable Ubuntu comes out. Had fun time fixing errors after half successful updates.

Ok. Then my whole project falls out since the option of limited user access was critical. Waiting for the highly trained MySQL monkeys to fix it. Or perhaps, my nose needs to be pointed in some setting I haven't yet discovered.

Is that user able to read and write to the tables on which he/she has no access? I mean, if he just can see those tables, but cannot read from or write into these, there might not be any issue for you.

Getting "UPDATE user denied" message. It stays as issue, however. Employees should not have access to the tables they are not supposed to see.

What about updating your MySQL server to fix that?

An earlier response which sums it up.

MySQL version 5.5.47. Fearing to update and won't. Since my general rule (and some of the users) is to wait till a stable Ubuntu comes out. Had fun time fixing errors after half successful updates.

Yes, I had read that. My question was to express the circularity of this issue, if we keep talking here about HeidiSQL. The issue is then that there is no stable Ubuntu with the fixed MySQL version. Or the fact that you fear issues with a beta release (which is quite reasonable).

Ok. I got it. Maybe I will test it later on another free server, which I won't fear debugging if anything happens. Thanks for helping.

MySQL version 5.5.47. Fearing to update and won't.

I suggest mariadb, it says it can happily coexists with current mysql that you have. Tested that on win machine (maria db on other port than original mysql) and all si fine. Took me like 2 minutes to install.

Did not do that on linux production machine doh (-:

Good point. Indeed, my version seems compatible with MariaDB. Though, did you install it alongside MySQL? I'm more worried about global settings being overridden since I have a number of databases which I'm again fearing to accidentally drop. There is a manual on keeping both alongside, but I would like to hear a second opinion.

In addition, the client has a paid domain accessed via cPanel which only has MySQL installed. There is little chance shaking the admins.

Your link says mariadb-5.5.24-linux-x86_64.tar.gz The latsest is 10. ..... I instaled that mainly coz it has REGEX built in.

I've given the link as exempli gratia, not as a latest version manual. I did find version 10 for Ubuntu.

Your picture says "local-MariaDb". Do I assume you've installed it local-only?

picture says one normal mysql & one maria db , both local. If remote would be win I would do it therere right away, but it is CentOS, so I fear to try (-:

But, it appears it is simle process: https://mariadb.com/kb/en/mariadb/yum/