Create MySQL users with access to own databases only

Posted On July 17, 2018

Kodewulf

Web developer and code junkie. Dabbling in just about anything that interests me.

Create MySQL users that will only have access to databases that they create. There are instructions for using phpMyAdmin and using the terminal.

To create a new MySQL user using SQL commands (phpMyAdmin)

CREATE USER [email protected]'%' IDENTIFIED BY 'secret';

To limit access for existing users using SQL commands (phpMyAdmin)

REVOKE ALL PRIVILEGES, GRANT OPTION from USERNAME;
GRANT ALL PRIVILEGES ON `USERNAME\_%` .  * TO 'USERNAME'@'%';
FLUSH PRIVILEGES;

To create a new MySQL user using mysql command line

mysql -u SQLUSERNAME -pSQLPASSWORD

Using the mysql command shell

CREATE USER 'USERNAME'@'%' IDENTIFIED BY 'secret';
SELECT User,Host FROM mysql.user;

To limit access for existing users using the mysql command shell

REVOKE ALL PRIVILEGES, GRANT OPTION from USERNAME;
GRANT ALL PRIVILEGES ON `USERNAME\_%` .  * TO 'USERNAME'@'%';
FLUSH PRIVILEGES;

More adventures

Javascript sticky header magic

Some sticky header magic.  If a sticky header overlaps some content at the top of the page, this will move page content down a bit.

Google Event Tracking

Google gtag.js event code To be included in HEAD, replacing #### with UA identifier <!-- Global site tag (gtag.js) - Google Analytics --> <script async src="https://www.googletagmanager.com/gtag/js?id=UA-#########-1"></script> <script>...

Share This

Share this post with your friends!