Large amount of unnecessary privileges in RDB$USER_PRIVILEGES for SYSDBA

[usystem]

In a small database with 40 tables in Firebird 3:

SELECT 
    RDB$PRIVILEGE,
    RDB$GRANT_OPTION,
    COUNT(*) as cnt
FROM RDB$USER_PRIVILEGES 
WHERE RDB$USER = 'SYSDBA'
  AND RDB$OBJECT_TYPE = 9
GROUP BY RDB$PRIVILEGE, RDB$GRANT_OPTION
ORDER BY cnt DESC;

Result:

G   1   327,000 records.

What is this and how to fix it? Has this been addressed somewhere? What is this?

The G privilege with GRANT_OPTION = 1 means "WITH GRANT OPTION" - permission to grant these privileges to other users. Having 327,000 such records for SYSDBA is completely abnormal and redundant because:

• SYSDBA already has all privileges by default
• SYSDBA can grant any privileges anyway
• This is system-level bloat that slows down backups

[mrotteveel]

SYSDBA has all those rights because it's recorded in the database.

[usystem]

327000 on 40 tables yes! Of course! How could I have missed that I needed hundreds of thousands of permissions!

[aafemt]

SYSDBA has all those rights because it's recorded in the database.

No, its name is hardcoded in sources, so any explicit privileges are redundant. But in this case, I suspect, SYSDBA is also a database owner and an owner of every object inside of the database. That makes difference.

But back to the topic: table RDB$PRIVILEGIES is not used by engine, it exists only for user reference so bloating of it is mostly harmless.

Besides, Firebird3 is approaching to end-of-life, so hardly can anybody care about fixing such little thing.

[mrotteveel]

RDB$OBJECT_TYPE 9 are domains. And, BTW, G is the usage privilege (that is, SYSDBA has the privilege to use those domains, and, given WITH_GRANT_OPTION is 1, may also grant usage to others). This suggest you have 327000 columns with implicit domains in those 40 tables (though 8175 columns per table on average sounds a bit high to me).

@aafemt RDB$USER_PRIVILEGES is used by the engine. Unless you meant to say "not used for SYSDBA", which I'm actually not sure about (I find the permission check code rather hard to follow).

[aafemt]

Yes, it is used, but mostly during DDL and not real permission checks.

The only exception is a relatively new usage of it for nested role check (using quite ugly and ineffective SQL query).

[mrotteveel]

Besides, Firebird3 is approaching to end-of-life, so hardly can anybody care about fixing such little thing.

@aafemt Though, Firebird 4 and Firebird 5 do the same thing.

[AlexPeshkoff]

Mark, RDB$USER_PRIVILEGES is certainly used by the engine to build correct security classes but not for access rights checks directly. Access rights are checked using that security classes.

@usystem Can you check how many records contain RDB$FIELDS & RDB$RELATION_FIELDS tables? There was an old bug related, may be it's still present in fb3.

[usystem]

SELECT COUNT(*) as rdb_fields_count
FROM RDB$FIELDS; - 479

SELECT COUNT(*) as rdb_relation_fields_count
FROM RDB$RELATION_FIELDS; - 753

SELECT COUNT(*) as user_rdb_fields_count
FROM RDB$FIELDS
WHERE RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0; - 329

SELECT COUNT(*) as user_rdb_relation_fields_count
FROM RDB$RELATION_FIELDS rf
JOIN RDB$RELATIONS r ON rf.RDB$RELATION_NAME = r.RDB$RELATION_NAME
WHERE r.RDB$SYSTEM_FLAG = 0; - 295

SELECT
RDB$FIELD_NAME,
COUNT() as duplicate_count
FROM RDB$FIELDS
WHERE RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0
GROUP BY RDB$FIELD_NAME
HAVING COUNT() > 1
ORDER BY duplicate_count DESC; - 0

SELECT
COUNT(DISTINCT p.RDB$FIELD_NAME) as unique_fields_in_privileges,
COUNT(DISTINCT f.RDB$FIELD_NAME) as unique_fields_in_rdb_fields
FROM RDB$USER_PRIVILEGES p
LEFT JOIN RDB$FIELDS f ON p.RDB$FIELD_NAME = f.RDB$FIELD_NAME
WHERE p.RDB$USER = 'SYSDBA'
AND p.RDB$OBJECT_TYPE = 9
AND p.RDB$FIELD_NAME IS NOT NULL; - 0

SELECT
RDB$FIELD_NAME,
RDB$COMPUTED_SOURCE,
RDB$DEFAULT_SOURCE,
COUNT() as occurrences
FROM RDB$FIELDS
WHERE (RDB$COMPUTED_SOURCE IS NOT NULL
OR RDB$DEFAULT_SOURCE IS NOT NULL)
AND (RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0)
GROUP BY RDB$FIELD_NAME, RDB$COMPUTED_SOURCE, RDB$DEFAULT_SOURCE
HAVING COUNT() > 1; - 0

Данные по базе

[AlexPeshkoff]

Looks like I know the reason - please try:
select count(*) from rdb$user_privileges where RDB$OBJECT_TYPE = 9 and RDB$USER = 'SYSDBA' and RDB$RELATION_NAME not in (select RDB$FIELD_NAME from rdb$fields);

[usystem]

select count(*) from rdb$user_privileges where RDB$OBJECT_TYPE = 9 and RDB$USER = 'SYSDBA' and RDB$RELATION_NAME not in (select RDB$FIELD_NAME from rdb$fields); - 325153

[AlexPeshkoff]

Yep, rdb$user_privileges is not cleared when record removed from rdb$fields. Bug remains in current HEAD too.

[usystem]

Well, that's exactly why I came here! Hope you'll fix it. I don't expect fixes in FB3 though). Would be nice to have it fixed in the current version).