MySQL Forums
Forum List  »  PHP

Re: Db access/privileges philosophy
Posted by: Federico Marziali
Date: November 21, 2011 01:01AM

Hi there,

thanks for spending some time replying to a newbie..

> > secure to manage access to the database for a
> web application
> 1. The users should not access the database
> directly. All queries should go through an API.
Not sure what you mean here with "go through an API". Basically, beside various queries which are "hard-coded" in the PHP code, only searches in the database are allowed, and I make sure that everything the user inputs gets quoted, to avoid the improper execution of SQL statements...

> 2. Learn about "Sql injection".
Yes, I've done this part of the homework :). And, as I mention above, I enclose in appropriate quotes all user inputs and all parameters as passed by PHP...

> 3. Learn about "cross-site scripting".
Ups, this bit of home work is still missing... :)

> 4. Validate all input from s. (is_numeric(),
> etc)
> 5. Escape everything used in building queries.
> (mysql_real_escape_string(), etc)
Points 4 and 5 are related to point 2, correct?

> 6. Escape everything that came from the user or
> the database and is being displayed.
> (htmlentities(), etc)
This is also related to point 2, correct?

> [CUT]
> > The connection to the database would always take
> place as "root".
> NO! NO!
> If a hacker gets in, that's the end of your
> database, and possibly your system.
> Even DOS (Denial Of Service) will be harder to
> deal with -- root (SUPER) is privileged to get one
> extra login beyond max_connections. You (the
> admin) need to keep that in reserve in case a
> hacker ties up all the connections and you need to
> get in to see what is going on and kill threads.
Ok, I admit I can't follow you here clearly: if a hacker gets in... where?
Do you mean if (s)he gets access to the server? Anyhow the connection to SQL through PHP always implies that the password to access the database is stored as plain text in the code (maybe outside the root of the website, but if somebody has access to the server it won't matter where that is)...
And having access to the server, the hacker can just delete everything, without even needing root access. Or just make a copy of the db and then try to break into it at a later stage... am I missing something here?

> GRANT ... ON dbname.* TO user@'11.22.33.44'
> IDENTIFIED BY '...'
> * Limit all your application logins to dbname.*,
> so they can't get to mysql.*, etc.
Ok

> * Limit your logins to coming from an IP address
> (or localhost), not '%'.
Can't do that unfortunately... the users are not all in house...

> [CUT]
> > the privileges cannot be "limited" to some set
> of tables only
> They can:
> GRANT ... ON dbname.tblname ...,
> But, as I say, don't depend on that; use an
> abstraction layer in your application.
Ok, so I've got to re-read the manual :)

Anyhow it seems to me that the bottom-line is: limit access to your db via MySQL mechanism as much as possible (i.e. so far you can push it without removing functionalities you need) and ALSO implement these limitations - and eventually a finer set of rules - in the PHP application.

Thanks for all your input, it's very much appreciated.

Fede

Options: ReplyQuote


Subject
Written By
Posted
Re: Db access/privileges philosophy
November 21, 2011 01:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.