我不确定我是否正确理解了这个问题，但似乎正在要求能够限制从Persons表中选择数据的人这样他们就不能在Secret列中看到该值，但是应该允许他们在查询内部(在WHERE子句等)中使用Secret列.

I'm not sure that I'm understanding the question correctly, but it seems to be asking for the ability to restrict the person selecting data from the Persons table so that they cannot see the value in the Secret column, but they should be allowed to use the Secret column in the interior of the query (in the WHERE clause, etc).

CREATE TABLE Person
(
    Id      ...,
    Secret  ...,
    ...
);
REVOKE ALL ON Person FROM PUBLIC;
GRANT SELECT(id) ON Person TO SomeOne;

因此，如果我的解释正确，那么当SomeOne选择数据时

So, if my interpretation is correct, when SomeOne selects data:

SELECT Id     FROM Person;    -- Works (as required)
SELECT Secret FROM Person;    -- Fails (as required)
SELECT Id
  FROM Person
 WHERE Secret = 1;            -- Fails (but we want it to work)

SQL不允许这样做，这是有充分理由的.基本上，如果您可以对Secret设置查询结果条件，则可以通过重复查询来确定Secret的值，因此假定为机密的内容不会成为秘密.泄漏信息非常容易.

SQL does not allow that, and for good reason. Basically, if you can condition query results on Secret, you can determine the value of Secret with repeated queries, so what is supposed to be secret does not remain a secret. It is very easy to leak information.

从失败的查询开始，但是不应" ...从查询结果中，您知道返回的每个Id的Secret值均为1，因此对于那些Id值而言，Secret不再是机密.

Looking at the query that fails but "shouldn't"...from its results, you know that every Id returned has the Secret value of 1, so for those Id values, the Secret is no longer secret.

如果您查看统计数据库，而该数据库只允许搜索汇总数据，那么您会发现有一些称为唯一跟踪器"的内容，即使您只被允许执行以下操作，它也基本上可以识别一个人的特征请参阅结果集中的汇总(SUM，COUNT，...)值.这是一个比您面临的场景还要复杂的场景(但是很有趣). CJ日期(已绝版)数据库系统简介，第二卷" 讨论了统计数据库和唯一跟踪器. (在统计数据库唯一跟踪器"上的Google搜索显示了更易于访问的有用的外观信息.)

If you look into Statistical Databases, where you're only allowed to search on aggregate data, you find there are things called Unique Trackers which basically allow you to identify the characteristics of one person, even if you're only allowed to see aggregate (SUM, COUNT, ...) values in the result sets. This is a more complex scenario than you're facing (but a fascinating one). C J Date's (long out of print) "Introduction to Database Systems, Vol II" has a discussion of Statistical Database and Unique Trackers. (Google search on 'statistical database unique tracker' reveals useful looking information that is more accessible.)

因此，如果我了解所需的内容，则认为该需求被误导了-并且SQL标准不允许您执行所需的操作.

So, if I've understood what is desired, I believe the desire is misguided — and the SQL standard does not permit what you seek.

有没有解决方法?

如果查询可以内置到视图中，则创建视图的人员可以访问基础详细数据并授予对该视图的访问权限，但是使用该视图的人员无法执行原始查询；这可能会为您提供保护.类似的注释也适用于存储过程，并可以更好地对查询进行参数化.

If the query can be built into a view, the person creating the view can access the underlying detail data and grant access to the view, but the people using the view cannot execute the raw query; this might give you protection you seek. Similar comments apply to stored procedures and allow the query to be parameterized better.