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
test_user
.create user test_user identified by ''
Then I gave grant to
test_user
for creating new tables on whole database "testing" which already existsgrant CREATE on testing.* to test_user
Then I logged into
test_user
accountmysql -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 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?