[solved]Confused about databases and users

Problems with the Windows version of XAMPP, questions, comments, and anything related.

[solved]Confused about databases and users

Postby Sorensen84 » 27. June 2012 22:59

Hello people

Im so lost. I do not know how to configure a database and a user the best way. I can see there is a lot of ways to create a user an a database. But what is the best way to do it?

I have plans on doing a lot of different websites, and I would like to have a different user to each database with a password. Is that a good way to do it?

Lets say I am making a new website.

So I go into Xampp and will create a new user.

1. Shall I first create the user or the database and then create a new database?

2. You can also first create the database, and then go into your new database and choose privileges and add a new user. Then you can choose "Grant all privileges on database "your database" But If you create the user first, shall I then choose this option "Grant all privileges on wildcard name (username\_%)" But what is the best way to do it? Create the database or the user first?

3. You can also go into your database and see the users that can use the database and under "edit privileges" there is a category named "Database-specific privileges" where there is this option "Add privileges on the following database" where you can choose a database. What does that do?

4. When i choose one of my databases and choose the option "privileges" then I can see, that all my users have access to the database. I just want to have one specific user to each database. I don't want all my users to have access to all of my databases. How do I that?


Like you can hear, im very confused about all these ways to make users and databases. But I just want a separate database and user to each of my websites. So I easily can upload the database in Xampp to my webhosting company, when it is done. Will you please help me and tell me the best way to make a database with one user to each of my websites.
Sorensen84
 
Posts: 7
Joined: 27. June 2012 21:49
Operating System: Windows 7 64-bit

Re: Confused about databases and users

Postby Altrea » 28. June 2012 04:03

Hi Sorensen84,

Sorensen84 wrote:I can see there is a lot of ways to create a user an a database.

yes, there are, but only because phpmyadmin tries to give you all possibilities you maybe need some day :D

Sorensen84 wrote:But what is the best way to do it?

That depends on the requirement.


Sorensen84 wrote:I have plans on doing a lot of different websites, and I would like to have a different user to each database with a password. Is that a good way to do it?

Thats a brilliant way of doing it.

Sorensen84 wrote:So I go into Xampp and will create a new user.

you mean phpmyadmin i hope.

Sorensen84 wrote:1. Shall I first create the user or the database and then create a new database?

In most cases the fastest way is to create the new user first and choose to automatically create a new database with the user with full usage permissions.
If you want to get a little bit more of security, you could create the user separately with a username independend from the database name.
This user shouldn't get any global permissions but just needed database related permissions without grant.

Sorensen84 wrote:2. [...] But If you create the user first, shall I then choose this option "Grant all privileges on wildcard name (username\_%)"

I wouldn't use that option because i don't name databases related on usernames, but usernames on databasenames.
And if i would create databases like joomla_live and joomla_dev a user joomla would have permissions to both databases and all databases i would create later with joomla_.

Sorensen84 wrote:But what is the best way to do it? Create the database or the user first?

Thats up to you. I create the user first, because the database is more or less uncritical in security concerns.

Sorensen84 wrote:3. You can also go into your database and see the users that can use the database and under "edit privileges" there is a category named "Database-specific privileges" where there is this option "Add privileges on the following database" where you can choose a database. What does that do?

Thats the option i choose most. A user should never get global (related to ALL databases) priviledges if you not really need that.
Always permit database or table specific permissions.

Sorensen84 wrote:4. When i choose one of my databases and choose the option "privileges" then I can see, that all my users have access to the database. I just want to have one specific user to each database. I don't want all my users to have access to all of my databases. How do I that?

see question/answer 3. database specific permissions.

best wishes,
Altrea
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8293
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Confused about databases and users

Postby Sorensen84 » 28. June 2012 18:52

Hey Altrea

Many thanks for the answer :)
I just have a little bit more questions, I hope you can help me with

