Saturday, November 19, 2005

Secure everything!

Throwing a firewall between your precious database server and the outside world or the internet is a good first step. Unfortunately many firms literally never get beyond this. It would be like putting a lock on the front an back door, but not on the president's door, the controller's door or the company safe. This leaves database information unsafe and vulnerable to anyone with access to the "hallways" or your company's network, that is, vulnerable to the "inside job".

If I can "see" the start-up script, I can copy it somewhere, edit it and run it, find out what files I have access to and begin dumping them. There is a simple protection against this, but rarely have I seen it done because of the extra layer of administration involved.

Do it, do it, do it - you will not regret it one bit. My next entry will give you the steps.

Thursday, November 17, 2005

Password complexity and compliance

To comply with HIPAA, Sarbanes-Oxley and other compliance authorities, some ERP packages, especially older ones, must be modified. A client of mine has had changes inserted in the code for "mf1a.p". This all appears after the statement: update global_userid passwd with frame welcome. They call procedures which track attempts and that supersedes the date logic which forces changes that are much stricter.

This is a really good idea and they use external program calls to encapsulate the logic and so as not to clutter the login screen code.

Wednesday, November 16, 2005

Compound measures, not redundant

The different steps taken in securing a database can be seen as sometimes compound, but they should not be redundant. Redundancy could be symbolized by two locks on the same door - what if they pry it open from the hinges? But how about one lock and reinforce the hinges, or one lock and an alarm? That would represent compound measures.

Recently someone asked me about adding an operating system group level for ownership of sensitive financial information at the R-code level. Obviously that's a good idea, but if you can keep them out of the editor of the database and you lock up the usr_mstr then this could represent a redundancy requiring extra resources. I almost think that it would be better to "trap" someone running a compiled procedure that they weren't supposed to. My favorite way would be via an email script embedded in every sensitive program, but that's just me. The -yx parameter can be used as well but it's useless in the event of ID spoofing.

Tuesday, November 15, 2005

Create Progress users from QAD file

Fortunately the same encoding is user in QAD and Progress passwords. So you can do the following after the QAD set up is complete.


for each usr_mstr no-lock:
  find _user no-lock where _user._userid = usr_userid no-error.
  if avail _user then next.
  create _user.assign
    _user._user-name = usr_name
    _user._userid = usr_userid
    _user._pass = usr_passwd.
end.


Then you'll still have to use the dictionary admin menu to assign security permissions, etc. All this would really do is prevent an "unknown" user from being able to compile programs which look at tables; it does nothing to secure the application programs. This would be suitable for small shops possibly, but the assumption would be that of no malevolence, usually not good. I believe a better tack would be to only set up developers in this table.

Single-user access depends on UNIX attributes

Connecting to a database in multi-user mode is independent of the ownership and permission mode of the database file(s). However if there is no server process running and an attempt is made to connect in single-user mode by a user who does not own the database file(s), the attempt to connect will fail unless the read/write permissions are open ("-rw-rw-rw-"). So use the command:

$ chmod 666 [dbname].??

before attempting a connect with -1.

Sunday, November 13, 2005

Lock down the DBs

Three things you simply have to do even if you protect, move or remove all the powerful and dangerous programs.

  1. Add at least one user. Use the dictionary program, then go to Admin/Security/Edit User List.
  2. Choose one user who can change security (Admin/Security/Security Administrators).
  3. Disallow blank userids. Go to (Admin/Security/Disallow Blank Userid Access...) - this will still allow entrance to the Procedure Editor to blanks, but will keep them from being able to compile any code which references the tables in the secured database.

Realize that doing all these things won't affect the functionality of a properly configured database client session providing all the application code is compiled. In other words, the "-p mf.p" of a QAD client script will keep the Progress DB security window from even appearing.

Lock users out of the Progress editor

In QAD, to prevent users from either executing the editor program (_edit.p) or escaping to the editor (F4 / P from main menu) there are 2 records which should be modified. Here's a program to help accomplish this:

for each mnd_det where (mnd_nbr = "" and mnd_sel = 1) or
  (mnd_nbr = "36.24" and mnd_sel = 3) with no-box:
  display mnd_nbr format "x(6)" mnd_select
    mnd_label format "x(18)".
  update mnd_canrun format "x(50)".
end.

Output will resemble this when complete:

Menu Sel Selection Label Access codes
------ --- ------------------ --------------------------------------
         1 Progress Editor    admin1,developer1,developer2
36.24    3 Program Execute    admin1,developer1,developer2

Wednesday, November 09, 2005

Move edit and dict programs

To eliminate the hacker threat mentioned earlier you can always use -rq in the startup script for Mfg/Pro. It would be really nice to run it with -rr, but it won't work out of the box. I'm not exactly sure why yet, however it's possible that it's hitting a source snag somewhere. The error message is different though...investigating...

The work-around I have at this stage is just putting the Editor and Data Dictionary programs out of arms reach. The programs to move out of their natural habitat (in UNIX, probably $DLC/tty/_edit.r and $DLC/tty/_dict.r) into a place that only your developers have access to, also renaming them. You could just change the mode on them, but make sure to get rid of read permissions as well; I'd make them 440 - period. I would advise creating a qad and a qaddev group - developers would be members of both groups, users would be members of qad only.

Tuesday, November 08, 2005

Master File Auditing limitations

OK, it's definite. The master file audit system in QAD does not track changes made outside the menus. So if I get into a Procedure Editor that can compile I can do something like this:

find ad_mstr where ad_addr = "C00100A2".
assign ad_city = "Pittsburgh" ad_state = "PA".


...and it will never show up in the master file audit system. Likewise:

find mnd_det where mnd_nbr = "36" and mnd_select = "3" no-lock
  no-error.
create usr_userid.
assign

  usr_userid = "me"
  usr_passwd = encode("me")
  usr_lang = "us"
  usr_groups = if avail mnd_det then mnd_canrun
               else "IS,IT,MANAGER".


...will allow me to get into the system as "me". But a really suave hacker would scan the usr_mstr table and decided who he wanted to spoof and write down the 16 letter encrypted usr_passwd. Then he'd change it, log in, do his dirty work and switch back afterward.

This is another reason to lock down the Procedure Editor.