Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

To educate myself in PHP and mysql, I am making a browser based rpg game. This game will allow any number of users to register an account and, once registered, they can add, edit or delete champions into my database. The problem is that they should ofcourse only be able to edit and delete champions that they made themself.

Now since this is just a practise project, I don't forsee large quantities of data or any serious attempts to hack it, but all the same I'd like to do it right so I can use this experience if I ever do get to work on large serious projects.

Should I
a) Let a new database be created for each user?
b) Let a new set of tables be created for each user?
c) Put the data of all users together in the same 3-5 tables and track for each row which user created it?

I think that C is the cleanest solution with the least data consumption, but perhaps the one with the least security. Is this correct?

And how would you suggest I implement solution C? Is there a privalege that restricts a user from only editting / deleting rows that he/she created him/herself? Or should I include this restriction in the PHP level? Would having this restriction in the PHP level not create a security risk since someone might be able to connect to my database without my php scripts?

And should a new mysql user be made for each player? Or can they all share a user account and make the distinction between players with an ID field or something like that?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

I would do it like this:
- Create a table users, where you store your users.
- Create a table champions, where you store the champions and also in a column champions.users_id the id for that user who created this champion.
- If a user wants to edit a champion, you check if he belongs to him by checking if champions.users_id is the same as the users ID.

And by the way, no one should be able to connect to your DB. You can define, that you can only grant access to the DB, if the IP is right. For example only localhost or whatever. Your queries should all run through one mysql user.

This are my thoughts about this topic. Maybe possible that there are better ways. But i would go on like this.

share|improve this answer
    
Ah, good point about the IP/localhost restriction. I had it in mind that since it's a public website, it should have a database with public acces. But all PHP scripts I build for this game will run through the localhost, as long as I place them both on the same server, correct? ( I plan to use a free webhost for this) –  Micheal Dec 7 '12 at 16:48
    
Yes that's correct. By default remote access to the MySQL database server is disabled. And i think on free webhosts, you cant even change it anyway. –  Michael Hirn Dec 7 '12 at 17:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.