2

Reference the attached screenshot:

enter image description here

  1. 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"?
  2. Why three root User names? What's the purpose of having so many?
1

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.

  1. 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.

  1. 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.

2
  • In this case: was it unnecessary to give the new user database specific privileges when the user already had all global privileges? Jul 28 '17 at 18:49
  • 1
    I can't tell 100% for sure, but my guess is your right, if the user had global privileges, having the same privileges on a database-specific case is unnecessary. However, keep in mind that if you have both of them, and you remove the global ones, the specific would remain only if they were actually specified explicitly.
    – joanolo
    Jul 28 '17 at 18:54
2

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

  • Shutdown the Database
  • See the processlist of other users
  • Setup Replication on their own
  • Kill DB Connections
  • Create a New User
  • Create Giant Tablespace Files
  • Load an External File into the Database
  • Create External Files in the OS using SELECT ... INTO OUTFILE
  • and so forth

SUPPLEMENTAL INFO

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.