Solved

Mysql user permission

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

Title # Comments Views Activity
mysql ide 10 48
when to use sequences in mysql 4 38
Could you point a way on how to connect to a remote DB from a MySQL stored function? 3 65
How to fix Datetime in MySQL? 4 64
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 …
Creating and Managing Databases with phpMyAdmin in cPanel.

685 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