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

Re: Basics: CGI MySQL security

by blue_cowdawg (Monsignor)
on Oct 08, 2006 at 23:50 UTC ( #577049=note: print w/replies, xml ) Need Help??

in reply to Basics: CGI MySQL security

      I have a website that allows viewing and modifying of a MySQL database using PERL CGI scripts and I haven't a clue how to do the security.

Yikes! That statement scares me! My first reaction to that statement is if you don't have a clue then learn about it before you create the website.

If I had to solve this problem though, here is one approach I'd consider.

Consider a "roles based" security system. What I mean by that is this:

  • Anonyomous user. IE a user not logged in.
  • Logged in user
    • Database(s) allowed to access
    • Table(s) allowed to access

There are many ways to implement a roles based system. The approach that I quite often use is to create a set of tables (outside of what users anonymous and otherwise have access to) such as:

create table users ( user_id integer not null primary key, login text, passwd text ); create table role_types ( role_type_id integer not null primary key, role_type text ); create table role_map ( role_mapping_id integer not null primary key, role_type_id integer not null references role_types(role_type_id) on delete cascade, user_id integer not null references users(user_id) on delete cascade );

Using that schema you now have a way to map users to one or more roles that user can have. Keep your business logic of how those roles map to access in your CGI script (for now..)

That's the first half of it. You now have a table that can be searched to authenticate users, you have a table that defines roles, and you have a mapping table that maps users to their roles. The next step is to track sessions and who owns the session:

create table sessions ( session_entry_id integer not null primary key, session_id text, session_hack timestamp ); -- Note, session_id is what is used to track the session -- and session_entry_id is used for FK mapping between -- tables create table session_ownership ( session_ownership_id integer not null primary key, session_id integer not null references sessions(session_entry_id) on delete cascade, user_id integer not null references users(user_id) on delete cascade ); create table session_vars ( session_var_id integer not null primary key, var_key text not null, var_value text );

Note: the primary keys are of type integer, but they could be other types. In most cases I'd use either an auto_increment or a sequence depending on if I were coding this for MySQL or PostgreSQL.

In the schema I've shown, you have the ability to persist values for sessions between forms within a session instead of using hidden values. Your session_id should be a string of random (unpredictable) characters of sufficient length to make guessing if not impossible at least too difficult to be worth it. I'm mot going into algorithms for that in this post.

Lastly, in your CGI code you need to provide logic to once in a while (or even every time it is accessed) of visiting the values in the sessions table and deleting rows of sessions that have timed out. Meaning you need to decide how long a session can be idle before you delete the session from the table. In your CGI logic you also need to visit this table between accesses to the CGI to make sure a session is still valid. If it isn't you bounce the user back to the login screen and make them log in again. Optionally you can tell them their session has expired if your nice. :-)

Now.... mapping your roles to the database(s) and/or table(s) they are allowed to access can either be done with hard coded logic (not the best way IMHO) or via more join tables within your schema. I leave that up to your creativity to figure out from here...

Peter L. Berghold -- Unix Professional
Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg

Replies are listed 'Best First'.
Re^2: Basics: CGI MySQL security
by jfrm (Monk) on Oct 12, 2006 at 21:13 UTC

    Don't worry - the data isn't that precious and hardly any of it is there yet, anyway. I'm just doing proof of concepts right now

    Yes, the roles idea has to be used, thanks - also it sounds like you are suggesting that I use the database itself to store users and roles; hadn't thought of that - sounds good.

    I'd like to KISS so would like to avoid doing the authentication myself - having read some more following the ideas in this thread, I think I'm happy with basic http authentication

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://577049]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (2)
As of 2022-06-27 05:25 GMT
Find Nodes?
    Voting Booth?
    My most frequent journeys are powered by:

    Results (86 votes). Check out past polls.