To illustrate how easy it’s to see who’s trying to access data they have not been granted for, we will first create a schema with two tables:
mysql> create database mydata;
mysql> use mydata
mysql> create table table1 (id int auto_increment primary key,
name varchar(20), something varchar(20));
mysql> create table table2 (id int auto_increment primary key,
name varchar(20), something varchar(20));
Now, let’s create a user :
mysql> create user myuser identified by 'mypassword';
And as it’s always good to talk about SQL ROLES, let’s define 3 roles for our user:
- myrole1: user has access to both tables in their entirety, reads and writes
- myrole2: user has access only to `table2`, reads and writes
- myrole3: user has only access to the column `name`of `table1` and …