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:
Logged in root account
mysql -u root -p
I created a new user
create user test_user identified by ''
Then I gave grant to
test_userfor creating new tables on whole database "testing" which already exists
grant CREATE on testing.* to test_user
Then I logged into
mysql -u test_user -p
I created a new table TABLE1
create table table1(id int auto_increment,name varchar(20), primary key (id));
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
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?