Reference the attached screenshot:
All Privileges of Type -- global and database-specific. What's the difference between these 2 "Types"?root User names? What's the purpose of having so many?
Your second question was well covered by joanolo.
I would like to address your first question only.
On Apr 17, 2014, I answered this question : How to grant super privilege to the user?. In that post, I explained how the SUPER privilege was impossible to grant to a database-specific user. I clarified the difference between global privileges and database-specific privileges by comparing the table structure of mysql.user and mysql.db.
To show the difference between them in terms of grants side-by-side, please run this query:
SELECT
REPLACE(glb,'_priv','') global_privilege,
IFNULL(REPLACE(dbs,'_priv',''),'NOT ALLOWED') database_privilege
FROM (SELECT A.column_name glb,B.column_name dbs FROM
(SELECT column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user'
AND column_name LIKE '%priv') A
LEFT JOIN
(SELECT column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='db'
AND column_name LIKE '%priv') B
ON A.column_name=B.column_name) AA;
When you run this in MySQL 5.7.12, you get the following output:
+-------------------+--------------------+
| global_privilege | database_privilege |
+-------------------+--------------------+
| Select | Select |
| Insert | Insert |
| Update | Update |
| Delete | Delete |
| Create | Create |
| Drop | Drop |
| Grant | Grant |
| References | References |
| Index | Index |
| Alter | Alter |
| Create_tmp_table | Create_tmp_table |
| Lock_tables | Lock_tables |
| Create_view | Create_view |
| Show_view | Show_view |
| Create_routine | Create_routine |
| Alter_routine | Alter_routine |
| Execute | Execute |
| Event | Event |
| Trigger | Trigger |
| Reload | NOT ALLOWED |
| Shutdown | NOT ALLOWED |
| Process | NOT ALLOWED |
| File | NOT ALLOWED |
| Show_db | NOT ALLOWED |
| Super | NOT ALLOWED |
| Repl_slave | NOT ALLOWED |
| Repl_client | NOT ALLOWED |
| Create_user | NOT ALLOWED |
| Create_tablespace | NOT ALLOWED |
+-------------------+--------------------+
29 rows in set (0.04 sec)
Given this display, it is impossible to grant RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER, and CREATE TABLESPACE to a database-specific user.
For example, you don't want a database-specific user to
There are also table-specific grants in mysql.tables_priv and column-specific grants in mysql.columns_priv. These grants are stored in ENUM columns.
Just run DESC mysql.tables_priv; and DESC mysql.columns_priv; and see.
phpMyAdmin shows you the privileges of the users with regard to the underlying MySQL database instance to which you're connected. So, the answers don't come from phpMyAdmin, but from MySQL.
- I created a non-root user, and gave it all privileges to a database. It now has "All" privileges of "Type" -- "global" and "database-specific". What's the difference between these 2 "Types"?
You can have one user have certain privileges for a given database (that's the database-specific, while s/he has some others for all the databases (that's the global). Someone with global privileges can, for instance, create new databases. That's something that person won't be able to do it s/he has only database-specific privileges.
- Why three "root" "User names"? What's the purpose of having so many?
In MySQL you can give different privileges to users based not only on the username, but also based on the IP address or DNS name from which they connect. In principle, localhost represents your local machine (or, in actuality, the machine where phpMyAdmin is running), accessed either via IP protocol version 4 or version 6, 127.0.0.1 represents localhost through IPv4, and ::1 represents your local machine via IPv6 (actually, they represent the virtua loopback interface, but don't bother with the subtle distinction). However, localhost could be changed on the hosts file of the machine to have a different meaning (don't do it or your machine might become inoperative!).
You have the three different settings (I think that by default) because the client connecting to your database might be using either an IPv4 address, an IPv6 address, or an address that maps to the localhost name. This is done so that, no matter which is the connection method, the root user can access MySQL and setup and change the database(s).
If you would like the user root to be able to connect from a remote computer (apart from changing some default configuration parameters), you would need to give him/her permissions to connect from a certain IP address (such as 123.123.123.123). Check the account names documentation from MySQL for how to specify IP addresses, DNS names, or wildcards.