1. When I go into my databases and choose privileges, all my users have access to all my databases. Is that because they're global? How do I change that? I just want one user to each database.

2. You are saying I should not grant all privileges to my user. But I would still like to have full control over everything. What did you do?

3. Is this a good way to do it:
I first make the database, then go into the database I have created and choose privileges and add a new user. Here I can make a password for the new user, and there is and option called "Database for user" Shall I then choose "Grant all privileges on database"? Do I then have full control over the database without it being global?
Under that there is another option called "Global privileges (Check All / Uncheck All) " What shall I choose there? I don't want all of my users to have access to this database, only the user i create for the database. That means it should not be global, right? Because if it is global, that user will have access to all the further databases I am making, is that right? And I don't want that. That user should only have access to the specific database for that user.

4. What does that global privileges actually means? What do it do?

5. Will you write down exactly what you do, when you create a database and user. And what options you make, and what privileges you take. And explain why you do it.


I hope you find time to answer my questions. I will be very grateful for that.
Sorensen84
 
Posts: 7
Joined: 27. June 2012 21:49
Operating System: Windows 7 64-bit

Re: Confused about databases and users

Postby Altrea » 28. June 2012 19:30

Hi Sorensen84,

Sorensen84 wrote:1. When I go into my databases and choose privileges, all my users have access to all my databases. Is that because they're global?

yes. you can see the scope in the column "type".

Sorensen84 wrote:How do I change that? I just want one user to each database.

At your priviledges page (if you don't click a database before).
  • Click on "edit priviledges" in the line of the user
  • uncheck all checkfields in the global priviledges area
  • click OK to save the change.
  • now edit the priviledges of that user again
  • leave the global priviledges empty and scroll down to the Database-specific privileges
  • select your wanted database from the select field
  • check all priviledges you want the user to have
  • Save the change with OK

Sorensen84 wrote:2. You are saying I should not grant all privileges to my user. But I would still like to have full control over everything. What did you do?

Just one user (very common the user root) should have all global priviledges. This user should not be a user which is used by any application, it should only be used for global database manipulation (creating new databases, change user permissions, etc).

Sorensen84 wrote:3. Is this a good way to do it:
I first make the database, then go into the database I have created and choose privileges and add a new user. Here I can make a password for the new user, and there is and option called "Database for user" Shall I then choose "Grant all privileges on database"? Do I then have full control over the database without it being global?

Simply try it out :)
I could tell you everything but you should get a feeling for the fields and options.
If something went wrong, simply delete the newly created users and databases and beginn again from the start.

Sorensen84 wrote:Under that there is another option called "Global privileges (Check All / Uncheck All) " What shall I choose there?

As i said, leave it empty exept a user really needs permission on ALL databases.

Sorensen84 wrote:I don't want all of my users to have access to this database, only the user i create for the database. That means it should not be global, right?

That means no user exept your administrative user root should have global priviledges.

Sorensen84 wrote:Because if it is global, that user will have access to all the further databases I am making, is that right?

All already existing and all further, yes.

Sorensen84 wrote:4. What does that global privileges actually means? What do it do?

It does what the name says: It permits global (database independent) database priviledges.

Sorensen84 wrote:5. Will you write down exactly what you do, when you create a database and user. And what options you make, and what privileges you take. And explain why you do it.

That depends. Simply rule: permit as much priviledges as needed but as less as possible.
It is recommend to maybe permit too less priviledges and realise that something went wrong and then add the missing priviledges rule by rule, then the other way round.
For many applications the data priviledges SELECT, INSERT, UPDATE, DELETE are enough. Structure priviledges are maybe needed for installation of applications the first time. Some applications need Database Views, so for that applications the user should have that priviledges.
The only priviledge which you should never give a user exept root is GRANT, because with that priviledge a user can grant priviledges to other database users.

best wishes,
Altrea
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8293
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Confused about databases and users

Postby Sorensen84 » 30. June 2012 13:39

