Mysql – How to show user’s privileges in MySQL

To list users:

select user,host from mysql.user;

To show privileges:

show grants for 'user'@'host';

To change privileges, first revoke. Such as:

revoke all privileges on *.* from 'user'@'host';

Then grant the appropriate privileges as desired:

grant SELECT,INSERT,UPDATE,DELETE ON `db`.* TO 'user'@'host';

Finally, flush:

flush privileges;

The MySQL documentation is excellent:

https://dev.mysql.com/doc/refman/8.0/en/access-control.html

The answers to your questions come from the online PostgreSQL 8.4 docs.

1. GRANT ALL PRIVILEGES ON DATABASE grants the CREATE, CONNECT, and TEMPORARY privileges on a database to a role (users are properly referred to as roles). None of those privileges actually permits a role to read data from a table; SELECT privilege on the table is required for that.

2. I'm not sure there is a "proper" way to grant all privileges on all tables to a role. The best way to ensure a given role has all privileges on a table is to ensure that the role owns the table. By default, every newly created object is owned by the role that created it, so if you want a role to have all privileges on a table, use that role to create it.

   PostgreSQL 9.0 introduces the following syntax that is almost what you want:

   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;

   The rub is that if you create tables in schemas outside the default "public" schema, this GRANT won't apply to them. If you do use non-public schemas, you'll have to GRANT the privileges to those schemas separately.