A client has asked me to manage the website that I just built for them, and I have no experience in this realm.
What Database Roles/Users should I have in a new production database?
I assume that when I create a MySQL database, that there will be a “Root” user be default, correct?
What is the best approach to take to keep things secure?
I was thinking of creating a second User which would only have SELECT, UPDATE, INSERT, and DELETE rights and refer to that user in all of my PHP scripts. (That way it would be harder for a hacker to do things like “DROP TABLE”.)
You shouldn’t use the root user. The root user has admin rights in all databases. If they’re only using this particular database, whether you use the root user or a second user with admin rights only on that database may not be very different.
But if they have more than one database in that MySQL instance, just for security reasons, you should use the second user, only with rights on one particular database. In this way, if that user is hacked, it will not have access to all databases and will only be able to mess up with one.
As you say, you can remove certain rights from this second user so it doesn’t have that much power
Yes, there will only be one database ever in MySQL.
This may sound funny, but do you ever want to disable the Root user in MySQL?
And as long as I change the default password for the Root user, combined with creating a second user that will represent the one the end users connect to, is that sufficient from a security standpoint?
You may want to change its name from root to something else.
[quote=“mikey_w, post:3, topic:113706”]
Yes, there will only be one database ever in MySQL.
[/quote]That’s like saying that there will be only one table in a database.
That’s not true. MySQL is a database manager, not a database in itself. The same instance of MySQL may have more than one database.
And one server can have more than one MySQL instance.
Could I create a user called “admin”, give it all of Root’s powers, and then disable Root?
I have one website with one database for one client. I can’t imagine why I would have two databases for one website - even if MySQL Server could do that.
That being said, I was just going to originally go with “Root” and “Production”.
Can you think of a better name for the second user?
