The revoke statement fails [CORE5523]

[firebird-automations]

Submitted by: Vicente Tejero Trueba (vicentett)

Attachments: elmadb.gdb

I'm a user SYSDBA. I have a table Concursantes and a role Alumnos. I grant the select privileges on Concursantes to Alumnos.

When I execute the statement revoke SELECT ON CONCURSANTES from ALUMNOS

Fails with message SQL Message : -607 This operation is not defined for system tables.

Engine Code : 335544351 Engine Message : unsuccessful metadata update REVOKE failed SYSDBA is not grantor of Select on CONCURSANTES to ALUMNOS.

[firebird-automations]

Commented by: @romansimakov

Could you prepare a full script to reproduce the bug?

[firebird-automations]

Commented by: @AlexPeshkoff

Did not reproduce - please provide full reproducible case:

⁠ ./isql -user sysdba employee

Database: employee, User: SYSDBA SQL> create table Concursantes(x int); SQL> create role Alumnos;
SQL> grant select on Concursantes to Alumnos; SQL> show role Alumnos; Role ALUMNOS isn't granted to anyone. SQL> show grant;

/* Grant permissions for this database */ GRANT SELECT ON CONCURSANTES TO ROLE ALUMNOS GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON COUNTRY TO PUBLIC WITH GRANT OPTION ... GRANT EXECUTE ON PROCEDURE SUB_TOT_BUDGET TO PUBLIC WITH GRANT OPTION SQL> revoke SELECT ON CONCURSANTES from ALUMNOS; SQL> show grant;

/* Grant permissions for this database */ GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON COUNTRY TO PUBLIC WITH GRANT OPTION ..... GRANT EXECUTE ON PROCEDURE SUB_TOT_BUDGET TO PUBLIC WITH GRANT OPTION SQL>

[firebird-automations]

Commented by: Ján Kolár (kolar_appliedp.com)

I have the same problem but with older database 2.5.8 and ODS=10. Maybe you will find clue also for Firebird 3. I have problem revoking delete privilege on table PROFILE_DLMS (in attachment) even when I am connected as SYSDBA. Operation fails with following error message:

Starting transaction... Preparing query: REVOKE DELETE ON PROFILE_DLMS FROM TECHNICIAN Prepare time: 0.219s Plan not available.

Executing... Error: *** IBPP::SQLException *** Context: Statement::Execute( REVOKE DELETE ON PROFILE_DLMS FROM TECHNICIAN ) Message: isc_dsql_execute2 failed

SQL Message : -607 This operation is not defined for system tables.

Engine Code : 335544351 Engine Message : unsuccessful metadata update SYSDBA is not grantor of Delete on PROFILE_DLMS to TECHNICIAN.

Total execution time: 0.224s

[firebird-automations]

Modified by: Ján Kolár (kolar_appliedp.com)

Attachment: elmadb\.gdb \[ 13369 \]

[firebird-automations]

Commented by: Ján Kolár (kolar_appliedp.com)

The reason is probably that Firebird does not handle correctly user names. 'sysdba' is sometimes different than 'SYSDBA'. This is excerpt from my RDB$PRIVILEGES table. If the name of grantor is stored with lower case, i cant use SQL revoke command. There is no problem with manually deleting from RDB$PRIVILEGES table appropriate record.

TECHNICIAN SYSDBA S [null] LIMIT [null] 8 0 00000012:000007b6 TECHNICIAN SYSDBA I [null] LIMIT [null] 8 0 00000012:000007b7 TECHNICIAN SYSDBA U [null] LIMIT [null] 8 0 00000012:000007b8 TECHNICIAN SYSDBA D 0 LIMIT [null] 8 0 00000012:000007b9 TECHNICIAN SYSDBA R [null] LIMIT [null] 8 0 00000012:000007ba TECHNICIAN sysdba I 0 PROFILE_DLMS [null] 8 0 00000012:000015ca TECHNICIAN sysdba R 0 PROFILE_DLMS [null] 8 0 00000012:00001637 TECHNICIAN sysdba S 0 PROFILE_DLMS [null] 8 0 00000012:00001675 TECHNICIAN sysdba U 0 PROFILE_DLMS [null] 8 0 00000012:000016e3 TECHNICIAN sysdba D 0 PROFILE_DLMS [null] 8 0 00000012:000019d8