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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. MySQL : How To Find the Slowest Queries
  2. “Access denied for user ‘username’@’hostname’ (using password: YES)” – Error while connecting MySQL with PHP
  3. How to Add Data Nodes to MySQL Cluster without Restarting the Cluster (version 6.4 and later)
  4. How To Start And Stop MySQL Cluster
  5. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  6. How to Restrict MySQL User Creation with Blank Password
  7. MySQL – How to Set Maximum Rates for Connections and Queries
  8. How to Set Space limits for MySQL for database/schema/table
  9. Understanding the REVOKE statement in MySQL
  10. How to backup and restore MySQL database

You May Also Like

Primary Sidebar

Recent Posts

  • What are Command Rules in oracle Database
  • Using Rule Sets in Oracle Database Vault
  • How Realms Work in Oracle Database Vault
  • How to use Privilege Analysis in Oracle Database
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary