Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Mysql-CGI Security Question

by serotta1958 (Novice)
on Aug 04, 2011 at 13:19 UTC ( #918523=perlquestion: print w/replies, xml ) Need Help??

serotta1958 has asked for the wisdom of the Perl Monks concerning the following question:

I am kind of rookie at the CGI thing and have a design question. How do I handle the mysql login in? As a temporary measure I have the root password hard coded into the script..which I know is wrong. How do others handle this? do you grant each user access using the mysql grant command or set up your own user table? also do you store the password in a cookie or can you just log a person in at the beginning of the session and it will stick through all the screens? Has anyone ever seen a book or guide with this level detail on security/design issues? thanks for any help.

Replies are listed 'Best First'.
Re: Mysql-CGI Security Question
by moritz (Cardinal) on Aug 04, 2011 at 13:46 UTC

    Each OS-level user that needs access to the DB gets a DB user on his own, and usually also a separate database, and gets granted all the permission he needs.

    His DB credentials are then stored in a file, and read access of that file is limited to a certain user or group.

    Then one uses the suexec mechanism of Apache (or comparable for other webservers) to make it execute the CGI script in the context of said user or group.

Re: Mysql-CGI Security Question
by Rhandom (Curate) on Aug 04, 2011 at 13:49 UTC
    This is a fairly common question, and an excellent one.

    The short answer is, there is not a good solution to this problem. Your script has to run as the user, therefore the user has to be able to see the password. Try to make sure the file containing the password is as restrictive in read permissions as possible.

    There is only one other semi-viable option. Use a custom DBD::Proxy based service which uses the password (which is different from the databases password) AND checksums the passed in sql based on IP address, or optionally uses named sql queries. However, this option only restricts what they can run to the dictionary of available queries. It doesn't stop somebody else from running those same queries.

    my @a=qw(random brilliant braindead); print $a[rand(@a)];

      you said "Try to make sure the file containing the password is as restrictive in read permissions as possible." does that mean the user logs in to each page over and over using that stored password? is that how you make sure the person doesnt by pass the login page and go directly to down stream scripts?

Re: Mysql-CGI Security Question
by davido (Cardinal) on Aug 04, 2011 at 17:03 UTC

    You don't need to be granting MySQL root privileges to CGI scripts. Within MySQL set up a user who has only the permissions necessary to do what your CGI script needs to do. Then make sure your tables are set up to grant only as much access to that user as necessary. For example, maybe table "logins" grants read/write/lock access to your cgi-user, but not alter table, etc. And maybe table "lotsofdata" only grants read access to your cgi-user account, if the CGI never needs to update that table.

    You also make sure that cgi-user's login topological-scope is as narrow as possible. If the database resides on the same machine as the webserver then you can restrict logins for cgi-user to only localhost. If the database resides on a different computer, restrict logins for cgi-user to just that IP. This is within the cgi-user's setup in MySQL. Your CGI script and webserver need to allow visitors from just about everywhere (presumably), but the script's login to the database can be much narrower.

    Then, of course, use best practices with respect to placeholders, taint mode, and server configuration such that the client is never able to send bad characters that would inject malicious content/code into your database, or allow them to see your actual code.


Re: Mysql-CGI Security Question
by scorpio17 (Canon) on Aug 04, 2011 at 18:24 UTC

    I recommend the following: create a mysql user named "webuser", with a good password. Put the information you need to give to DBI->connect() into a config file (username, password, host, database, etc.). Make sure that this config file is readable ONLY by the user that the web server runs as. If you use the apache web server, you can change this by editing the apache config file. For example, if it's set to 'apache', then any file created by a cgi script will be owned by 'apache' - so make your database config file readable by only the 'apache' user:

    chown apache:apache config.ini chmod 600 config.ini

    To be more specific about the mysql setup, let's say you plan to use a database called 'webstuff'. Run mysql as the root user:

    mysql -uroot -p
    Then enter something like this:
    create database webstuff; grant all on webstuff.* to webuser@localhost identified by 'secret_pas +sword';

    This assumes that your database server is running on the same machine as your web server - else you might have to specify something other that localhost. To be more secure, you could take care to grant only those privileges that you really need, rather than "all". For example, if your web app never has to drop (delete) a table, don't give it the ability to do so. Then, if a bug in a cgi script allows a mysql injection attack, even if someone tries to submit a "drop table" command, it won't actually do anything.

    Also, note that webstuff.* means that the privileges apply to every table in that database. If necessary, you specify different privileges on a table-by-table basis. Just because you need to allow updates on 1 or 2 tables, you don't have to allow updates to ALL tables - keep a few "read only" if you need to.

    Once created, you can connect like this:

    mysql -uwebuser -psecret_password webstuff

    Always make sure you can run your queries manually, from the mysql prompt, before coding them up inside a cgi script. I find this makes debugging easier.

    Another trick you can use is to create a "read only" user, a user with limited write privileges, and a user that can do anything (grant all). Then you can write your cgi scripts so that unauthenticated users are in "read only" mode, users that have logged in may have permission to do limited updates, and only a special admin user has permission to do things like drop tables, etc.

    I guess the main point is that you NEVER let a cgi script run as the mysql root user. You don't need multiple mysql users in a cgi environment - just one will work fine. You might have hundreds of users on your website, but when they login they each have to authenticate using the same username/password table. NEVER store a password in a cookie. A cookie should be used to store a unique session ID number. Use that to look up a "state" (logged in or not) stored in a session table in your database.

    Read the docs for CGI::Application, and CGI::Session. Also, some time ago I started a tutorial on how to use CGI::Application to create a simple login page. It never made it past the "request for comments" stage, but you can still find it here.

    Good luck!

Re: Mysql-CGI Security Question
by sundialsvc4 (Abbot) on Aug 04, 2011 at 23:02 UTC

    I always set up web-site systems ... those which cannot offload the entire “authentication question” to something like LDAP ... to initially connect to the database using a userid/password that gives only read-only permission to the authentication table.   Once the user is authenticated, the web-site software might choose to re-authenticate itself using one of several userid/password combinations, but none of them are rootly.   The idea, simply, is “the principle of least privilege.”   Each of the several tasks which the web-site might do are compartmentalized, and while performing each of those functions the web-process has only the privileges which it requires to do that job ... and no more.

      I work similarly -- I have at least three classes of mysql logins:

      • Unauthenticated -- only has read access to the public tables
      • Authenticated -- may also have read to user-level tables, and possibly write or insert to tables where appropriate (I don't always use this one, as my stuff tends to be more information push in nature)
      • Web Administrator -- just for authenticated administrators, may be allowed to insert / delete / update to perform administrative tasks
      • Local Admin -- not used by the CGI, but for interactive use ... may be allowed bulk data loads (FILE), truncate, create / alter tables, etc, with that application's schema.

      Rootly powers (eg, ability to modify the mysql schema) are never given to CGI scripts, nor are any powers that the web user wouldn't need to do. In some cases, the public facing webserver doesn't actually have valid webadmin credentials -- those are only on a separate virtual host that's restricted to connections from specific IPs.

      I also don't store authenticaton information within the database ... if I were going to, I'd likely separate read & write of passwords out to two separate roles, to reduce the ability of injection to obtain the passwords. If I had more roles that needed different permissions, I'd create seperate mysql logins for them, too.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://918523]
Approved by Corion
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (8)
As of 2021-01-17 23:14 GMT
Find Nodes?
    Voting Booth?