MySQL – permissions in database

  Database, Web hosting

After the establishment of MySQL database on webhosting you will receive login information for 2 users:

  • User admin (login name begins with “a“) has full rights to the database, is primarily used to manage the database through phpMyAdmin – creating tables, indexes setting, etc.. This user should not be used to connect to MySQL from PHP.
  • User web (login name begins with “w”) has limited permissions, allowed to work with the data, but not allowed to change the structure of the tables. Use this user to connect to the database from PHP.

Each database has fixed these 2 users. Other users can not be created or assigned different permissions to them. It is also not possible to access multiple databases by using a single user.

Particular user permissions for admin:

  • Select
  • Insert
  • Update
  • Delete
  • Create
  • Drop
  • Index
  • Alter
  • Create_tmp_table
  • Lock_tables
  • Create_view
  • Show_view
  • Create_routine
  • Alter_routine
  • Execute
  • Trigger

Particular user permissions for web:

  • Select
  • Insert
  • Update
  • Delete
  • Create_tmp_table
  • Lock_tables
  • Execute

What is this separation good for? in the case when someone will attack your site for example via SQL Inject, it can be extremely difficult for the attacker in this situation by using a user with the least permissions.

Careful, however, when you install some content management systems to your webhosting. You must use in the installation wizard user admin, because the user web does not have the rights to create database tables. After installation is complete, change the configuration of CMS user to web.

Byl pro Vás tento návod užitečný?