1

So what privileges should be set for the mysql user that is to be used as 'main' user for the website?

I'm thinking data and structure ( see below ) for the database that is used should be set. But do i need to add administration rights also? And would this be any different if you own a web server and grant privileges to a client that for example want you to host an website for them giving them rights to use phpmyadmin on there own databases.

Data

SELECT
INSERT
UPDATE
DELETE
FILE

Structure

CREATE
ALTER
INDEX
DROP
CREATE TEMPORARY TABLES
SHOW VIEW
CREATE ROUTINE
ALTER ROUTINE
EXECUTE
CREATE VIEW
EVENT
TRIGGER

Administration

GRANT
SUPER
PROCESS
RELOAD
SHUTDOWN
SHOW DATABASES
LOCK TABLES
REFERENCES
REPLICATION CLIENT
REPLICATION SLAVE
CREATE USER
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
FLY
  • 2,379
  • 5
  • 29
  • 40

1 Answers1

9

For a website user (that your application uses to connect to your database) simple SELECT, INSERT, UPDATE, DELETE rights should be enough. In most situations, your website should not really modify the structure of your tables (that might indicate bad database design). You only want to deal with data here.

If you are talking about a user that also needs to modify structural things, design and create tables, like one used for phpMyAdmin, you certainly need the Data and Structure rights. No Administration I'd say.

kapa
  • 77,694
  • 21
  • 158
  • 175
  • 1
    So basically 'data' is for the 'application' (web) user, 'structure' for the webmaster and 'administration' for the server administrator? – FLY Jan 27 '12 at 10:03
  • @scubaFLY In most situations, I think yes. – kapa Jan 27 '12 at 11:47
  • 2
    You might want also CREATE TEMPORARY TABLES and EXECUTE permission for more complex queries and stored procedures. – JustAMartin Sep 07 '17 at 07:28