Best privilegs for your Mysql user for your website ? [ OPEN Discussion ]

[HattanBinNassar 4]

Soo , you're willing to build a website for your server ? 

So you must link your database with your website but that have a lot of risks ,

to optimise the potential risk one of the things That you need to do is " limited privileges user " ,

 

So the discussion question is :

What's the privileges That you would use for your website user ?

 

For me :

i think ( 
 

for registration & login process :

Account.account.password & Email : update & Select ,



For players ranking system :

Player.player: select



For guild ranking system :

Player.guild: select



For itemsshope :

Player.item_award: insert 

+ Other account for ' Coins ' linked with PayPal

)

 

What about your privileges choice ?

Spoiler

Important **** ( never ever give your user full privileges ) 

Spoiler

Important *** ( you're root password complexity is important )

 

[Karbust 4737]

account.account can have insert, select and update, not only to email and password like you referenced.

player.guild only select

player.player can have select and update (in case you have an unbug function, otherwise only select)

player.item or player.item_award only insert, depending on which table you use

Other tables related to item shop and purchases should have, at least, insert and select

[HattanBinNassar 4]

1 hour ago, Karbust said:

account.account can have insert, select and update, not only to email and password like you referenced.

player.guild only select

player.player can have select and update (in case you have an unbug function, otherwise only select)

player.item or player.item_award only insert, depending on which table you use

Other tables related to item shop and purchases should have, at least, insert and select

Nice , this informative , what do you think as a web dev is the most potential risk of a website ?

[Karbust 4737]

2 hours ago, HattanBinNassar said:

Nice , this informative , what do you think as a web dev is the most potential risk of a website ?

SQL Injection, bad handling of sessions and/or authentication cookies/tokens, remote code execution

If you don't sanitize every single thing you get from the frontend and it's supposed to end up in a database, then you are open to the risk of SQL Injection. I recommend the use of ORMs, since it already handles almost all the boring part of sanitizing input.

If you don't write good code and end up doing some sketchy shit that executes commands on the server running the website, then your are fucked...

Authentication is some of the hard parts of doing a good website, there are discussions on where should the information be saved, either cookies or local storage and manually attach it to every request. I work with JWT, and I always put the token on cookies so it goes attached to every request when CORS with Allow Credentials set to true. Also make sure you use strict true so it doesn't go on requests to other domains. Obviously cookies can be grabbed by some third party trick, but it's hard and if you put a expire date on JWT and use refresh tokens and IP validation, then you are better served.

 

[HattanBinNassar 4]

5 hours ago, Karbust said:

SQL Injection, bad handling of sessions and/or authentication cookies/tokens, remote code execution

If you don't sanitize every single thing you get from the frontend and it's supposed to end up in a database, then you are open to the risk of SQL Injection. I recommend the use of ORMs, since it already handles almost all the boring part of sanitizing input.

If you don't write good code and end up doing some sketchy shit that executes commands on the server running the website, then your are fucked...

Authentication is some of the hard parts of doing a good website, there are discussions on where should the information be saved, either cookies or local storage and manually attach it to every request. I work with JWT, and I always put the token on cookies so it goes attached to every request when CORS with Allow Credentials set to true. Also make sure you use strict true so it doesn't go on requests to other domains. Obviously cookies can be grabbed by some third party trick, but it's hard and if you put a expire date on JWT and use refresh tokens and IP validation, then you are better served.

 

So informative , thanks a lot ,, my fear of building a website has increased to the roof

[Karbust 4737]

17 hours ago, HattanBinNassar said:

So informative , thanks a lot ,, my fear of building a website has increased to the roof

No one should face fear when starting, we all start somewhere, sometimes is just trial and error.

I read a lot of blog entries (mostly all on medium) and documentation for something I'm learning or trying to achieve or trying to compare to alternatives and see which one is the most performant and/or more secure.

I have a lot of books, some which I have personally used for both college and personal projects, you can find them here: https://ebooks.karbust.me/Technology/

[HattanBinNassar 4]

4 hours ago, Karbust said:

No one should face fear when starting, we all start somewhere, sometimes is just trial and error.

I read a lot of blog entries (mostly all on medium) and documentation for something I'm learning or trying to achieve or trying to compare to alternatives and see which one is the most performant and/or more secure.

I have a lot of books, some which I have personally used for both college and personal projects, you can find them here: https://ebooks.karbust.me/Technology/

Thanks tho , that helpful ,i will let you know when i finished my website ( i found a really great idea for it )