Oracle：检查读/写权限，自动化测试

Oracle : check for read/write privileges, automated testing

提问by Jerry Saravia

I have a question about Oracle and checking privileges.

我有一个关于 Oracle 和检查权限的问题。

Some background Info

一些背景信息

I wrote some php scripts that will 'test' various things in our environment. One of those tasks is checking that a user has execute privileges on a procedure and that the procedure is valid/compiled.

我写了一些 php 脚本来“测试”我们环境中的各种东西。其中一项任务是检查用户是否对某个过程具有执行权限以及该过程是否有效/已编译。

Here is the query

这是查询

select ao.object_name, utp.grantee, ao.status, utp.privilege
from all_objects ao, user_tab_privs utp 
where utp.owner = ao.owner and
ao.object_name = utp.table_name and
upper( ao.object_name ) = :object_name and 
ao.object_type = 'PACKAGE' and
utp.privilege = 'EXECUTE' and
ao.status = 'VALID'

This has worked well and has saved us time on procedure privileges; I do realize now that I can also double check the all_tab_privs to check execute access as well.

这很有效，并为我们节省了程序特权的时间；我现在意识到我也可以仔细检查 all_tab_privs 以检查执行访问权限。

The problem

问题

Now my question is, how do I do something similar with tables? We ran into an issue where a certain user had SELECT privs on a table but not UPDATE/INSERT privs. How can I check for each of these privileges individually. I've looked into all_tab_privs but haven't found it shows me what I want. It has procedures I can execute but when I check to see if a known table is there it isn't. For example, I'll run the following

现在我的问题是，我如何对表格做类似的事情？我们遇到了一个问题，某个用户在表上有 SELECT 权限，但没有 UPDATE/INSERT 权限。我如何单独检查这些权限中的每一个。我查看了 all_tab_privs 但没有发现它显示了我想要的东西。它有我可以执行的程序，但是当我检查是否有已知表时，它不存在。例如，我将运行以下

select * from all_tab_privs 
where table_name = 'KNOWN_TABLE' and 
grantee = 'CURRENT_USER'
and privilege in ( 'SELECT', 'UPDATE', 'INSERT' );

but instead of getting back 3 rows for a table I know 100% that I can already select/insert/update it returns nothing.

但是我没有为表取回 3 行，我 100% 知道我已经可以选择/插入/更新它不返回任何内容。

Any ideas? Thank you.

有任何想法吗？谢谢你。

Disclaimer

免责声明

I am aware that I could just try inserting/updating data and then deleting it but I'd rather not do this. I'd rather not leave any trace since these scripts will run periodically, should be repeatable, and shouldn't alter the state of any piece of data, even if it's just a sequence on the table.

我知道我可以尝试插入/更新数据然后删除它，但我不想这样做。我宁愿不留下任何痕迹，因为这些脚本将定期运行，应该是可重复的，并且不应改变任何数据的状态，即使它只是表上的一个序列。

Also, if you could provide a 'list' of possible queries that I can use to determine privileges that would be fine. For example, to determine if i have select access run query 1, 2 and 3. If either returns data then you have select privs and so on for insert/update.

另外，如果您能提供一个我可以用来确定权限的可能查询的“列表”，那将是很好的。例如，要确定我是否有选择访问运行查询 1、2 和 3。如果任一返回数据，那么您必须选择 privs 等以进行插入/更新。

回答by David Aldridge

This looks rather optimistic to me, as the role issue could get really complex, particularly if roles ever get password protected, and I'd never really trust the method 100% without actually trying the DML.

这对我来说看起来相当乐观，因为角色问题可能会变得非常复杂，特别是如果角色受到密码保护，而且如果不实际尝试 DML，我永远不会真正 100% 信任该方法。

It might be more simple to try queries such as:

尝试以下查询可能更简单：

select count(*)
from   schema_name.table_name
where  1=0;

insert into schema_name.table_name
select *
from   schema_name.table_name
where  1=0;

delete from schema_name.table_name
where  1=0;

update schema_name.table_name
set    column_name = column_name
where  1=0;

I believe that such queries would fail if no privileges were granted (no database handy to check it), and they would never modify any data.

我相信如果没有授予权限（没有数据库可以方便地检查它），这样的查询将会失败，并且它们永远不会修改任何数据。

By the way, the ANY privileges are generally regarded as a security problem, so you might like to fail the system if any user is granted them.

顺便说一下，ANY 权限通常被认为是一个安全问题，因此如果任何用户被授予它们，您可能希望使系统失败。

回答by Alex Poole

If you have privileges granted via roles, you need a more complicated check. The link in comments gives some queries to use to look at the wider picture, but if you want to check what the current user can see - as one of your queries suggests - then you can query the session_rolesview to see what object privileges are currently available to your session via roles, in addition to directly-granted object privileges:

