Why DDL privilege neeed with RDB$ADMIN

[Gabor Boros]

Hi All,

I try to reproduce a problem with 4.0 replication.
The log say:

... (primary) ...
Database: ...
ERROR: unsuccessful metadata update
CREATE OR ALTER VIEW ... failed
no permission for ALTER access to VIEW ...
Effective user is ...

The problem appears only if replication used and no error without it.

I try to make a simple case but don't understand why the RDB$ADMIN role
not enough for DDL operations.

A user exist with admin role:
CREATE USER USR1 PASSWORD 'pwd1' GRANT ADMIN ROLE;

Create a database, connect to it with SYSDBA then execute a DDL
statement with USR1:

Database: localhost/3052:C:\R\DB\ORIGINAL.FDB, User: SYSDBA
SQL> select sec$admin from sec$users where sec$user_name='USR1';

SEC$ADMIN
=========
<true>

SQL> set term ^;
SQL> execute block
CON> as
CON> begin
CON> execute statement 'create or alter view v1 as select 1 x from
rdb$database'
CON> with autonomous transaction
CON> as USER 'USR1'
CON> password 'pwd1'
CON> role 'rdb$admin';
CON> end^
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-CREATE OR ALTER VIEW V1 failed
-No permission for CREATE VIEW operation
-At block line: 4, col: 3

Gabor

[Gabor Boros]

2021.06.09. 15:24 keltezéssel, Gabor Boros írta:
> Hi All,
>
> I try to reproduce a problem with 4.0 replication.
> The log say:
>
> ... (primary) ...
>     Database: ...
>     ERROR: unsuccessful metadata update
>     CREATE OR ALTER VIEW ... failed
>     no permission for ALTER access to VIEW ...
>     Effective user is ...
>
> The problem appears only if replication used and no error without it.

Try again after GRANT ALTER ANY VIEW TO ...;

... (primary) ...
Database: ...
ERROR: unsuccessful metadata update
CREATE OR ALTER VIEW ... failed

no permission for ALTER access to TABLE ...
Effective user is ...

The problematic object is a view. Why alter table permission needed?

Try again after GRANT ALTER ANY VIEW TO ...; and GRANT ALTER ANY TABLE
TO ...;

No error. Is the replication engine too strict or the core engine not
strict enough? :-)

Gabor

[Mark Rotteveel]

On 09-06-2021 15:24, Gabor Boros wrote:
> I try to reproduce a problem with 4.0 replication.
> The log say:
>
> ... (primary) ...
>     Database: ...
>     ERROR: unsuccessful metadata update
>     CREATE OR ALTER VIEW ... failed
>     no permission for ALTER access to VIEW ...
>     Effective user is ...
>
> The problem appears only if replication used and no error without it.
>
> I try to make a simple case but don't understand why the RDB$ADMIN role
> not enough for DDL operations.
>
> A user exist with admin role:
> CREATE USER USR1 PASSWORD 'pwd1' GRANT ADMIN ROLE;

This statement only grants the user RDB$ADMIN in the security database.
The user doesn't have the RDB$ADMIN role in the current database, so
doesn't obtain the necessary privileges. To be able to wield RDB$ADMIN
in the current database, you must execute:

grant RDB$ADMIN to user USR1;

This must be done in each database the user needs to assume admin rights.

Mark
--
Mark Rotteveel

[Gabor Boros]

2021.06.12. 11:06 keltezéssel, Mark Rotteveel írta:
>
> This statement only grants the user RDB$ADMIN in the security database.
> The user doesn't have the RDB$ADMIN role in the current database, so
> doesn't obtain the necessary privileges. To be able to wield RDB$ADMIN
> in the current database, you must execute:
>
> grant RDB$ADMIN to user USR1;
>
> This must be done in each database the user needs to assume admin rights.
>
> Mark

Thank you very much Mark!
Now I understand the situation and can reproduce the original problem:
https://github.com/FirebirdSQL/firebird/issues/6856

Gabor