Restrict or limit user access to certain tables

[expired user #9567]'s profile image [expired user #9567] posted 8 years ago in General Permalink

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.

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

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?

ansgar's profile image ansgar posted 8 years ago Permalink

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.

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

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.

ansgar's profile image ansgar posted 8 years ago Permalink

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:

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

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.

ansgar's profile image ansgar posted 8 years ago Permalink

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

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

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

2 attachment(s):
  • User_1_access
  • User_1
ansgar's profile image ansgar posted 8 years ago Permalink

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.

ansgar's profile image ansgar posted 8 years ago Permalink

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

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

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.

ansgar's profile image ansgar posted 8 years ago Permalink

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.

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

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

ansgar's profile image ansgar posted 8 years ago Permalink

What about updating your MySQL server to fix that?

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

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.

ansgar's profile image ansgar posted 8 years ago Permalink

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).

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

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.

BubikolRamios's profile image BubikolRamios posted 8 years ago Permalink

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 (-:

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

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.

BubikolRamios's profile image BubikolRamios posted 8 years ago Permalink

BubikolRamios's profile image BubikolRamios posted 8 years ago Permalink

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.

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

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?

BubikolRamios's profile image BubikolRamios posted 8 years ago Permalink

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 (-:

BubikolRamios's profile image BubikolRamios posted 8 years ago Permalink

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

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