如果您拥有通过角色授予的权限，则需要进行更复杂的检查。评论中的链接提供了一些用于查看更广泛图片的查询，但是如果您想检查当前用户可以看到的内容 - 正如您的一个查询所建议的那样 - 那么您可以查询session_roles视图以查看当前可用的对象权限除了直接授予的对象权限外，还通过角色访问您的会话：

select atp.table_schema, atp.table_name, atp.privilege, atp.grantee,
    'Direct' as grant_type
from all_tab_privs atp
where atp.grantee = user
union all
select atp.table_schema, atp.table_name, atp.privilege, atp.grantee,
    'Via role' as grant_type
from session_roles sr
join all_tab_privs atp on atp.grantee = sr.role;

You can obviously add filters if you want to look at a specific object or privilege, and the grant_typepseudo-column is just for info - not really that useful since you can compare granteewith userto get the same info I suppose.

如果你想看看一个特定的对象或特权可以很明显的添加滤镜，以及grant_type伪列只是为了信息-不是真的有用，因为你可以比较grantee与user获得同样的信息，我想。

You might want to look at session_privsas well, to check your user has any system privileges you expect.

您可能还想查看一下session_privs，以检查您的用户是否具有您期望的任何系统权限。

But if you want a single query to check the privileges for another user or several users at once, you'll need something more like the linked queries, and the privileges necessary to run them.

但是，如果您希望通过单个查询同时检查另一个用户或多个用户的权限，您将需要更多类似于链接查询的内容，以及运行它们所需的权限。

回答by Jerry Saravia

I ended up solving this problem using a multi-step approach based around different queries and the results they brought back. I'm executing all the queries with some PHP code so it wasn't 100% necessary that I only have big query to solve it all.

我最终使用基于不同查询及其返回结果的多步骤方法解决了这个问题。我正在使用一些 PHP 代码执行所有查询，因此我并不是 100% 需要只有大查询来解决所有问题。

Additionally, our databases are split up physically and they are linked together through database links so I had to do some additional work to make sure that this privilege checking worked across database links.

此外，我们的数据库在物理上是分开的，它们通过数据库链接链接在一起，所以我必须做一些额外的工作来确保这个权限检查跨数据库链接工作。

Currently I'm only checking for SELECT, DELETE, UPDATE, and INSERT privileges; that is all I need really for now.

目前我只检查 SELECT、DELETE、UPDATE 和 INSERT 权限；这就是我现在真正需要的。

The procedure

步骤

Here is the general procedure in a nut-list.

以下是坚果列表中的一般程序。

1. Get a list of all the database links available to the user.

2. For the following steps, start with the current database we are logged into and then check each database link retrieved from step 1.

   2a. Check to see if the table is visible using a database query.

   2b. If the table is visible, check to see if any of the permission queries return that this user has access to the table.

1. 获取用户可用的所有数据库链接的列表。

2. 对于以下步骤，从我们登录的当前数据库开始，然后检查从步骤 1 中检索到的每个数据库链接。

   2a. 使用数据库查询检查该表是否可见。

   2b. 如果该表可见，请检查是否有任何权限查询返回该用户有权访问该表。

The queries

查询

Here are the queries for each of the steps above.

以下是对上述每个步骤的查询。

1 Database links

1 数据库链接

select db_link from all_db_links

2a Table Visibility

2a 表格可见性

select * from all_tables%DB_LINK% where table_name = :table_name and owner = :owner

The %DB_LINK% above is replaced by @db_link where applicable. If we are checking the current connection then I remove it entirely. Remember, the queries are being executed by a PHP script so I can do some string manipulation on the string to either remove the %DB_LINK% for the current database or replace it with one of the database links we got from step 1.

在适用的情况下，上面的 %DB_LINK% 替换为 @db_link。如果我们正在检查当前连接，那么我将其完全删除。请记住，查询是由 PHP 脚本执行的，因此我可以对字符串进行一些字符串操作，以删除当前数据库的 %DB_LINK% 或将其替换为我们从步骤 1 中获得的数据库链接之一。

2b. Users, Roles, Tables

2b. 用户、角色、表

There are 4 queries all together here.

这里总共有 4 个查询。

/*****/
/* 1 */
/*****/
select * 
from user_tab_privs%DB_LINK%
where 
owner = :owner
and
table_name = :table_name
and
privilege = :privilege

/*****/
/* 2 */
/*****/

select * from user_sys_privs%DB_LINK% where privilege = :privilege

/*****/
/* 3 */
/*****/

select * from
      (
        select distinct granted_role from 
        (
          select null linker, granted_role 
          from user_role_privs%DB_LINK%
          union all
          select role linker, granted_role 
          from role_role_privs%DB_LINK%
        )
        start with linker is null
        connect by prior granted_role = linker 
      ) user_roles join role_tab_privs%DB_LINK% rtab on user_roles.granted_role = rtab.role
      where 
      owner = :owner
      and
      table_name = :table_name
      and
      rtab.privilege = :privilege

/*****/
/* 4 */
/*****/

