Search code examples
c#mysqldatabasewindowswindows-authentication

Connect to MySQL database w/ C# as non-root user?

Let me start by saying I'm completely new to databases, but I have been reading through the MySQL tutorial they have.

Right now, I'm trying to make an app that allows unprivileged users (non-root) to connect and do some commands on a database through a C# GUI app. The users will login to the database using windows authentication.

Now, I was able to to make a quick GUI where the person running the program can connect to the database on a local host using "root" and get whatever content is in it.

My question is, how exactly do I allow users to connect w/ non-root privileges? The only things I've been able to find all deal with using the connection string with "root" as the user.

Edit: The database is already made. I won't personally be able to connect to it as a root user and give other users permissions.

Solution

  • Some important concepts too are the rows in the table shown here:

    select user,host,password from mysql.user where user='pfinferno';

    Important takeaways, users can have multiple hostnames or wildcard. Each have their own rights and passwords. Though the password from the above is hashed, at least you can quickly eyeball it so see if all the passwords match (such as root with 3 accounts).

    The host column is populated by the following values, mostly:

    Specimen A:

    • localhost
    • 127.0.0.1
    • %
    • common names such md21.newyork.comcastbusiness.net and the like

    The % value is the wildcard. It is for flexibility, but it can be highly dangerous when used with users like 'root'@'%'. Keeping root as the 1st two only in Specimen A is highly advised. Also, the first two are quite different in different tools. What I recommend is having a root user row for the first two, and keeping the passwords the same. There are pros and cons to this approach. But remember that when you can't connect that something else out there was relying on the connection, be it an agent, phpmyadmin, a tool, the my.conf settings, etc. You will be revisiting this a lot.

    In the example given by Mureinik it grants privileges to the user with the wildcard host = %. This means it is banking on a user created as such. Note that it is typical to have user accounts setup that way. Though there is nothing restricting you to locking it down tighter.

    When a user attempts to connect to the server, the user he is ultimately connected as can be resolved to a different user/host combo, as can be seen in the case where there is no localhost (host) user or common-name, but rather one with a host value of wildcard %. This can be seen with this query:

    Specimen B:

    select current_user(),user();

    The latter is the user presented by the connection attempt, the former is the one that was resolved and actual. The implications can cause one to waste days in debugging, as can be seen in these forums. Some people can't connect for days, I am serious.

    Specimen C:

    create user 'pfinferno'@'localhost' identified by 'thePassword';
create user 'pfinferno'@'127.0.0.1' identified by 'thePassword';
create user 'pfinferno' identified by 'thePassword';
create user 'pfinferno'@'md21.newyork.comcastbusiness.net' identified by 'thePassword';

    -- note #3 above is same as 'pfinferno'@'%'

    Now granted, the host name may be wildcard for normal users, and host name may be localhost and 127.0.0.1 for root only. But in the attempt to lock down security, admins often create user accounts based on hostname coming in (such as Specimen C, line 4), and vary security with grants based on that. Managing it can be a bit overwhelming. So they often just say screw it, I will create the wildcard user. That may be fine for a new user, say Susie, but for Secimen C line 4, if that is who you are coming in, you can have grants on line 3 user that you won't pick up. So the resolving of what user your are actuallity (See Specimen B), goes from most specific hostname to fallback to more general, until it finds one such as wildcard.

    Unfortunately, users don't connect with hostname specified per se, they just are what they are. They try to connect. So you don't say hey I want to be this thing @hostname, you just are what you are. You are 'pfinferno'@'md21.newyork.comcastbusiness.net', but may fallback to wildcard.

    If users are dropped in Specimen C except for wildcard %, well you better have the grants that went with them that you expect, because you are now a new user.

    Try to limit the use of wildcards on the grant statement to not do *.* as in the lazy approach, which would just grant rights to all databases and tables. In Mureinik's example, it was for all tables in one database. Not too shabby. Try to fine tune rights, such as granting SELECT privileges on tables or not at all, to users that don't need them. Be careful with WITH GRANT OPTION as seen on the net with cut and paste from it. If you use it, you just granted the user rights to grant other users rights.

    SSH Tunnels

    One reason you might not want to use Secimen A host = % wildcard (other than the obvious We are Risk Averse) is that 

    create user 'pfinferno'@'localhost' identified by 'thePassword';

    is perfect for SSH Tunnels. You would be connecting through a cryptographically secure channel with PKI, and present yourself as if you are @ localhost. This drastically reduces security exposure.

    Why Can't I Connect?

    Hopefully the below visual with little commentary can show why I named this section as I did.

    drop user 'pfinferno'@'localhost';
