I want to see the names of all the users for MySQL. When I try to google this question most results are all concerned with users in a database as opposed to MySQL users. I checked out the MySQL documentation but it is very verbose and does not really help much. How do I view the account names of MySQL users, change permissions, and create new users?
Asked
Active
Viewed 2,028 times
0
-
Can't you just query the MySQL Users table? `SELECT user FROM mysql.user` – VerySeriousSoftwareEndeavours Apr 03 '15 at 21:08
-
possible duplicate of [How to get a list of MySQL user accounts](http://stackoverflow.com/questions/1135245/how-to-get-a-list-of-mysql-user-accounts) – nl-x Apr 03 '15 at 21:20
-
@nl-x Does anyone else find it hysterical that the second his question started getting criticism, he changed the question and then added his own answer to suit the new question? If he was able to answer his own question in less than 10 minutes, why did he even post the question at all? – VerySeriousSoftwareEndeavours Apr 03 '15 at 21:38
-
http://blog.stackoverflow.com/2011/07/its-ok-to-ask-and-answer-your-own-questions/ – chopper draw lion4 Apr 03 '15 at 22:27
4 Answers
3
Normally very simple... Log in as root, and do:
select * from mysql.user;
And if you googled on mysql show users you would have gotten this as first hit.
Furthermore, rights to specific databases are held in mysql.db and host limitations are in mysql.host
nl-x
- 11,762
- 7
- 33
- 61
2
Display only User and Host
SELECT User, Host FROM mysql.user
Display users and privileges (pretty way)
SELECT * FROM mysql.user\G;
Create user
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
Grant Privileges (Set)
-- Grant all privileges - Specific Database, all tables
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
-- Grant only SELECT privilege - Specific Database, specific table
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
-- Grant USAGE privilege All databases, all tables
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
List Of Privileges:
CREATE
DROP
GRANT OPTION
LOCK TABLES
REFERENCES
EVENT
ALTER
DELETE
INDEX
INSERT
SELECT
UPDATE
CREATE TEMPORARY TABLES
TRIGGER
CREATE VIEW
SHOW VIEW
ALTER ROUTINE
CREATE ROUTINE
EXECUTE
FILE
CREATE USER
PROCESS
RELOAD
REPLICATION CLIENT
REPLICATION SLAVE
SHOW DATABASES
SHUTDOWN
SUPER
ALL [PRIVILEGES]
USAGE
Leandro Papasidero
- 3,728
- 1
- 18
- 33
0
Performing the following query will provide all your MySQL users:
SELECT user FROM mysql.user;
You may need to login as admin to perform the above query. If that is the case login as admin from terminal by using the following command:
sudo mysql -p
Additionally, you can also create new users as follows:
create user '<user name>'@'<host name>';
Example
create user 'tony'@'localhost';
Depending on the scope of your project you will need to provide this new user with various permissions. To do that use the following syntax:
grant <permission type> on <database name> to '<user name>'@'<host name>';
Example
grant all on uncovery.* to 'tony'@'localhost';
chopper draw lion4
- 12,401
- 13
- 53
- 100
0
If you are looking to do this with a GUI just to get yourself started with MySQL download MySQL workbench.
https://www.mysql.com/products/workbench/
Once this is completed launch the application and add your new connection by clicking the little + sign and filling out the parameters.
Defaults:
Hostname: 127.0.0.1 (if hosted on the machine you are running Workbench)
Username: root
Password: <blank> (if you set the root password enter that here)
Click test connection and make changes until the connection is successful and connect.
You can then click the server drop-down on the top and select Users and Privileges. This will give you a nice easy GUI to play around with things and get familiar with the available options.
Some additional documentation can be found here: https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-navigator-management-users-and-privileges.html
Shane Burke
- 46
- 5