dcsimg
Results 1 to 3 of 3
  1. #1

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,166

    MySQL-User-Setup

    Question to the resident Database-Experts:

    I'm writing a Frontend to a MySQL-Database.
    So far so good.
    Now:
    The MySQL-Server runs inside a private Network, but will be accessible from outside (dyndns and port-forwarding on the router).
    Access to the MySQL-Server will be from both sides (private network and internet).

    Question: How to setup the "Users"?
    Set up any User as a MySQL-User (with GRANTS and what not), or use a, let's call it a "Dummy"-User, only i (and select others) will know the login-credentials of, but every other User is set up in a separate table within my own Database-Schema?

    Processing as follows:
    End-User launches my Frontend.
    Internally inside the frontend the Connection is established using the above mentioned "Dummy"-User, but the End-User doesn't know this.
    He enters his Login-Credentials, the frontend looks up in my own Users-Table, and everything OK.

    The reason why i ask: If i setup every User as a MySQL-User, those Users might get the Idea to use MySQL-Workbench (or any other tool) to connect directly to the Server.
    If i use the "Dummy"-User-approach, when the End-user gets the idea to use MySQL-Workbench with his credentials, the Server can say "Oh, no Sir, you not. Not with those credentials!".

    Opinions?
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,709

    Re: MySQL-User-Setup

    I'm a SQLServer rather than MySQL but I feel the principles are the same on this one.

    First, a bit of nomenclature. You "Dummy User" is traditionally referred to as an "Application" user or login.

    I want to address this before I go any further:-
    The reason why i ask: If i setup every User as a MySQL-User, those Users might get the Idea to use MySQL-Workbench (or any other tool) to connect directly to the Server.
    If i use the "Dummy"-User-approach, when the End-user gets the idea to use MySQL-Workbench with his credentials, the Server can say "Oh, no Sir, you not. Not with those credentials!".
    In terms of security it's preferable to design your database in such a way that it's agnostic of the tool accessing it. You should only be giving permissions to access those parts of the system that you are happy with a user accessing - and you shouldn't have to care how the do so. Obviously that's not always feasible but it is the first layer of your security onion and you should be pursuing it. So if you're happy with a user reading from the Orders table, give them permission to read the Orders table. If you're not, don't.

    Your statement above sounds like you envisage giving open access to everything in the DB and then controlling access through the app - that's wrong. Controlling access in the app is good but it's your second line of defence, not your first. Your first is the permissions in and design of the DB and if you get that right, you don't really care about the access vector.

    Now the three typical approaches to security:-
    Conventional wisdom in the MS world is that you should use Windows Authentication where possible. It's basically a "passort" or "certificate" based form of security. You create DB users that correspond to your users Windows Logins. When they login their windows login is passed to the DB for validation so they don't need to worry about entering passwords etc. This approach is probably the easiest to manage and generally very secure BUT it only applies to windows users. External users aren't easily supported and it doesn't work if someone's accessing from a non windows device. I honestly don't know if it's even available in MySQL but it would certainly be worth you doing some research to see if there's an equivalent

    The next form is to create individual database logins for each user. I would argue that this is probably the second best option. It's reasonably easy to manage, all the functionality is available to set up "Groups" of users and "Roles" which makes managing who can access what simpler. Auditing is possible because you can see who is actually carrying out an operation and so on. If Certification based logins aren't available to you then this is probably the best bet. The downside is that it's not really possible to manage this except by sitting at the DB Console because that's where it's housed. This means it's not particularly useful behind systems where a user can "sign up" like a forum or an internet shop. Unless you're going to give that sign up process access to the security model in the DB (which, I would suggest, is a VERY bad idea) then you'll need some way of processing those requests.


    Finally there's the application log in. It's probably the least secure. In DB security terms it means you have to open up access to everything to everyone - which is horrible. And it means you'll never really know which user is accessing which entity so your auditability is trashed. You're going to have to write all your own security and auditing code which you will probably put in the app, where it is potentially comprisable. If you can hold the whole lot behind e.g. some web services then it gets a lot safer and more manageable though. But it does have the advantage that users can "self manage". They can sign up for different roles and so on. Generally, though, I'd suggest that this approach should only ever be given VERY limited access to the DB. Sure, you might want to allow it to create Order records (ideally in a DMZ area) but you sure as hell don't want to give t permission to view the company financials.


    I don't know which model is right for you but hopefully there's some food for thought here. Most commercial systems actually end up with a blend.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    815

    Re: MySQL-User-Setup

    I'd personally do the latter for security reasons. Your public MySQL will be constantly "attacked" so the fewer logins that work externally the better. I have it set so I can't even login from an external IP.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.