drop user 'pfinferno'@'127.0.0.1';
drop user 'pfinferno'@'%';
drop user 'pfinferno'@'md21.newyork.comcastbusiness.net';
flush privileges; -- some say this is not necessary, I have found otherwise

create user 'pfinferno'@'localhost' identified by 'thePassword';
create user 'pfinferno'@'127.0.0.1' identified by 'thePassword';
create user 'pfinferno' identified by 'thePassword';
create user 'pfinferno'@'md21.newyork.comcastbusiness.net' identified by 'thePassword';

    ...

    select user,host,password from mysql.user where user='pfinferno';

grant all on so_gibberish.* to 'pfinferno'@'%'; -- grant all rights on so_gibberish db

flush privileges; -- some say this is not necessary, I have found otherwise

    Look at some grants.

    show grants for 'pfinferno'@'localhost';    -- sandboxed. Can just log in and sit there
+-----------------------------------------------------------------------------------------+
| Grants for pfinferno@localhost                                                          |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'pfinferno'@'localhost' IDENTIFIED BY PASSWORD '*74692AE70C53...' |
+-----------------------------------------------------------------------------------------+

show grants for 'pfinferno'@'127.0.0.1';    -- sandboxed. Can just log in and sit there
same as above

show grants for 'pfinferno'; -- wildcard % user, has all rights on so_gibberish;
+-----------------------------------------------------------------------------------------+
| Grants for pfinferno@%                                                                  |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'pfinferno'@'%' IDENTIFIED BY PASSWORD '*74692AE70C53...'         |
| GRANT ALL PRIVILEGES ON `so_gibberish`.* TO 'pfinferno'@'%'                             |
+-----------------------------------------------------------------------------------------+

    Note above that GRANT USAGE means at least you have the rights to log in and sit (sandboxed). But the wildcard %user also has all rights on so_gibberish db in its entirety.

    Now I go to mysql prompt via mysql -u pfinferno -pthePassword

    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

mysql> select current_user(),user();
+---------------------+---------------------+
| current_user()      | user()              |
+---------------------+---------------------+
| pfinferno@localhost | pfinferno@localhost |
+---------------------+---------------------+

    The attempted user (user()) was resolved to the same (current_user()). I was sandboxed, able to do basically nothing, except select now() til bored to death.

    mysql> use so_gibberish;
ERROR 1044 (42000): Access denied for user 'pfinferno'@'localhost' to database 'so_gibberish'

    quit mysql CLI as that user.

    Now

    drop user 'pfinferno'@'localhost';
drop user 'pfinferno'@'127.0.0.1';
drop user 'pfinferno'@'md21.newyork.comcastbusiness.net';

    I just dropped three out of four of my pfinferno users

    Go to mysql prompt via mysql -u pfinferno -pthePassword

    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| so_gibberish       |
+--------------------+

mysql> select current_user(),user();
+----------------+---------------------+
| current_user() | user()              |
+----------------+---------------------+
| pfinferno@%    | pfinferno@localhost |
+----------------+---------------------+

mysql> use so_gibberish;
Database changed

    This shows that @ the CLI (or any program) from any host, that the attempt to connect is first as presented by user and then as resolved to actual ( see the output from user() and current_user(), respectively).

    So, perhaps oddly, as I dropped users, the EFFECTIVE RIGHTS increased as the user was resolved to a different one. This can be the subject of hours/days/weeks of debugging. Users can have no clue who they are really logged in as (resolved to), or why they can't, as each, by the way, can have a different password ! This is especially true with user root with several rows in mysql.user ... and considering that probably 50% of all mysql users connect with it, initially, as developers. Just a guestimate.