Creator of a MySQL table automatically granted all table permissions?

Question

I recently read it in SilberSchatz book that in MySQL the creator of a relation/view is the owner and granted all permissions on the relation/element including the power to transfer these grants to other users.

I tested this on MySQL myself:

1. Logged in root account

   mysql -u root -p
   

2. I created a new user test_user.

   create user test_user identified by ''
   

3. Then I gave grant to test_user for creating new tables on whole database "testing" which already exists

   grant CREATE on testing.* to test_user
   

4. Then I logged into test_user account

   mysql -u test_user -p
   

5. I created a new table TABLE1

   create table table1(id int auto_increment,name varchar(20), primary key (id));
   

6. But when I insert data into this table it is giving error.

   insert into table1 values(1,'alankrit');
   

   ERROR : INSERT command denied to user 'test_user' for table 'table1'

When I do give test_user INSERT grant from root then I can insert data into table1.

The question is that test_user is creating table TABLE1 so why is he not getting INSERT grant on this table on its own as emphasised in this book.

Secondly, do we need to ask root to grant us permissions on everything we create on our own? Wouldn't this be problematic? How will this work?

Answer 1

Seems Silber Schatz doesn't have it fully explained,

Because CREATE doesn't implicitly imply INSERT.

For a database level grant ALL is usually used.