Hey Altrea

Many thanks again for your answers:) Now I just have a little bit about the root user :) And that's will be all.

So the only user I should have with global privileges, is the root user. I can see that the root user also have access to all my databases. Should I delete that user our just let it be. Do you also have a root user, who has access to all your databases? The root user was already created when I installed Xampp, so shall I just let it be, our can you change the root users permissions as well? Is it a good idea to have the root user, who has access to all databases if something should happen?
Like i said before, I just want one user to each database, but the root user has access to all of them.
Sorensen84
 
Posts: 7
Joined: 27. June 2012 21:49
Operating System: Windows 7 64-bit

Re: Confused about databases and users

Postby Altrea » 30. June 2012 16:48

Sorensen84 wrote:So the only user I should have with global privileges, is the root user.

That's what i would recommend. At the end it's up to you.

Sorensen84 wrote:I can see that the root user also have access to all my databases. Should I delete that user our just let it be.

That's okay, let it be.

Sorensen84 wrote:Do you also have a root user, who has access to all your databases?

yes and that could be very helpful for backuping or manipulating multiple databases.
But i don't use that user in application purposes.

Sorensen84 wrote:The root user was already created when I installed Xampp, so shall I just let it be, our can you change the root users permissions as well?

You can change the existing users as well, but for the root user the only thing which would make sense is giving it a very secure password.

Sorensen84 wrote:Is it a good idea to have the root user, who has access to all databases if something should happen?

what should happen?

Sorensen84 wrote:Like i said before, I just want one user to each database, but the root user has access to all of them.

Well, at the end it's up to you.

best wishes,
Altrea
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8293
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Confused about databases and users

Postby Sorensen84 » 02. July 2012 11:00

Thank you again for your answers. Now I just have two little questions left :)

When I installed Xampp, why do I have all these extra users:

Any % -- USAGE No Edit Privileges Export
Any localhost No USAGE No Edit Privileges Export
pma localhost No USAGE No Edit Privileges Export
root 127.0.0.1 No ALL PRIVILEGES Yes Edit Privileges Export
root localhost Yes ALL PRIVILEGES Yes Edit Privileges Export

Can I delete them, so I only have the root user. Our are they important?
I also have two root users like you can see. One that is named "localhost" and one that is named "127.0.01" Can I delete one of them.
Our should I just let all these users be there, since they are automatically created, when I install Xampp.


I need to be fully sure, that what I am doing is correct. I first start with making a database. After that i go into my new database under privileges and choose "Add a new user" Then I am making a username and a password. Under that there is an option called "Database for user" there is an option called "Grant all privileges on database "testsite" ( I have called my database for testsite) Should i choose that option?
Under that there is the global privileges. Where there are "Data" "Structure" and "Administration" I should just leave all them unchecked, because I don't want global privileges, is that correct?

After that my user to my new database called "testsite" look like this:

User: Thomas84
Host: Localhost
Type: Database-specific
Privileges: Usage
Grant: No
Action: Edit Privileges

Do that above look correct? Do my user only have access to the database "testsite" now. Do the users you create to your database looks like mine, and have the same features?

This is how it all looks like. With the two root-users:
User Host Type Privileges Grant Action
Thomas84 localhost database-specific Usage No Edit Privileges
root 127.0.0.1 global ALL PRIVILEGES Yes Edit Privileges
root localhost global ALL PRIVILEGES Yes Edit Privileges

So the differents is that my type is "database-specific" and under privileges is "usage" now. I uncheck all my global privileges. So all boxes under "Data" "Structure" and "Administration" is unchecked. Do you do the same?
And I have no grant. If "Grant" is set to "Yes" did that mean, that the user has Global Privileges?

That was the last of my questions. I am very glad, that you have helped me. Thanks again :)
Sorensen84
 
Posts: 7
Joined: 27. June 2012 21:49
Operating System: Windows 7 64-bit

