1

As a PHP developer I must set privileges for each database user on my web hosting panel. I read some articles like which privileges should be set for website database user / client in mysql in Stackoverflow.com, but there are something missed in it I think.

This is what I have to set in my web hosting panel:

enter image description here

I know about SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,GRANT,CREATE TMP TABLES. But I need to know do I must set or unset the privileges for INDEX,REFERENCE, and LOCK TABLES.

What are these 3 last privileges for?

Community
  • 1
  • 1
Mohammad Saberi
  • 12,864
  • 27
  • 75
  • 127
  • Any reason you couldn't just google those? https://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html#priv_index https://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html#priv_references https://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html#priv_lock-tables – Evan Trimboli Nov 18 '12 at 09:04
  • could you tell me please that do I must enable lock tables privilege always or not? Is it related with MySQL engines like MyISAM or innoDB ? – Mohammad Saberi Nov 18 '12 at 09:27

1 Answers1

2

Information on all the privileges can be found in one lengthy MySQL documentation page. Here are the three you asked about:

6.2.2 Privileges Provided by MySQL

INDEX

Enables use of statements that create or drop (remove) indexes. INDEX applies to existing tables. If you have the CREATE privilege for a table, you can include index definitions in the CREATE TABLE statement.

REFERENCES

Creation of a foreign key constraint requires the REFERENCES privilege for the parent table.

LOCK TABLES

Enables use of explicit LOCK TABLES statements to lock tables for which you have the SELECT privilege. This includes use of write locks, which prevents other sessions from reading the locked table.

TLDR:

  • INDEX allows adding/removing indexes on existing tables.
  • REFERENCES allows creating foreign key constraints on a table.
  • LOCK TABLES allows acquiring locks on tables to prevent other sessions from accessing them in particular ways, such as reading or writing.
mwfearnley
  • 3,303
  • 2
  • 34
  • 35