Solved

Mysql user permission

Posted on 2013-11-18
5
29 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now