Database Exercises
User management

    User management is quite easy to handle with phpMyAdmin or with MySQLWorkbench, but it is useful to know also how to do it with command-line. Check the instructions from mysql-section

    But it might be good idea to use MySQLWorkbench to check what happens, when you make the steps in this exercise. So in Workbench open Server->Users and Privileges

    In MySQLServer all the user informations are in database named mysql. That database has an table named user and that table has an field named User. So you can allways check which users do you have with commad 

    	SELECT User FROM mysql.user;
    And if you want to check also the hosts where they are able to connect, you can use command 
    	SELECT User,Host FROM mysql.user;
    You can handle the user's privileges with GRANT-commands. Check the syntax of GRANT from http://dev.mysql.com/doc/refman/5.1/en/grant.html and then make the exercises

    • Start UniServerController and MySQL-server and open MySQL-console
    • Check that you have databases project and library. If you havent project, so create it (you can find the codes from mainpage). And if you don't have database library you have to make the exercise2 first.
  1. Create user named "testUser" which has password is testPass
  2. Add to testUser SELECT-privileges to all tables in database project, if connect from localhost
  3. Open ServerConsole and login with testUser
  4. As a testUser check which databases you can see
  5. As a testUser execute some SELECT queries
  6. As a testUser try to INSERT new person to person-table
  7. As a root add INSERT-privilege to testUser to project-database tables, from localhost
  8. As a testUser try to INSERT new person to person-table (perhaps you have to logout and login again)
  9. As a testUser try to DELETE the "new person" from person-table
  10. As a root add DELETE-privilege to testUser to project-database's person-table, from localhost
  11. As a testUser try again to DELETE the "new person" from person-table
  12. As a root remove SELECT privilege from testUser to database project
  13. As a testUser check which databases you can see
  14. As a testUser logout
  15. Check from http://dev.mysql.com/doc/refman/5.0/en/drop-user.html how DROP USER command works
  16. As a root remove the testUser and check that the user doesn't exist
  17. Try to login as a testUser



Toggle Menu

Copyright © Pekka Alaluukas / 2024
email