0

We've recently added a few restrictions as to what data an analyst can retrieve - specifically the password column in a users table.

The problem is she has thousands of queries which feature SELECT * from users, or joins, etc etc, in his scripts.

Now, when she attempts to run these, MySQL returns:

SQL Error (1142): SELECT command denied to user 'foo'@'bar' for table 'users'

The RDBMS is actually AWS Aurora MySQL 5.6.10a, if this helps. The analyst is reading from a read-replica, so has no write access, but can use and create temporary tables.

Is there a MySQL setting or something we can do, rather than getting the analyst to specify every single column?

Kevin Sedgley
  • 1,049
  • 1
  • 11
  • 22
  • 2
    You could use VIEWS, where you define what columns the user can see. https://dev.mysql.com/doc/refman/8.0/en/create-view.html – nacho May 17 '18 at 17:13
  • 2
    well for start if password isnt encrypted you have bigger problems there. – Juan Carlos Oropeza May 17 '18 at 17:21
  • The password column is an example. We have several columns and tables we want to control access to. I'm aware of the best practice for password hashing, etc, but I'd still rather not expose that data. – Kevin Sedgley May 18 '18 at 08:23
  • Thanks, @nacho - that answer was helpful. Hopefully switching relevant tables to views won't require them to change too much of their initial queries. – Kevin Sedgley May 18 '18 at 08:25

0 Answers0