Describe the problem

Cockroach stores its user privileges with string user names. This means that a privilege on an old table may refer to a different user entity than the current name (if you say delete the user and recreate a user with the same name). Furthermore, it's probably not great that if you revoke access to a table then the user can still access it historically.

There is quite a bit of discussion on this topic in this thread here:
https://groups.google.com/a/cockroachlabs.com/g/sql-schema-team/c/L4oUTiceGY8/m/srPRdGgCAgAJ

Expected behavior

Ideally we'd not allow access to a table unless the user currently has privileges on the table. For deleted tables this probably means that we'd require that the user had permissions when the table was deleted.

Proposed Solution

In the relatively short term we should address this. One complication is being able to find the appropriate privileges for tables which have been deleted. It seems better to me to only do the right thing for non-deleted tables than to do what we do today but obvious a complete solution would be better.

Jira issue: CRDB-3999

Epic CRDB-61003