Why does Firebird 2.5.8 return NONE on SELECT CURRENT_ROLE?

Question

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:

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

Answer 1

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.