I am making my first simple WPF app with C# and SQL Server and I can't wrap my head around database access for new users. I am failing to understand the logic of checking if a user exists on a database and adding a new one if not.
What I have done:
- Created a local SQL Server instance
- Created a database and a table in it, called
USERS
What bugs me the most is the connection string. I want to use SQL Server authentication because I will host the database publicly so I can share my work publicly and allow people to use my app if they want.
I understand I can manually create with SSMS Logins and Users and map them to a specific database, then send my users the username and password which can be used inside a connection string when they try to connect.
Which is ok for me for now; I would have anyone wanting to use the app just email me a request or something and I'll email them back the login data. But it would be better if they can register themselves.
When running locally I understand I can just put a trusted connection inside the connection string and that all works but what about a completely new user? If a new user tries to log in; what username and credentials to put inside the connection string if he does not exists, how to check him on a database if he does not have access to it?
Do I need, on a server-side with SSMS (Security-Logins and Users) create a default login and a user and only give the right to read/write to table USERS so it can register itself?
Am I way off?
Thank you
CodePudding user response：
You will have to create some kind of user management functionality to create/read/update the users and logins. This can be a pain when using the database provider's security. Usually the app will talk to an api which will take care of the authentication/authorization part of the logic, and then the api will have a single user to connect to the database.
Connecting directly to the database from a client app is discouraged since that necessitates putting your database on the network, which is discouraged for security reasons.
CodePudding user response：
I am failing to understand the logic of checking if a user exists on a database and adding a new one if not.
For this first question you should add a check in your sql insert into statement or create some Unique fields. If you wish to follow the first approach you have to do something like this:
//Use some paramater which supposed to be unique, i.E LastName (just for demo purposes, ofc there would be more than one person with same LastName and must be inserted into the table as valid new user)
if not exists (select 1 from USERS where USERS.LastName = @LastName)
insert into USERS(FirstName, LastName, ...)
values (@FirstName, @LastName, ...)
For applications like WPF, WASM that will reside in the end's user machine you should never store sensitive information (db user/pass) in the connection string. Also in most cases its bad idea even to encrypt it. Instead the most straightforward way to go is with Web API and with adding this extra layer between the UI and the database all your sensitive info will now reside to your server. In most cases you should create DB credentials for DB administrators and for end users you must use an authentication mechanism such as Microsoft Identity Platform. Its also bad idea to create a new auth mechanish from scratch, because there is a big chance to add vulnerabilities. Finally if you want to have different privileges you could assign roles (such as admin, guest etc) to your users. For more information about Identity Platform take a look here: https://docs.microsoft.com/en-us/azure/active-directory/develop/