- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
How to prevent a user from accessing a specific schema in MySQL?
To prevent a user from accessing a specific schema, you need to use delete command. Following is the syntax −
DELETE FROM mysql.db WHERE Db IN("yourSpecificSchema","yourSpecificSchema\_%") AND User = "yourUserName" AND Host = "yourHostName";
Let us implement the above syntax to prevent a user from accessing a specific schema. First of all, let us display all users and host from MySQL.user table.
mysql> select user,host from MySQL.user;
This will produce the following output −
+------------------+-----------+ | user | host | +------------------+-----------+ | Bob | % | | User2 | % | | mysql.infoschema | % | | mysql.session | % | | mysql.sys | % | | root | % | | @UserName@ | localhost | | Adam Smith | localhost | | Chris | localhost | | David | localhost | | James | localhost | | John | localhost | | John Doe | localhost | | User1 | localhost | | am | localhost | | hbstudent | localhost | | mysql.infoschema | localhost | | mysql.session | localhost | +------------------+-----------+ 18 rows in set (0.00 sec)
Here, we will prevent user ‘Chris’ with host name ‘localhost’ from accessing a specific schema which has the name ‘sample’ −
mysql> DELETE FROM mysql.db WHERE Db IN("sample","sample\_%") AND User="Chris" AND Host="localhost"; Query OK, 0 rows affected (0.00 sec)
After executing the above query, you need to use FLUSH.
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.06 sec)
- Related Articles
- How to update User Logged in Time for a specific user in MySQL?
- Check privileges (grants) for a specific user in MySQL?
- Display all grants of a specific user in MySQL
- How to exclude a specific row from a table in MySQL?
- Accessing array values in a MongoDB collection to fetch a specific document
- How can we revoke privileges from a MySQL user?
- How to Add Cron Jobs to A Specific User in a Linux System
- How to allow a MySQL user account to connect from any host?
- Insert data from one schema to another in MySQL?
- How to get a specific column record from SELECT query in MySQL?
- How to remove all instances of a specific character from a column in MySQL?
- How to prevent MySQL GROUP BY from collapsing NULL values into a single row?
- MySQL replication: temporarily prevent specific SQL statements replicating to the slaves?
- Granting _SYS_REPO with SELECT to user schema in SAP HANA
- Prevent a combination of items from being inserted twice in MySQL?