Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Operator REVOKE can modify rights granted to system tables at DB creation time [CORE4980] #5271

Closed
firebird-automations opened this issue Oct 27, 2015 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @AlexPeshkoff

Some forms of SQL operator REVOKE can trash access rights to system tables. For example:

REVOKE ALL ON ALL FROM <DB-owner>
REVOKE ALL ON ALL FROM PUBLIC
REVOKE SELECT ON RDB$RELATIONS FROM PUBLIC

As a result it's very easy to have a database from which none can read (for example) list of tables.

Commits: 2e52275 e261773 ea49fca FirebirdSQL/fbt-repository@c13087b FirebirdSQL/fbt-repository@cd9c62e FirebirdSQL/fbt-repository@1395455

====== Test Details ======

NOTE!
NON-privileged user is created and used for verifying this ticket issues (rather than <db_owner> as is was specified in the source DDL expression: "REVOKE ALL ON ALL FROM <DB-owner> "). With manipulation against privileges of DB owner one may NOT to see effect of fix: seems that one may not to revoke privileges from him (letter from Alex 30-oct-2015 13:11).

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

Isn't it exactly what users ask for from time to time? I mean metadata security by obscurity.

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

The most correct solution would be to add RDB$SYSTEM_FLAG field to RDB$USER_PRIVILEGES table. But in order to not change ODS after RC1 it was decided to use RDB$GRANTOR field (set it to NULL) to mark rights granted to system objects. This solves a problem by checking for NULL field value when trying to drop records from privileges table.

Also modified gbak code in order to never backup/restore rights granted to system objects.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.0 [ 10048 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Security by obscurity?
May be...
But we have already added one hack (ability to NULLify source of procedure/trigger/etc) and I hope it's enough for them.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> As a result it's very easy to have a database from which none can read (for example) list of tables.

I could NOT reproduce this on Beta2 release:

C:\MIX\firebird\QA\fbt-repo\tmp>C:\MIX\firebird\oldfb30b2\isql.exe /3002:C:\MIX\firebird\QA\fbt-repo\tmp\E30B2.fdb
Database: /3002:C:\MIX\firebird\QA\fbt-repo\tmp\E30B2.fdb
SQL> show version;
ISQL Version: WI-T3.0.0.31896 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31896 Firebird 3.0 Beta 2"
. . .
SQL> create or alter user admin2 password '123';
SQL> grant create database to user admin2;
SQL> commit;
SQL> create database '/3002:c:\temp\tmp4admin2.fdb' user 'ADMIN2' password '123';
SQL> show database;
Database: /3002:c:\temp\tmp4admin2.fdb
Owner: ADMIN2
. . .
SQL> select current_user from rdb$database;

USER

ADMIN2

SQL> commit;
SQL> connect '/3002:c:\temp\tmp4admin2.fdb' user 'SYSDBA' password 'masterke';
Database: '/3002:c:\temp\tmp4admin2.fdb', User: 'SYSDBA'
SQL> show database;
Database: /3002:c:\temp\tmp4admin2.fdb
Owner: ADMIN2
. . .
SQL> revoke all on all from admin2;
SQL> revoke all on all from public;
Warning: ALL on ALL is not granted to PUBLIC.
SQL> REVOKE SELECT ON RDB$RELATIONS FROM PUBLIC;
Warning: Select on RDB$RELATIONS is not granted to PUBLIC.
SQL> commit;
SQL> select rdb$relation_name from rdb$relations rows 3;

RDB$RELATION_NAME

RDB$PAGES
RDB$DATABASE
RDB$FIELDS

SQL> commit;
SQL> connect '/3002:c:\temp\tmp4admin2.fdb' user 'ADMIN2' password '123';
Use CONNECT or CREATE DATABASE to specify a database
Database: '/3002:c:\temp\tmp4admin2.fdb', User: 'ADMIN2'
SQL> set count on;
SQL> select current_user, rdb$relation_name from rdb$relations rows 3; --------------------------------- THIS SHOULD FAIL ?

USER RDB$RELATION_NAME
=============================== ===============================
ADMIN2 RDB$PAGES
ADMIN2 RDB$DATABASE
ADMIN2 RDB$FIELDS

Records affected: 3 -------------------------------------------------------------------------------------- BUT IT PASSED OK.

SQL> show grants;
There is no privilege granted in this database

So, what I've missed ? How to made test for this ticket ?

PS.

C:\MIX\firebird\oldfb30b2>findstr /r /c:"^[^#⁠;]" firebird.conf | sort
AuthClient = Legacy_Auth,Srp,Win_Sspi
AuthServer = Legacy_Auth,Srp
BugcheckAbort = 1
DefaultDbCachePages = 8192
FileSystemCacheThreshold = 65536K
LockHashSlots = 22111
MaxUnflushedWrites = -1
MaxUnflushedWriteTime = -1
MaxUserTraceLogSize = 99999
RemoteServicePort = 3002
TempCacheLimit = 2147483647
TempDirectories = c:\temp
UserManager = Legacy_UserManager
WireCrypt = Disabled

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

Test Details: NOTE!
NON-privileged user is created and used for verifying this ticket issues (rather than <db_owner> as is was specified in the source DDL expression: "REVOKE ALL ON ALL FROM <DB-owner> "). With manipulation against privileges of DB owner one may NOT to see effect of fix: seems that one may not to revoke privileges from him (letter from Alex 30-oct-2015 13:11).

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants