• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

MySQL Grants – Setting User Permissions On Different Tables

by admin

Question: How to set a user, read-only permission for few tables, and alter/insert/delete permission for the rest of the tables?

This can be achieved using MySQL grant’s, as MySQL do allow to allow/revoke privileges at a table level. Normally, a database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics. For example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost'; GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost'; GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

All the possible privileges can be found in the Grant Privileges section on the online manual. The same applies to stored procedures privileges as mentioned here.

Note: The below syntax is not supported:

GRANT INSERT, DELETE, ALTER, UPDATE ON *.myTableName TO 'myUser'@'%';

Meaning it’s not possible to specify the same table name on all existing databases.

Filed Under: mysql

Some more articles you might also be interested in …

  1. Which Ports are Used by mysqld, ndb_mgmd, and ndbd/ndbmtd in a MySQL Cluster Installation
  2. How to Change the Default Character Set and Collation for a Database in MySQL
  3. How to Disable Client Access Control in MySQL
  4. how to capture the SQL statements of a binary log and edit them before restoring the data (Point-in-time Recovery for MySQL)
  5. Troubleshooting MySQL Query hung – “Waiting for Table Flush”
  6. How to use a Wrapper Script to set Custom Per-Process Attributes for MySQL Server
  7. Beginners Guide to Management of MySQL Cluster Log Files
  8. How To Create a Local Yum Repository for MySQL Enterprise Packages
  9. Configure MySQL Router to Auto Restart of Failure using systemd
  10. MySQL 8.0 : Persisted Variables

You May Also Like

Primary Sidebar

Recent Posts

  • qsub Command Examples in Linux
  • qsub: command not found
  • qrcp Command Examples in Linux
  • qmrestore Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright