Solved

Mysql user permission

Posted on 2013-11-18
5
31 Views
Last Modified: 2016-05-27
Hi,
Can we restrict an user in mysql to view only limited table?

Details as follows:
(1)We have one database with more over 100 of tables.
(2) We need to create user to view only 20 of tables from database and other user to view all.


Thanks,
Pradeep
0
Comment
Question by:pradeep_jmd
  • 2
5 Comments
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 39658411
It can be done. do you want to restrict any other privilege (other than view) ..like Delete, Insert , Update ?
0
 
LVL 36

Accepted Solution

by:
Loganathan Natarajan earned 250 total points
ID: 39658415
GRANT SELECT, INSERT, DELETE ON dbName.tblName1 TO 'User1'@'%'
GRANT SELECT, INSERT, DELETE ON dbName.tblName2 TO 'User1'@'%'

FLUSH PRIVILEGES;
0
 
LVL 15

Assisted Solution

by:Insoftservice
Insoftservice earned 250 total points
ID: 39686509
Why can't you use some gui tool for the same like navicat or sqlyog i am using sqlyog for the same.
But as per your requirement i have given the sql statements.

% => makes user available in any network
localhost / 127.0.0.1 is made for local machine

GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';

GRANT SELECT, INSERT, DELETE ON database TO username@'%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON database TO username@'127.0.0.1' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;
select * from mysql.user where User='username';
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question