Welcome to WebmasterWorld Guest from 54.226.159.223

Forum Moderators: open

Message Too Old, No Replies

MySQL: which Privileges to give user?

     
12:46 am on Apr 15, 2017 (gmt 0)

Preferred Member

Top Contributors Of The Month

joined:Mar 15, 2013
posts: 570
votes: 47


For my PHP scripts, I originally set up a user with ALL PRIVILEGES. I realize now, though, that that's not really necessary, and possibly a security risk to have a site user that can drop tables and all that.

The only queries I use in scripts that require privileges are SELECT, INSERT, DELETE, and UPDATE.

So am I correct in assuming that it's safe to NOT grant the following privileges?

ALTER
ALTER ROUTINE
CREATE
CREATE ROUTINE
CREATE VIEW
DROP
EVENT
EXECUTE
REFERENCES
SHOW VIEW
TRIGGER

I have no idea what EVENT or TRIGGER do, so I'm not sure if they're used behind the scenes or what.

Also, what about these?

INDEX
CREATE TEMPORARY TABLES
LOCK TABLES

I'm assuming that certain SELECT statements might create a temporary table, or lock a table, so does the user need those? And what about INDEX... is that just the privilege to create an index, alter an index, or use an index?
6:20 am on Apr 15, 2017 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member tangor is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 29, 2005
posts:7994
votes: 578


More to the point, what specifically do you want to allow? And for who? And how will they use the database? I'd reserve delete to myself. All too easy for someone to decimate a database!
8:53 am on Apr 15, 2017 (gmt 0)

Preferred Member

Top Contributors Of The Month

joined:Mar 15, 2013
posts: 570
votes: 47


Well, this is a username that I use on all of my PHP scripts. So it's not a "who", exactly. Unless you consider the author of the scripts, which is only me (unless someone hacks in to the server, I guess).

So to my knowledge, the only commands the scripts run are SELECT, INSERT, DELETE, and UPDATE. In retrospect, though, I might not even have DELETE anywhere anymore... I think I've changed everything to change a status field instead of actually deleting rows.

Are any of those other privileges necessary for SELECT, INSERT, UPDATE, and maybe DELETE? Or are they only necessary if I've hard-coded them in to the queries?
9:43 am on Apr 15, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Sept 25, 2005
posts:1655
votes: 238


You only have to assign the privileges you explicitly use. If you don't have any queries where you manually create temporary tables, you don't need that privilege. That doesn't mean MySQL won't be able to create a temporary table in the process of executing, say, a SELECT query, if need be. INDEX is the privilege of creating indexes. See privileges as a group of commands the user is allowed to send to the MySQL server; what MySQL does when executing that query (reading from indexes, temporary tables, etc) is largely irrelevant to these permissions.

I usually create a read user and a write user, with SELECT as the only permission for the former and SELECT, UPDATE, DELETE and INSERT for the latter, and then I assign all queries to the appropriate user. I have separate includes() (or other logic) for each user to avoid opening two MySQL connections for every page load as much as possible.
7:50 pm on Apr 15, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 19, 2002
posts:3353
votes: 39


if you are looking at it from a security angle - although nothing is perfect - i generally only write stored procedures, the 'web user' is given EXECUTE permission only.
i then call the stored procedures from the php script, additionally i only use php prepared statements with parameters. any input from the webinterface be it from a form or from the url itself is cleaned rigorously and as precisely as possible, eg. if i expect a string as a parameter value that can only be a-z and with a maximum length of 24 characters, then it is tested for this before being used, the stored procedure would also be set up to accept a VACHAR of maximum length 24 and so on.
at first this takes a little longer but after a while it is a time saver, especially if you call the same stored procedure in multiple places in your website, as you can tinker with it to optimise it without having to change your php scripts at all.
additionally stored procedures are saved in the server cache the first time they are used (since MySQL restarted) and thus actually are faster on subsequent uses too (actually the server cache is whatever size it is set to, and when full, memory space used the longest time ago is reused if required, so if the stored procedure isn't called very often it could get uncached)
the other advantage of stored procedures is that with one procedure you can do many database queries all with one call from php, which otherwise might have required multiple requests to the MySQL server from your php script - thus making your pages a LOT quicker.

if you don't want to go as hardcore as this, then what robzilla said is gold

You only have to assign the privileges you explicitly use


if your web application only uses SELECT statements in the frontend, only allow the 'web user' SELECT permissions, also be aware you can set permissions on a table by table basis, only allow permissions for the table sthat the 'web user' actually uses.

*** IMO even a basic website should have at least 2 MySQL users, what i call the 'web user' which is basically the user that builds the front end web pages and the 'admin user' which does admin area stuff - which only the webmaster would do. give both of these users only the permissions each needs to do its' tasks (it makes sense to have a third user, with greater permissions that maybe you use to connect to phpMyAdmin or Workbench or whatever you use to admin your DB)
8:17 am on Apr 16, 2017 (gmt 0)

Preferred Member

Top Contributors Of The Month

joined:Mar 15, 2013
posts: 570
votes: 47


The concept of a stored procedure is a new one for me, but I did a quick search and it seems like it might be right up my alley. More complicated, yes, but you had me at "pages a LOT quicker" :-)

For now, though, I'm just updating my user with the minimal privileges. As I rebuild, though, I think that stored procedures might be the way to go...
7:10 am on Apr 17, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Sept 25, 2005
posts:1655
votes: 238


...but you had me at "pages a LOT quicker" :-)

You're actually not very likely to see a performance benefit from using stored procedures; it's a popular myth that they execute faster than regular queries. From a security standpoint there's definitely an advantage, but for me that's not worth adding the extra complexity and separation of code and query.
8:15 am on Apr 17, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 19, 2002
posts:3353
votes: 39


it's a popular myth that they execute faster than regular queries.


i completely agree, in that, for instance a simple SELECT statement inside a stored procedure is no quicker than a SELECT statement called directly from a php script.

i like to use them as it keeps all my queries on the server so i can reuse them easily on different 'pages'.
for me personally i find it easier developing long and complicated queries in a tool such as Workbench (and when i'm happy with the query i just create it into a stored procedure).
when i first discovered that you could use things like conditional statements (IF,ELSEIF,ELSE) within sql scripts (and of course that is just the tip of the iceberg) i realised i could start doing some of the logic that i had previously used php to process, directly on the MySQL server itself. this saved database calls from the script and it can save some time (depending entirely on your setup).
10:28 am on Apr 17, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Sept 25, 2005
posts:1655
votes: 238


Fair enough :-) I suppose you could do the same with functions or variables in PHP, but we all have our own preferences.

I've sometimes found myself gone overboard with moving logic to MySQL and the queries would have gotten so complex that they were much slower than when I cut them up into simpler ones. Since connecting to MySQL on localhost only takes about 200 microseconds (0.2ms), the overhead of sending multiple calls is usually negligible.

[/offtopic]