Bug #9036 Table creation should include automatic table use privileges

• Submitted: 8 Mar 2005 1:12
• Modified: 14 Mar 2005 19:52
• Reporter: Trudy Pelzer
• Status: Not a Bug
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: 5.0.3-alpha-debug
• OS: Linux (SuSE 9.1)
• Assigned to: Jim Winstead
• CPU Architecture: Any

[8 Mar 2005 1:12] Trudy Pelzer

Description:
A user who is allowed to create a table should also
be granted, automatically, all table privileges on that
table. This is not happening: right now, if I create a 
table, I get no privileges on it. The SQL Standard says 
that the creator of a table automatically gets the all
table privileges on the table. MySQL should follow the 
Standard as regards automatic table privilege grants.

How to repeat:
-- sign on as root
mysql> select current_user;
+----------------+
| current_user   |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> grant create on t1 to me;
Query OK, 0 rows affected (0.00 sec)

-- sign on as me
mysql> select current_user;
+------+
|      |
+------+
| me@% |
+------+
1 row in set (0.00 sec)

mysql> create table t1 (col1 int not null primary key, col2 varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(10,'trudy');
ERROR 1142 (42000): INSERT command denied to user 'me'@'localhost' for table 't1'
mysql> select * from t1;
ERROR 1142 (42000): SELECT command denied to user 'me'@'localhost' for table 't1'
-- These show the wrong response. If I can create the
table, I should be allowed to use it. The SQL Standard
says the creator of a table automatically gets all
table privileges on that table (i.e. SELECT, INSERT, 
UPDATE, DELETE, REFERENCES)

[10 Mar 2005 2:03] Jim Winstead

This is, in principle, fairly easy to do. When we create a table, just check if we have all permissions (TABLE_ACLS & ~GRANT_ACL) on the table after it has been created. If not, have ourselves granted privileges for it.

In practice, it isn't that easy. The functions that handle updating the grant tables are quite tied to the structures that come along with an actual GRANT statement.

[7 Apr 2005 18:00] Trudy Pelzer

This feature request has been written up as WL#2531.