2

Following on from my question here How to REVOKE ROLE GRANTED BY another user on Firebird 2.5.8?

and @Arioch's helpful suggestion to query what ROLE is actually connected prior to the command.

SELECT CURRENT_ROLE FROM RDB$DATABASE

Now despite my connection string explicitly using "ROLE=RDB$ADMIN", checking in gsec that the given user has admin option, when I run the command the result is NONE. This was using the Firebird ADO.NET FirebirdSql.Data.FirebirdClient interface v6.6.

So I performed a similar check by using FlameRobin, logging in with the same user and the RDB$ADMIN ROLE specified, and the result is the same:

enter image description here

Any ideas what I am doing wrong as to why this is not returning 'RDB$ADMIN'?

David Carr
  • 400
  • 4
  • 7

1 Answers1

3

As user rights are granted in a specific database, the user must be RDB$ADMIN in that specific database and must specify the role on connect. That a user has the admin role in GSEC only means that a user has the admin role in the security database. That doesn't mean they have the admin role in a specific database.

The fact CURRENT_ROLE returns NONE means that the user either didn't specify a role, or hasn't been granted the RDB$ADMIN role in the current database.

To quote from the Firebird Language Reference, User Authentication, RDB$ADMIN Role:

The internally-created role RDB$ADMIN is present in every database. Assigning the RDB$ADMIN role to a regular user in a database grants that user the privileges of the SYSDBA, in the current database only.

The elevated privileges take effect when the user is logged in to that regular database under the RDB$ADMIN role and give full control over all objects in the database.

Being granted the RDB$ADMIN role in the security database confers the authority to create, edit and delete user accounts.

and

To manage user accounts through SQL, the grantee must specify the RDB$ADMIN role when connecting. No user can connect to the security database, so the solution is that the user connects to a regular database where he also has RDB$ADMIN rights, supplying the RDB$ADMIN role in his login parameters. From there, he can submit any SQL user management command.

The SQL route for the user is blocked for any database in which he has not been the granted the RDB$ADMIN role.

The GRANT ADMIN ROLE clause applies only to the security database (specifically for the CREATE/ALTER/DROP USER SQL user management statements), and only if the user connects through a normal database where they also have the RDB$ADMIN role specifying that role on connect.

To grant a user administrator privileges in a specific database, they need to have been granted that role - in that specific database - using:

GRANT [ROLE] RDB$ADMIN TO username

And they need to specify the role on connect.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • One more thing is `gsec` functionality seems to be reduced in FB3 ( can not select non-default security provider ) and will be totally disabled one day later. Since topic starter was asking about FB3, he better prepare to get rid of `gsec` in his workflow – Arioch 'The Aug 16 '19 at 15:52
  • @Arioch'The gsec is deprecated, as mentioned in the Firebird 3 release notes. On the other hand, given how Firebird handles other deprecated things (eg dialect 1), who knows how long it will take for it to be really gone. – Mark Rotteveel Aug 16 '19 at 15:54
  • Perhaps not that long... Though i'd prefer they rather extended it. Another comparison point can be ODS 9.x till 11.x in FB3 – Arioch 'The Aug 16 '19 at 17:01