Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – What is the best way to grant user on sql

I am trying to learn the best methods to grant user privileges on database

CREATE USER IF NOT EXIST `'silas'@localhost'` IDENTIFIED BY '1234' 
GRANT ALL PRIVILEGES ON *.* TO 'silas'@'localhost';

or

GRANT ALL PRIVILEGES ON `my_db.*` TO `'silas'@'localhost'`;

I am just trying to find the best way to do this.

Tags:

2

Answers


  1. 0

    I would strongly recommend to grant specific privileges instead of ALL unless it’s absolutely necessary like below statement which only granting select, insert and update privilege on my_db database.

    GRANT SELECT, INSERT, UPDATE ON my_db.* TO 'silas'@'localhost';

    I would also recommend to use specific database name if not it’s necessary.
    instead of .* my_db.* is safer option when you just want to give privilege on my_db database

    And please use FLUSH PRIVILEGES; after granting the privilege to ensure immediate effect.

    Login or Signup to reply.
  2. 0

    so lets break it down and this might help you understand when to use which, there is no There is no "the best way".

    
GRANT ALL PRIVILEGES ON *.* TO 'silas'@'localhost';

    This above gives silas complete control over every database on the server.

    
GRANT ALL PRIVILEGES ON `my_db`.* TO 'silas'@'localhost';

    This above limits silas‘ full control to just the my_db database, while other databases remain protected.

    
GRANT SELECT, INSERT ON `my_db`.* TO 'silas'@'localhost';

    This above grants silas the ability to only read (SELECT) and insert (INSERT) data, without allowing them to delete or modify database structures.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search