GRANT is meant for adding privileges to users. Confusingly, it also has the ability to create users and change their passwords. This functionality is deprecated and should not be used. If you use GRANT with IDENTIFIED you can change the user’s password: When IDENTIFIED is present and you have the global grant privilege (GRANT OPTION), … Read more
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 … Read more
Assuming by full dump you also mean the VIEWs and the EVENTs, you would need: GRANT USAGE ON *.* TO ‘dump’@’%’ IDENTIFIED BY …; GRANT SELECT, LOCK TABLES ON `mysql`.* TO ‘dump’@’%’; GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `myschema`.* TO ‘dump’@’%’; and if you have VIEWs that execute a function, then unfortunately … Read more
Thanks to Mike’s comment, I’ve re-read the doc and I’ve realised that my current user (i.e. userA that already has the create privilege) wasn’t a direct/indirect member of the new owning role… So the solution was quite simple – I’ve just done this grant: grant userB to userA; That’s all folks 😉 Update: Another requirement … Read more
It looks like you can also use a netmask, e.g. GRANT … TO ‘user’@’192.168.0.0/255.255.255.0’ IDENTIFIED BY …
You found the shorthand to set privileges for all existing tables in the given schema. The manual clarifies: (but note that ALL TABLES is considered to include views and foreign tables). Bold emphasis mine. serial columns are implemented with nextval() on a sequence as column default and, quoting the manual: For sequences, this privilege allows … Read more
I already found it: SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name=”mytable”
If you want more than just direct table grants (e.g., grants via roles, system privileges such as select any table, etc.), here are some additional queries: System privileges for a user: SELECT PRIVILEGE FROM sys.dba_sys_privs WHERE grantee = <theUser> UNION SELECT PRIVILEGE FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role) WHERE rp.grantee = … Read more
As you said, in MySQL USAGE is synonymous with “no privileges”. From the MySQL Reference Manual: The USAGE privilege specifier stands for “no privileges.” It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges. USAGE is a way to tell … Read more