如何撤销Firebird 2.5.8上另一个用户授予的角色? [英] How to REVOKE ROLE GRANTED BY another user on Firebird 2.5.8?

问题描述

我正在使用Firebird 2.5.8(ODS版本11.2)，并通过Firebird ADO.NET v6.6(在使用Visual Studio的C#中)进行连接.我已经建立了一个数据库管理工具来配置我们的表，以及执行一些基本的Firebird用户管理操作.该数据库具有不同的角色(MyRoleX和MyRoleY)，用于授予/限制访问权限.

I am working with Firebird 2.5.8, ODS Version 11.2, connecting via Firebird ADO.NET v6.6 (in C# using Visual Studio). I have built a database management tool for configuring our tables, as well as performing some basic Firebird user management operations. The database has different roles (MyRoleX and MyRoleY) defined to give/restrict access.

用户管理操作包括将这些角色授予/撤销给不同的用户.登录该工具后，连接使用RDB $ ADMIN ROLE，并且已使用ADMIN ROLE创建了连接的用户.最后，该工具可能有多个Firebird用户(例如Mgr1和Mgr2).

User management operations include granting/revoking these roles to different users. When logged into the tool, the connection uses the RDB$ADMIN ROLE and the connected user has been created with the ADMIN ROLE. Lastly, there may be more than one Firebird user of the tool (e.g. Mgr1 and Mgr2).

好的，因此Mgr1可以创建一个新用户，以及:

Ok, so Mgr1 CREATEs a new user, along with:

GRANT MyRoleX TO UserA;
GRANT MyRoleY TO UserA;

然后，Mgr1不在班次/空缺/不可用，并且Mgr2意识到不应向UserA授予MyRoleY.但是当Mgr2登录并尝试运行命令时:

Mgr1 then is off shift/vacation/unavailable, and Mgr2 realizes UserA should not have been granted MyRoleY. But when Mgr2 logs in and tries to run the command:

REVOKE MyRoleY FROM UserA;

给出错误信息:

unsuccessful metadata update
Mgr2 is not grantor of Role on MyRoleY to UserA.

，如果命令更改为:

REVOKE MyRoleY FROM UserA GRANTED BY Mgr1;

然后显示错误消息:

unsuccessful metadata update
Only SYSDBA or database owner can use GRANTED BY clause.

虽然第二条消息是明确的，但是，如果同时使用ROLE = RDB $ ADMIN(当然，这些用户被授予ADMIN ROLE)连接了Mgr1和Mgr2，为什么他们不能执行此操作?

While the 2nd message is explicitly clear, why, if both Mgr1 and Mgr2 are connected using ROLE=RDB$ADMIN (and of course these users are granted ADMIN ROLE), can they NOT perform this operation?

来自授予许可的特权"标题下指出:

From Statements for Revoking Privileges , under the heading 'Revoking Privileges That Were GRANTED BY' it states:

当前用户必须以完全管理权限登录

the current user must be logged in either with full administrative privileges

如果以RDB $ ADMIN身份登录，那不是完全的管理员特权吗?

If logged in under under RDB$ADMIN, is that not full admin privileges?

在链接顶部标题

At the top of the link under the heading 'RDB$ADMIN Role', it also states:

将RDB $ ADMIN角色分配给数据库中的常规用户将授予该用户SYSDBA的特权.

Assigning the RDB$ADMIN role to a regular user in a database grants that user the privileges of the SYSDBA.

那么为什么Mgr2拥有像SYSDBA这样的特权?

So why then does Mgr2 have privilege like SYSDBA?

一些寻求答案的问题:

1. 我在这里做错什么了吗?有没有一种方法可以连接或允许Mg​​r2将GRANT撤销到Mgr1制作的角色?

1. Am I doing anything wrong here? Is there a way to connect or allow Mgr2 to REVOKE GRANTs to ROLEs made by Mgr1?

我们不想共享SYSDBA或数据库所有者凭据来执行这些操作，所以还有其他解决方案吗?

We do NOT want to be sharing the SYSDBA nor database owner credentials to perform these operations, so any other solutions?

推荐答案

由于Firebird 2.5.9发行说明未提及任何与用户授权相关的错误修正，因此我认为您误解了一些内容，可能是您在未调用RDB$ADMIN时您使用Mgr2登录.在尝试撤消操作之前，请尝试查询活动角色.

Since Firebird 2.5.9 Release Notes do not mention any user grant related bugfixes, I think you mistook something, probably you just did not invoke the RDB$ADMIN when you logged in with Mgr2. Try querying the active role just before trying to revoke.

只需使用 IBExpert 套件在 Firebird 2.5.9 Win64 中进行了尝试.

Just tried this in Firebird 2.5.9 Win64 using IBExpert suite.

第一次会议:

/*** connected as SYSDBA with no role specified ***/
GRANT RDB$ADMIN TO ADM_1;
GRANT RDB$ADMIN TO ADM_2;
CREATE ROLE USER_ROLE;

第二届会议:

/*****  ADM_1 with RDB$ADMIN role specified *****/
select current_role, current_user from rdb$database;
-- ROLE         USER
-- RDB$ADMIN    ADM_1

grant user_role to user_1;
grant user_role to user_2 granted by sysdba;

第三次会议:

/*****  ADM_2 with RDB$ADMIN role specified *****/
select current_role, current_user from rdb$database;
-- ROLE         USER
-- RDB$ADMIN    ADM_2

revoke user_role from user_2 granted by sysdba;
-- OK

revoke user_role from user_1;
-- This operation is not defined for system tables.
-- unsuccessful metadata update.
-- ADM_2 is not grantor of Role on USER_ROLE to USER_1.

revoke user_role from user_1 granted by adm_1;
-- OK

因此，至少在2.5.9 SuperServer中，它具有到数据库的单个连接-它可以正常工作.

So, at least in 2.5.9 SuperServer with a single connection to the database - it just works.

P.S.由于您可以拥有的管理员人数不止两个，而且有多个管理员可以向用户授予角色，因此必须逐一找到并撤消其中的每个授予，所以我建议您针对这种情况，用户，然后以他的名字提供所有赠款，就像我在第二次会话中对SYSDBA所做的那样.

P.S. since you can have many more admins than just two, and since SEVERAL admins may grant a role to the user, and then EACH of those grants would have to be found and revoked one by one, so I suggest for your scenario you have a dedicated user then, with all grants being given in his name, like I did with SYSDBA in my second session.

这篇关于如何撤销Firebird 2.5.8上另一个用户授予的角色?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！