Return to Answer

correct the "one schema per user" part

Source Link

edited Mar 17, 2023 at 10:09

There are two options I can see:

The "modern" option: As Philip Kendall explains, don't. Do not directly connect to the database from the client. Instead, have a dedicated backend service which does. This is what most modern client/server applications do - web applications in particular, but native applications as well.
The "traditional" option: Use the SQL Server's authentication and authorization mechanisms. That means creating a database schema andseparate database user account for each user of your application, thus essentially giving each user their own separateand setting appropriate permission at the database level for reading and writing (typically by forcing most operations to happen via stored procedures). This, when done correctly, can also be secure, and this is how many systems used to work (in the 90s and earlier, I'd say). However, there are numerous drawbacks to this solution (such as depending on the database server for a lot of functionality, and putting logic into the database), thus most modern systems use option 1.

I'd follow Philip Kendall's answer and create a backend service - the initial setup is fairly simple with modern frameworks.

There are two options I can see:

The "modern" option: As Philip Kendall explains, don't. Do not directly connect to the database from the client. Instead, have a dedicated backend service which does. This is what most modern client/server applications do - web applications in particular, but native applications as well.
The "traditional" option: Use the SQL Server's authentication and authorization mechanisms. That means creating a database schema and user account for each user of your application, thus essentially giving each user their own separate database. This, when done correctly, can also be secure, and this is how many systems used to work (in the 90s and earlier, I'd say). However, there are numerous drawbacks to this solution (such as depending on the database server for a lot of functionality), thus most modern systems use option 1.

I'd follow Philip Kendall's answer and create a backend service - the initial setup is fairly simple with modern frameworks.

There are two options I can see:

The "modern" option: As Philip Kendall explains, don't. Do not directly connect to the database from the client. Instead, have a dedicated backend service which does. This is what most modern client/server applications do - web applications in particular, but native applications as well.
The "traditional" option: Use the SQL Server's authentication and authorization mechanisms. That means creating a separate database user account for each user of your application, and setting appropriate permission at the database level for reading and writing (typically by forcing most operations to happen via stored procedures). This, when done correctly, can also be secure, and this is how many systems used to work (in the 90s and earlier, I'd say). However, there are numerous drawbacks to this solution (such as depending on the database server for a lot of functionality, and putting logic into the database), thus most modern systems use option 1.

I'd follow Philip Kendall's answer and create a backend service - the initial setup is fairly simple with modern frameworks.

Source Link

answered Mar 17, 2023 at 7:50

There are two options I can see:

The "modern" option: As Philip Kendall explains, don't. Do not directly connect to the database from the client. Instead, have a dedicated backend service which does. This is what most modern client/server applications do - web applications in particular, but native applications as well.
The "traditional" option: Use the SQL Server's authentication and authorization mechanisms. That means creating a database schema and user account for each user of your application, thus essentially giving each user their own separate database. This, when done correctly, can also be secure, and this is how many systems used to work (in the 90s and earlier, I'd say). However, there are numerous drawbacks to this solution (such as depending on the database server for a lot of functionality), thus most modern systems use option 1.

I'd follow Philip Kendall's answer and create a backend service - the initial setup is fairly simple with modern frameworks.