Solved

Mysql user permission

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

14 Experts available now in Live!

Get 1:1 Help Now