只允许用户来自数据库的某些信息 [英] Allow users only certain information from database

问题描述

我有一个大约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.

这篇关于只允许用户来自数据库的某些信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！