Solved

Mysql user permission

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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