MySQL : Grant privileges to the user

GRANT : GRANT statement grants privileges to a user account 



By default, MySQL creates the following grant tables:

SELECT * FROM columns_priv;

SELECT * FROM db;

SELECT * FROM tables_priv;

SELECT * FROM user;

The following grant command will provide Select,Insert access to the user SaiReddy on the desired database.


GRANT SELECT ,INSERT ON databaseName.tableName

 TO SaiReddy@localhost IDENTIFIED BY 'pw1';

Column-Level Privileges :

GRANT SELECT ,UPDATE (BookTitle ,Copyright ) ON test.Books
 TO 'user1' @ 'domain1.com' IDENTIFIED BY 'pw1';
Grant Create , alter,drop and create view permission for a database


GRANT CREATE ,ALTER ,DROP ,CREATE VIEW ON TENNIS.* TO USER1

Grant permission for a procedure
GRANT EXECUTE ON PROCEDURE PROCEDURE_NAME TO User1

GRANT ALL PRIVILEGES to a user from localhost
GRANT ALL PRIVILEGES ON *.* TO 'User1' @ 'localhost' WITH GRANT OPTION



GRANT Create,Alter,Drop privileges to ALL
GRANT CREATE ,ALTER ,DROP ON *.* TO MAX
GRANT Create,Alter,Drop view for a database
GRANT CREATE ,ALTER ,DROP ,CREATE VIEW  ON TENNIS.* TO KRISH



Give user permission for querying a view
CREATE USER 'TEST' @ 'localhost' IDENTIFIED BY 'TEST@123';

CREATE VIEW VW_EMP (DEPT ,NUMBER_OF) AS
SELECT DEPT ,COUNT(*) FROM EMP GROUP BY DEPT;

GRANT SELECT ON VW_EMP TO DEPT