Re: Confused about databases and users

Postby Altrea » 02. July 2012 15:40

Sorensen84 wrote:Any % -- USAGE No Edit Privileges Export
Any localhost No USAGE No Edit Privileges Export

These are Anonymous / Testusers created automatically by MySQL. This users have just priviledges on the databases test and everything which starts with test_.
You can delete the users without worry.

Sorensen84 wrote:pma localhost No USAGE No Edit Privileges Export

phpMyAdmin user. You should leave that user as it is. It has very limited priviledges so thats okay.

Sorensen84 wrote:root 127.0.0.1 No ALL PRIVILEGES Yes Edit Privileges Export
root localhost Yes ALL PRIVILEGES Yes Edit Privileges Export

Can I delete them, so I only have the root user. Our are they important?
I also have two root users like you can see. One that is named "localhost" and one that is named "127.0.01" Can I delete one of them.
Our should I just let all these users be there, since they are automatically created, when I install Xampp.

I would leave both users, simply because the user for localhost just works if your name resolution for localhost works. If not, you can't administrate your database.
But it's up to you. If you always connect with localhost, you can delete the 127.0.0.1. The same the other way round.

Sorensen84 wrote:Under that there is an option called "Database for user" there is an option called "Grant all privileges on database "testsite" ( I have called my database for testsite) Should i choose that option?

That depends. Does the user really needs ALL priviledges? In the most cases it wouldn't. Like i said before, i would give the user as much priviledges as needed and as less as possible.
Just to make it clear: Let's say your database user has the priviledge to drop database tables and you have a security issue in one of your html forms. Someone can delete any database table your user has access to via SQL Injection.

Sorensen84 wrote:Under that there is the global privileges. Where there are "Data" "Structure" and "Administration" I should just leave all them unchecked, because I don't want global privileges, is that correct?

global priviledges = evil :evil:

Sorensen84 wrote:After that my user to my new database called "testsite" look like this:

User: Thomas84
Host: Localhost
Type: Database-specific
Privileges: Usage
Grant: No
Action: Edit Privileges

Do that above look correct?

looks good if the user really needs all usage priviledges. I can't say that often enough.

Sorensen84 wrote:Do my user only have access to the database "testsite" now.

If you have done everything correct, yes. You can check that if you check the user priviledges from the phpmyadmin home (without selecting any database).

Sorensen84 wrote:Do the users you create to your database looks like mine, and have the same features?

No. My users have just the permissions they need. Some just have SELECT priviledges, some can SELECT, INSERT and UPDATE, some others SELECT, INSERT, UPDATE and DELETE... others can CREATE VIEWS, etc. Sure i have few users which has full USAGE priviledges like yours too.

Sorensen84 wrote:And I have no grant. If "Grant" is set to "Yes" did that mean, that the user has Global Privileges?

GRANT means the user can grant permissions to other users. No user should have that exept your root user.

best wishes,
Altrea
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8293
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Confused about databases and users

Postby Sorensen84 » 02. July 2012 19:54

Hey Altrea

Thanks again :) I only have this question left now. You wrote this:

"No. My users have just the permissions they need. Some just have SELECT priviledges, some can SELECT, INSERT and UPDATE, some others SELECT, INSERT, UPDATE and DELETE... others can CREATE VIEWS, etc. Sure i have few users which has full USAGE priviledges like yours too"

I actually don't have any privileges checked on. So what do you mean with I have full usage privileges. I don't know what "Usage" means. Can you explain that.

I did go back into "edit privileges" and checked all boxes in the option "database-specific privileges" but did not choose "Grant". I do not have "Global Privileges" Is it bad to have all privileges in the database? Can it hurt anything? My user now look like this:
User: Thomas84
Host: Localhost
Type: Database-specific
Privileges: All Privileges ( Before it was usage, I dont know what that means. But I did not have any of the boxes checked in the database-specific privileges option. Now they are checked, but not "Grant" )
Grant: No
Action: Edit Privileges

