MySQL Bugs: #101593: Permissions Survive Past The Lifetime of Objects They Were Granted On

Bug #101593	Permissions Survive Past The Lifetime of Objects They Were Granted On
Submitted:	12 Nov 2020 21:01	Modified:	13 Nov 2020 16:33
Reporter:	Stanislav Revin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Orphan permissions pose a security risk. An orphan permission can be described as a permission whose lifetime exceeds the lifetime of the object it was granted on.

How to repeat:
-- Create some table
CREATE TABLE TestTable (id int);

-- Grant select permission to a user who you think should have an access to it
GRANT SELECT ON TestTable To SomeUser@localhost;

-- Remove the table
DROP TABLE TestTable;

-- Create another instance of TestTable for a different confidential purpose intended for completely other users. SomeUser@localhost is not supposed to see it.

CREATE TABLE TestTable (name varhchar(16));

-- However, SomeUser@localhost still can select from TestTable and read its contents because the lifetime of a permission on the object exceeds the lifetime of the object itself.

Suggested fix:
Drop all permissions on the object when the object is dropped to avoid having orphan permissions that bind to the name of the object.

Hi Mr. Revin,

Thank you very much for your bug report, which is not a bug but feature request, at best.

However, we do not see how could we implement this feature request without breaking millions of applications. Namely, so many applications have granted permissions for some table to many users, sometimes hundreds of them. Now, many of these applications are designed so that a table is dropped, created again and filled up with data. Also, there are many installations were tables are defragmented by dropping them, creating them and restoring them with data.

All those applications and installations would be broken if we would introduce your feature request.

Hence, we do not think that this is doable, without huge side effects, but we shall leave this decision to people that are in charge of making such decisions.

Verified as a feature request.

Yeah, it's up to you what to do with this information.
Another point to keep in mind is that I have tested the same scenario with database permissions.

For example:

CREATE DATABASE test_db_perms;
use test_db_perms;
CREATE TABLE test_table (id int);
-- ... could create and populate more tables here
use mysql;

CREATE USER lucky_user IDENTIFIED BY 'password';
GRANT SELECT ON test_db_perms.* TO lucky_user;

-- some time goes by... Company decided to remove database test_db_perms
DROP DATABASE test_db_perms;

-- some time goes by.. Company decides to repurpose the name test_db_perms
CREATE DATABASE test_db_perms;
use test_db_perms;
CREATE TABLE confidential (ssn varchar(11));
INSERT INTO confidential (ssn) VALUES ('123-45-6789');

-- at this point lucky_user has access to select from confidential table in test_db_perms, which was not originally intended

What if the behavior to drop permissions from users when underlying objects are deleted were included in some type of a strict (or secure) MySQL server mode such that users make a conscious decision when the opt for using this behavior as a feature in their applications? This way it won't necessarily have to break existing applications since their administrators will have to enable the secure mode manually.

Thank you, Mr. Revin.