select * from
  (
    select distinct granted_role from 
    (
      select null linker, granted_role 
      from user_role_privs%DB_LINK%
      union all
      select role linker, granted_role 
      from role_role_privs%DB_LINK%
    )
    start with linker is null
    connect by prior granted_role = linker 
  ) user_roles join role_sys_privs%DB_LINK% rtab on user_roles.granted_role = rtab.role
  where rtab.privilege = :privilege

Explanations

说明

Database links

数据库链接

In the phpunit tests I pass in two things : table name and schema name ( owner ). However, because of the database links we have to explicitly check the other databases by using the @db_link in the queries. Otherwise I might report a table as being inaccessible when in reality it is accessible via the database link.

在 phpunit 测试中，我传入了两件事：表名和模式名（所有者）。但是，由于数据库链接，我们必须在查询中使用@db_link 显式检查其他数据库。否则我可能会报告一个表无法访问，而实际上它可以通过数据库链接访问。

Table visibility

表格可见性

If the user can't see the table then there is no point in checking for privileges. Checking for privileges also prevents the case where a user has been given 'SELECT ANY TABLE' privileges but the table itself doesn't actually exist from causing an unwanted failure.

如果用户看不到该表，则检查权限没有意义。检查权限还可以防止用户被授予“SELECT ANY TABLE”权限但表本身实际上并不存在的情况导致意外失败。

The band of 4 queries

4 个查询的波段

As shown by other posters, a user can be given access to a table in many ways. Specifically, they can be given roles, and those roles can be given roles, and then those roles can be assigned access. Or the user can be given explicit access or generic access through system privileges.

正如其他海报所示，可以通过多种方式授予用户访问表的权限。具体来说，他们可以被赋予角色，这些角色可以被赋予角色，然后这些角色可以被分配访问权限。或者可以通过系统权限授予用户显式访问权限或通用访问权限。

查询 1

The first of the four queries checks to see if the user has been given explicit SELECT, DELETE, etc, privileges on the table. It's easy to understand and would ideally be all that's necessary

四个查询中的第一个检查用户是否已被授予对表的显式 SELECT、DELETE 等权限。它很容易理解，最好是所有必要的

查询 2

The second checks to see if the user has been granted any system privileges like DELETE ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, etc. These are not granted explicitly on a table but the user can perform any of the referenced actions on any table they have visibility.

第二个检查用户是否已被授予任何系统权限，如 DELETE ANY TABLE、SELECT ANY TABLE、INSERT ANY TABLE 等。这些权限不是在表上明确授予的，但用户可以对任何表执行任何引用的操作他们有知名度。

查询 3

The third query will see if any of the roles that user has, either directly or indirectly, has been given explicit SELECT, DELETE, etc, privileges on the table. This is similar to query 1 except it's based on the roles given to the user, not the user.

第三个查询将查看用户拥有的任何角色（直接或间接）是否已被授予对表的显式 SELECT、DELETE 等权限。这与查询 1 类似，不同之处在于它基于赋予用户的角色，而不是用户。

查询 4

The fourth checks to see if any of the roles that user has, either directly or indirectly, has been given any system privileges like DELETE ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, etc. This one is similar to query 2.

第四个检查用户是否直接或间接拥有的任何角色已被授予任何系统权限，如 DELETE ANY TABLE、SELECT ANY TABLE、INSERT ANY TABLE 等。这个类似于查询 2。

That's it! I chain these together and use the results returned from each to determine whether a user has the desired privileges or not.

就是这样！我将这些链接在一起并使用从每个返回的结果来确定用户是否具有所需的权限。

Details worth mentioning

值得一提的细节

If a user has any privileges across db_link_1 it does NOT mean that they have the same privileges on tables they access across db_link_2. Most should know this but I wanted to make sure I stated it explicitly. For example, select privs on table 1 across db_link_1 does not imply select privs on table 2 across db_link_2.

如果用户在 db_link_1 上拥有任何权限，这并不意味着他们对通过 db_link_2 访问的表具有相同的权限。大多数人应该知道这一点，但我想确保我明确说明了这一点。例如，跨 db_link_1 在表 1 上选择 privs 并不意味着跨 db_link_2 在表 2 上选择 priv。

I check each db_link one at a time. So first I start of with the database I'm connected to directly, no database link required for this. Then, if I can't find the table or don't have the privs on the table I move on to the next database link.

我一次检查每个 db_link。所以首先我从我直接连接的数据库开始，不需要数据库链接。然后，如果我找不到表或表上没有权限，我将转到下一个数据库链接。

In queries 2 and 4, I use 'SELECT ANY TABLE', 'DELETE ANY TABLE', etc in place of the :privilege variable.

在查询 2 和 4 中，我使用“SELECT ANY TABLE”、“DELETE ANY TABLE”等代替 :privilege 变量。

In queries 1 and 3, I use 'SELECT', 'DELETE', 'UPDATE', 'INSERT' in place of the :privilege variable.

在查询 1 和 3 中，我使用“SELECT”、“DELETE”、“UPDATE”、“INSERT”代替 :privilege 变量。