So my database have all privileges but not "Grant" and my user has no privileges at all. Is that alright? I have been making 3 databases now with one user to each of them. None of the users are global. So that is good. It's only each of my databases that have all privileges.

And when I am making a new user to each database, I choose this option under "Database for user":
None
Create database with same name and grant all privileges
Grant all privileges on wildcard name (username\_%)
Grant all privileges on database "testsite" ( I choose this one. Is that okay. That don't make my user global )
And when I have done that. I do as I write before, I got into my user under edit privileges and choose check all in "Database-specific privileges"
So my database have all privileges without Grant and my user have no privileges and is not being global. What do you think about this way I do it?
Sorensen84
 
Posts: 7
Joined: 27. June 2012 21:49
Operating System: Windows 7 64-bit

Re: Confused about databases and users

Postby Altrea » 02. July 2012 20:09

Hi Sorensen84,

Sorry i have nothing more to say, because i think everything is said.
You have all possibilities to check the user priviledges with phpmyadmin (you can check the user priviledges without clicked a database and if you click a database the user has priviledges too). From the phpmyadmin home site a user with just database specific priviledges will always be displayed as usage, independent which or how many specific priviledges he has. Thats differnet if you have clicked a database (here means all priviledges exept grant = all priviledges)

Sorensen84 wrote:Is it bad to have all privileges in the database? Can it hurt anything?

I have explained that in my last post. Yes, it can hurt (keyword SQL-Injection)

best wishes,
Altrea
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8293
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Confused about databases and users

Postby Igal - Incapsula » 03. July 2012 10:37

Is it bad to have all privileges in the database? Can it hurt anything?


Yes it can.

This should be avoided at all cost - mainly for security reasons; as giving every user full privileges can, and most-likely will, lead to hack attempt at some point in the future (i.e. SQL injection).

Privileges should be assigned only as needed per individual user/application.

BTW: Assigning full privileges to all users will also prevent you from achieving PCI compliancy. (think long term)

Best of luck.
Igal - Community Evangelist - Incapsula (Cloud CDN Acceleration & Security)
User avatar
Igal - Incapsula
 
Posts: 1
Joined: 03. July 2012 10:14
Operating System: Windows

Re: Confused about databases and users

Postby Sorensen84 » 04. July 2012 09:47

Hello Igal

I don't have any privileges on my users at all. No boxes is checked. But I have check all privileges to my database. I don't know if you know what I mean.
Sorensen84
 
Posts: 7
Joined: 27. June 2012 21:49
Operating System: Windows 7 64-bit

Re: Confused about databases and users

Postby Sorensen84 » 04. July 2012 09:48

Altrea wrote:Hi Sorensen84,

Sorry i have nothing more to say, because i think everything is said.
You have all possibilities to check the user priviledges with phpmyadmin (you can check the user priviledges without clicked a database and if you click a database the user has priviledges too). From the phpmyadmin home site a user with just database specific priviledges will always be displayed as usage, independent which or how many specific priviledges he has. Thats differnet if you have clicked a database (here means all priviledges exept grant = all priviledges)

Sorensen84 wrote:Is it bad to have all privileges in the database? Can it hurt anything?

I have explained that in my last post. Yes, it can hurt (keyword SQL-Injection)

best wishes,
Altrea


Hello Altrea

You have helped my a lot :) Thanks for you advices.
Sorensen84
 
Posts: 7
Joined: 27. June 2012 21:49
Operating System: Windows 7 64-bit

Re: Confused about databases and users

Postby Altrea » 04. July 2012 15:45

You are welcome :)
I finally have marked your topic as solved.

I wish you much fun with XAMPP and all that stuff 8)
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8293
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64


Return to XAMPP for Windows

Who is online

Users browsing this forum: hthnightwolf, JJ_Tagy and 72 guests