Why DDL privilege neeed with RDB$ADMIN

28 views
Skip to first unread message

Gabor Boros

unread,
Jun 9, 2021, 9:24:30 AMJun 9
to firebird...@googlegroups.com
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

unread,
Jun 10, 2021, 6:02:08 AMJun 10
to firebird...@googlegroups.com
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

unread,
Jun 12, 2021, 5:06:11 AMJun 12
to firebird...@googlegroups.com
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

unread,
Jun 15, 2021, 5:36:51 AMJun 15
to firebird...@googlegroups.com
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
Reply all
Reply to author
Forward
0 new messages