Grant permission to individual fields ( Columns) in MySQL - MySQL DBA Tutorial

How to provide permission on limited columns in a table in MySQL Database 


In MySQL Server you can provide permission on column level. 

SELECT: 
You can provide select permission on specific columns of a table. The user will be only able to select the data on which he/she has permission. User will not be able to use the column in WHERE clause, On, Group by and in Oder by clause if does not have permission.
Syntax to provide Select Permission on Columns:

MySQL > GRANT SELECT (Column1,Column2,....)  ON TABLENAME To 'UserName'@'Host'

UPDATE:
The user will be only update values in columns on which he/she has the permission.

MySQL > GRANT UPDATE (Column1,Column2,....)  ON TABLENAME To 'UserName'@'Host'

INSERT : 
Insert permission can be provided on column level. The Null/default value will be inserted in columns on which user don't have permission.

MySQL > GRANT INSERT (Column1,Column2,....)  ON TABLENAME To 'UserName'@'Host'


Example:

Let's create employee table with below definition and provide Select permission on FName and LName to user TB.
CREATE TABLE employee 
  ( 
     id    INT, 
     fname VARCHAR(100), 
     lname VARCHAR(100), 
     age   SMALLINT, 
     ssn   CHAR(10) 
  );
 


insert some sample data
INSERT INTO employee 
VALUES     (1, 
            'Aamir', 
            'Shahzad', 
            35, 
            '000-000-00'); 

INSERT INTO employee 
VALUES     (2, 
            'Raza', 
            'M', 
            30, 
            '000-000-00');
Provide SELECT PERMISSION to user TB on FName and LName columns.
MySQL > GRANT Select (FName,LName) on employee to 'TB'@'localhost';

To provide UPDATE PERMISSION ON Age column to TB. we can use below statement.

MySQL > GRANT UPDATE (Age) on employee to 'TB'@'localhost';

To provide INSERT PERMISSION on id, FName,LName only, we can use below statement.

MySQL > GRANT INSERT (Id,FName,LName) on employee to 'TB'@'localhost';

MySQL Tutorial for beginners  - How to provide Column level permission in MySQL Server to user

1 comment: