[mysql] Remove privileges from MySQL database

The USAGE-privilege in mysql simply means that there are no privileges for the user 'phpadmin'@'localhost' defined on global level *.*. Additionally the same user has ALL-privilege on database phpmyadmin phpadmin.*.

So if you want to remove all the privileges and start totally from scratch do the following:

  • Revoke all privileges on database level:

    REVOKE ALL PRIVILEGES ON phpmyadmin.* FROM 'phpmyadmin'@'localhost';

  • Drop the user 'phpmyadmin'@'localhost'

    DROP USER 'phpmyadmin'@'localhost';

Above procedure will entirely remove the user from your instance, this means you can recreate him from scratch.

To give you a bit background on what described above: as soon as you create a user the mysql.user table will be populated. If you look on a record in it, you will see the user and all privileges set to 'N'. If you do a show grants for 'phpmyadmin'@'localhost'; you will see, the allready familliar, output above. Simply translated to "no privileges on global level for the user". Now your grant ALL to this user on database level, this will be stored in the table mysql.db. If you do a SELECT * FROM mysql.db WHERE db = 'nameofdb'; you will see a 'Y' on every priv.

Above described shows the scenario you have on your db at the present. So having a user that only has USAGE privilege means, that this user can connect, but besides of SHOW GLOBAL VARIABLES; SHOW GLOBAL STATUS; he has no other privileges.

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to ubuntu

grep's at sign caught as whitespace "E: Unable to locate package python-pip" on Ubuntu 18.04 How to Install pip for python 3.7 on Ubuntu 18? "Repository does not have a release file" error ping: google.com: Temporary failure in name resolution How to install JDK 11 under Ubuntu? How to upgrade Python version to 3.7? Issue in installing php7.2-mcrypt Install Qt on Ubuntu Failed to start mongod.service: Unit mongod.service not found

Examples related to phpmyadmin

phpMyAdmin on MySQL 8.0 phpmyadmin - count(): Parameter must be an array or an object that implements Countable Error loading MySQLdb Module 'Did you install mysqlclient or MySQL-python?' phpMyAdmin ERROR: mysqli_real_connect(): (HY000/1045): Access denied for user 'pma'@'localhost' (using password: NO) phpMyAdmin access denied for user 'root'@'localhost' (using password: NO) mysqli_real_connect(): (HY000/2002): No such file or directory How to create a foreign key in phpmyadmin #1292 - Incorrect date value: '0000-00-00' MySQL error - #1932 - Table 'phpmyadmin.pma user config' doesn't exist in engine phpMyAdmin Error: The mbstring extension is missing. Please check your PHP configuration

Examples related to privileges

Check Postgres access for a user Give all permissions to a user on a PostgreSQL database ORA-01950: no privileges on tablespace 'USERS' Getting ORA-01031: insufficient privileges while querying a table instead of ORA-00942: table or view does not exist Remove privileges from MySQL database Permission denied for relation How to view user privileges using windows cmd? Grant all on a specific schema in the db to a group role in PostgreSQL How to show all privileges from a user in oracle? What precisely does 'Run as administrator' do?

Examples related to grant

Postgresql: error "must be owner of relation" when changing a owner object Give all permissions to a user on a PostgreSQL database Remove privileges from MySQL database What GRANT USAGE ON SCHEMA exactly do? Permission denied for relation Grant SELECT on multiple tables oracle How to grant remote access to MySQL for a whole subnet? Grant all on a specific schema in the db to a group role in PostgreSQL grant remote access of MySQL database from any IP address Query grants for a table in postgres