MySQLphpmyadmin
I have set up a general user account using phpMyAdmin with no privileges. I can now use GRANT to give SELECT privileges:
GRANT SELECT ON DB.Table TO general@localhost
Is it possible to apply this general user account to users within the table Users and then give them privileges to view only their own data?
For example:
GRANT SELECT ON DB.Booking TO general@localhost IDENTIFIED BY DB.Users.UserId
When you execute GRANT SELECT ON store.catalog TO 'wordpress'@'%';, mysqld wants to insert a row into the grant table mysql.tables_priv. Here is mysql.tables_priv:
mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)
mysql>
Since you want to insert a row into mysql.table_priv where user='wordpress' and host='%', there has to exist a row in mysql.user where user='wordpress' and host='%'.
You also mentioned that you are using MySQL Workbench. You must be using 'root'@'localhost'. That would usually have all rights and a password.
If you want to just allow anonymous SELECT against that table, first run this:
GRANT USAGE ON *.* TO 'wordpress'@'%';
This will place wordpress@'%' into mysql.user. Afterwards, GRANT SELECT ON store.catalog TO 'wordpress'@'%' should run just fine.
You will have to see what other wordpress entries are in mysql.user. This should show what SQL GRANT commands you need:
SELECT CONCAT('GRANT SELECT ON store.catalog TO ',userhost,';') GrantCommand
FROM
(
SELECT CONCAT('''',user,'''@''',host,'''') userhost
FROM mysql.user WHERE user='wordpress'
) A;
I think using a MySQL gui tool like SQLyog would solve your problem. It has User manager tool where you can set privilege to different users.
Best Answer
Database engines do not understand the contents of your tables. You can instruct them to set up a foreign key relationship between the
Userstable and various other tables in the database, and they can enforce that - but the engine cannot automatically prevent a query from returning data that's tied to a differentUserIdthan the one that's stored in a variable or cookie or whatever, indicating who the person connected to your application is.That's something that generally must be done specifically by the application. In most cases, I'd expect this to be done by giving the users of the application forms to fill out; from the input on the form, the application would build a query, including whatever business logic applied (like
UserIdmust match the ID value for the user running the query).If you want to set up every user of your database as an actual database-level login, then some engines (can't recall if MySQL has this capability or not) allow for row-level security. However, this works with users and role set up in the database itself, not user accounts created in and managed by an application.