0

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.