User/Role are identified by unique id

[eiDear]

Cockroach v22.1.5 CCL

There are two terminal or clients connected to cockroach server. Terminal 1(named as T1): create database, table, user and control privileges. Terminal 2 (named as T2): execure query on object.

# Terminal 1
root@:26257/defaultdb> create database db1;
CREATE DATABASE


Time: 139ms total (execution 138ms / network 1ms)

root@:26257/defaultdb> create user u1 login;
CREATE ROLE


Time: 371ms total (execution 371ms / network 371ms)

root@:26257/defaultdb> create table tb1(id int primary key, ts timestamp);
CREATE TABLE


Time: 94ms total (execution 93ms / network 1ms)

root@:26257/defaultdb> create table tb2(id int primary key, ts timestamp);
CREATE TABLE


Time: 71ms total (execution 70ms / network 1ms)

root@:26257/defaultdb> grant insert on table tb1 to u1;
GRANT

Time: 245ms total (execution 245ms / network 1ms)

And then, u1 login in Terminal 2:

# Terminal 2

u1@:26257/defaultdb> insert into tb1 values(1,now());
INSERT 1


Time: 68ms total (execution 67ms / network 1ms)

u1@:26257/defaultdb> insert into tb2 values(1,now());
ERROR: user u1 does not have INSERT privilege on relation tb2
SQLSTATE: 42501

Now, I revoke grants and drop USER u1 in Terminal 1, but DO NOT disconnect in Terminal 2. The Terminal 2 is always keeping the connection.

# Termunal 1
root@:26257/defaultdb> revoke insert on table tb1 from u1;
REVOKE


Time: 247ms total (execution 246ms / network 1ms)

root@:26257/defaultdb> drop user u1;
DROP ROLE


Time: 325ms total (execution 324ms / network 1ms)

root@:26257/defaultdb> create user u1 login;
CREATE ROLE


Time: 338ms total (execution 337ms / network 1ms)

root@:26257/defaultdb> grant insert on table tb2 to u1;
GRANT


Time: 246ms total (execution 246ms / network 1ms)

And magic things occured! The USER u1 in Logged in Terminal2 now have the privilege to insert data into table tb2.

# Terminal 2
u1@:26257/defaultdb> insert into tb1 values(1,now());
ERROR: user u1 does not have INSERT privilege on relation tb1
SQLSTATE: 42501
u1@:26257/defaultdb> insert into tb2 values(1,now());
INSERT 1


Time: 20ms total (execution 20ms / network 20ms)

The USER u1 created in Terminal 1 secondly is different from the USER u1 created in Terminal 1 first. BUT, actually they seem/are the same in cockroach. This will make customers confuse.

To avoid this problem, to add USER ID in system.users. USER IDs are different among users even they have the save name when they're DROPPED AND re-CREATED. Doing so can gurantee every user created is unique.

A suggestion is : Adding userid/roleid field to system.users.

Jira issue: CRDB-18227

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[ajwerner]

The good news is that we're very actively working on this. See https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/20220307_user_ids.md for the design doc and #81457 for a remaining PR in the implementation.

Closing as a duplicate of #78963.

[eiDear]

OK