CMS php logon with C#

Question

I'm not deeply into MySQL, so I followed a small tutorial about a simple php CMS system. As described here, https://codewithawa.com/posts/complete-user-registration-system-using-php-and-mysql-database

In the article there is a simple user MySQL database generated by:

CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`username` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(100) NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

It seemed just what I needed, a simple of login system for simple datalogger site. And for web purposes it would indeed work I believe. Though in my case I have a small datalogger device that should login over C#

using MySql.Data.MySqlClient;      

When I started to write the connection url

 MySqlConUrl = $"Server={DBServer};Database={DeviceDB};Uid={Acount};Pwd={AcountPass}";
 MySQLcon = new MySqlConnection(MySqlConUrl);

I noticed when I tried to write multiple device databases so a user acount logs on and by userID is allowed/disalowed to see databases. Then C# cannot login to the earlier database, nor can I logon to that user database itself with C#. And I cannot set permissions with users from the first table. Is there a way to make that earlier database support C# logins as well?

--update 2--as of 18/2/2018
To clarify a bit more MySQL contains a database \mysqls\users
That's where MySQL internal users are created, only those accounts can be used by the MySQLclient, MySqlConnection( ) command. What I am looking for a way to use another custom user database, not under \mysql\users. So that it can provide logon functionality for mySQL connectors as used by C#

Answer 1

From the question, this is what I think is going on.

You are trying to use the web users credential (Account and AccountPassword) to make the connection to the database.

This isn't how it works for websites and in the blog post.

Here is how it works

You have a database in MySQL which the application connects to using its own login information which is stored in the connection string and this doesn't change depending on the user.

Now when you want to authenticate a user you can check up his login information in the users table (while still connecting to the database using YOUR connections string).

If you want more information on authentication/authorization check out the asp.net docs

Response to comment clarification

Now let's look at it from a Multi-tenant application perspective.

If you plan on having only one instance of the application running which connects to other databases then you would need a way to get the database information for the tenant. What you can do is create one database that the application connects to and store that connection string in the application settings. It should have a table with all the tenants and the database login information.

If you have an instance per tenant then it would probably be easier to just add the connection string in the web.config, appsettings.json or in an Environment Variable.

Regarding changing where/how MySQL stores user information

You can check out Database Administration