问题描述
我有一个大约100个用户的信息数据库.我有2个对数据库具有权限的MySQL用户. root以及特定的数据库用户目录".用户目录"仅具有运行SELECT语句的权限,而没有其他权限.
I have a database of information for 100 or so users. I have 2 MySQL users that has permission to the database. root and also a specific database user "directory". User "directory" only has permission to run SELECT statements, nothing else.
一些信息对该用户敏感,并且我正在使用PHP作为代理来检测他们在运行任何SQL命令之前是哪个用户,我如何附加每个SELECT语句以包含WHERE id = '$their_id'
,其中$their_id
是一个带有其ID的php变量.
Some of the information is sensitive to that user and I am using PHP as a proxy to detect which user they are before any SQL commands are ran, how can I append every SELECT statement to include WHERE id = '$their_id'
where $their_id
is a php variable with their id.
请注意,用户将能够编写其PHP mysql命令,我正在db.php中定义这些限制,其中将包括连接信息.
Please note the users will have ability to write their PHP mysql commands, I am defining these restrictions in a db.php that will include the connection information.
我想防止说joe
不是bob
时用户joe
能够运行类似SELECT * WHERE id = 'bob'
的命令.
I want to prevent say the user joe
being able to run a command like SELECT * WHERE id = 'bob'
when joe
isn't bob
.
我目前不扫描任何MySQL命令,我只是在db.php中为它们创建连接.无论如何,我可以制作自己的代理MySQL命令并强制它们使用它们,那样它们就必须使用我的命令mysql_query
而不是默认的mysql_query
来覆盖我所做的任何处理.
I currently do not scan-through any of the MySQL commands, I simply just create the connection for them in db.php. Is there anyway I can make my own proxy MySQL commands and force them to use them, that way they have to use my command mysql_query
and not the default mysql_query
to override any processing I do.
推荐答案
与其修改查询,不如总是让他们通过视图引用数据.他们根本没有创建视图所依据的实际表的权限.您使用
Rather than modify their query just always have them reference the data through a view. They have no rights at all to the real table the view is created over. You create that view with
Create View tablenametheyuse as select * from realtable with id = 'user'
并且仅授予该用户对其视图的权限.
and only give that user authority to his view.
现在,他们可以在视图上以任何方式对select语句进行编码.
They now code their select statements any way they want over the view.
这篇关于只允许用户来自数据库的某些信息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!