Understanding Cloud Databases for several Customers

[h725]

Hello everybody,

I would like to understand how Cloud Databases for several Customers are organised.
Background:

I am using MS SQL Databases for development. Every instance has its own username
and password. So the databases in this instance have the same password. At the moment
the customer has his own database installed locally.

If I offer my customers cloud-databases, how are new customers handled / managed?
Do I have to install an instance for every new customer? Do several customers share
one instance? Maybe MS SQL is also the wrong Database for this. Do you have any
idea or links so I can see how this is handled in a professional way?

Kind regards
h725

 

[ilan]

is the database schema for all costumers the same?
what information do you want to store? what kind of app is it?
do you need a rational database or a non-rational database?

 

[h725]

Hello,
the database schema is the same for all customers.
It is an merchandise management system where you have
the typical tables: products, customers, orders etc.
I need a rational database.

h725

 

[ilan]

i would say for each costumer a separate db.
so basically what i would do is running a mysql server on a local machine with a fix public ip. then i will run a webapp on that machine that is the only app that has direct connection to the sql db and all clients will use api calls with api key to write/read to that database.
every costumer should have his own database.
are you familiar with web apps?

the second cheaper option is creating a mysql db on a shared hosting and making php calls to read/write to that database.

 

[ilan]

something like this:

you handle all endpoints and have full control of what you are allowing to do on the database like read/write/delete etc.
if you would make a direct connection from the client to the db it is very dangerous. A hacker could get the connection data and do whatever he wants to the db and there are also more reasons why not putting the connection on the client directly.

 

[h725]

Thank you very much.

What I actually do not understand yet is how to secure the data:

I am new to mysql, so I do not know if there are huge difference to mssql
concerning instances and databases. Is it possible to set a password for
each database? Otherwise one might manipulate the connectionstring
if he knows the database name and has access to another customer.

 

[h725]

Thanks.
I think you were faster than me...
Is there a sample here in the B4X Forum?

 

[ilan]

Thank you very much.

What I actually do not understand yet is how to secure the data:

I am new to mysql, so I do not know if there are huge difference to mssql
concerning instances and databases. Is it possible to set a password for
each database? Otherwise one might manipulate the connectionstring
if he knows the database name and has access to another customer.

Thanks.
I think you were faster than me...
Is there a sample here in the B4X Forum?

correct, having the sql db running on the same pc as the webapp is secure enough.
all clients will only get data or write data using api calls and api key for security.
like this you can allow what ever you want and deny what ever you want.
if there is no endpoint to delete the whole db then it is impossible to do it as long as you follow all important sql securities like parameterized queries ...

all important data like passwords should be encrypt!

 

[ilan]

Is there a sample here in the B4X Forum?

to much examples ?

my latest one use that technique (like this one: https://www.b4x.com/android/forum/threads/lets-quiz-b4j-api.149382/#content)

you may have a look at @aeric live stream tutorials:

[Live] Streaming on YouTube - Creating Web API with B4X

Last session: Wed 01 Nov 2023 21:00 GMT+8 Topic: Creating Web API with B4X (Part 4) - Connecting to Database with MiniORM Last session: Wed 25 Oct 2023 21:00 GMT+8 Topic: Creating Web API with B4X (Part 3) - Connecting to Database Last session: Wed 11 Oct 2023 21:00 GMT+8 Topic: Creating...

www.b4x.com

 

[h725]

Thanks for the support.