Setting up safer access to the MySQL database

By default, OSCAR uses root mysql access for all activities. This is not the most sensible approach, as simple programming errors or, when accessing the database directly, simple typos can wreak havok with your data.

It is important to be very, very careful with the root login to mysql as the root user can do anything - you don't want to accidentally delete your whole database when you only meant to ask how many users you have!

Clearly, it is important to make sure you have a regular archived backups of your database (not just incrementals, as you don't want to delete in the backup what you accidentally deleted in the live database).

However, I also strongly recommend that you set up 'read only', 'read and write only' and 'Admin' MySQL logins which you can then use more safely when writing scripts or accessing the database manually via MySQL.

First, log into your OSCAR server via SSH (eg: from a terminal app type ssh yourusername@yourserver.address).

Then log into MySQL as an administrator (eg: mysql -uroot -p). You will need the MySQL password.

Then grant the necessary privileges to your two new users:

mysql> GRANT select ON oscar_mcmaster.* TO OscarRead@localhost IDENTIFIED BY '[password1]';

mysql> GRANT select, update, insert, delete ON oscar_mcmaster.* TO OscarWrite@localhost IDENTIFIED BY '[password2]';

mysql> GRANT all ON oscar_mcmaster.* TO OscarAdmin@localhost IDENTIFIED BY '[password3]';
Now log out as root from MySQL:

mysql> exit

and log in again as the read only user:

mysql -uOscarRead -p

When you want to write to the database, log out and log in again as OscarWrite:

mysql> exit

mysql -uOscarWrite -p


I recommend using an alias in your .bash_profile on the server to make access to the OSCAR database simpler:

exit MySQL:

mysql> exit

and open up .bash_profile for editing:

vi ~/.bash_profile

add the following lines (scroll down to the bottom of the file and type 'i' to enter input mode)

alias sqloscar='mysql -u OscarRead -p[password1] oscar_mcmaster -A'
alias sqloscarwrite='mysql -u OscarWrite -p oscar_mcmaster -A'
alias sqloscaradmin='mysql -u OscarAdmin -p oscar_mcmaster -A'

Exit input more (hit 'esc' key) and save and quit:

:wq

You can now simply type 'sqloscar', 'sqloscarwrite', and 'sqloscaradmin' from the server command line to access the three levels of MySQL security.

As soon as you have completed the write (or admin) task, log out of MySQL and log back in again as the read-only user. This minimizes the risk of typo-disasters.

Important Note: if you're bash_profile can be accessed by other users on the system that should not have read access to the database, then don't include password1 in the .bash_profile.

OSCAR Mods: Tweaking the appointment status settings

OSCAR uses appointment statuses (?stati) to keep track of the patient's progress through the appointment system.

As the appointment icon is clicked on the appointment screen, it advances one status (eg: from 'booked' to 'chart pulled'). The OSCAR manual shows how to make some changes to this setup using the admin section of the EMR, however it is also possible to make more changes.

First, log into your OSCAR server via SSH (eg: from a terminal app type ssh yourusername@yourserver.address).

Then log into MySQL as an administrator (eg: mysql -uroot -p). You will need the MySQL password.

You need to be very, very careful with the root login to mysql as the root user can do anything, so you might accidentally delete your database. Make sure you have a backup of your database before proceeding, or better still set up 'read only' and 'read and write only' MySQL logins which you can then use more safely.

From the MySQL command, select the oscar_mcmaster database:

mysql> use oscar_mcmaster;

You can show a list of appointment status settings by typing:

mysql> select * from appointment_status;

+----+--------+--------------+---------+--------------+--------+----------+
| id | status | description .| color . | icon ........| active | editable |
+----+--------+--------------+---------+--------------+--------+----------+
| 1 .| t .....| Booked ......| #FDFEC7 | starbill.gif | 1 .....| 0 .......|
| 2 .| T .....| Chart Pulled | #FDFEC7 | todo.gif ... | 0 .....| 0 .......|
| 3 .| H .....| Arrived .....| #00ee00 | here.gif ... | 1 .....| 1 .......|
| 4 .| P .....| Being Seen . | #FFBBFF | picked.gif ..| 1 .....| 1 .......|
| 5 .| E .....| Left ........| #FFFF33 | empty.gif ...| 1 .....| 1 .......|
| 11 | N .....| No Show .... | #999999 | noshow.gif . | 1 .....| 0 .......|
| 12 | C .....| Canceled ... | #999999 | cancel.gif ..| 1 .....| 0 .......|
| 13 | B .....| Billed ..... | #3ea4e1 | billed.gif ..| 0 .....| 0 .......|
| 6 .| a .....| Customized 1 | #897DF8 | 1.gif ...... | 0 .....| 1 .......|
| 7 .| b .....| Customized 2 | #897DF8 | 2.gif .......| 0 .....| 1 .......|
| 8 .| c .....| Customized 3 | #897DF8 | 3.gif ...... | 0 .....| 1 .......|
| 9 .| d .....| Customized 4 | #897DF8 | 4.gif .......| 0 .....| 1 .......|
| 10 | e .....| Customized 5 | #897DF8 | 5.gif .......| 0 .....| 1....... |
+----+--------+--------------+---------+--------------+--------+----------+

If you want to stop using a particular status in OSCAR (eg: skip the 'chart pulled' status, which can't be done from the admin screen in OSCAR), then - from a read-and-write MySQL login - type:

mysql> update appointment_status set active='0' where id=2;

If you would like to make all items editable in the Admin screen, then - from a read-and-write MySQL login - type:

mysql> update appointment_status set editable='1';

If you would like to add a new status line, then - from a read-and-write MySQL login - type:

mysql> insert into appointment_status set
id='14',
status='z',
description='New Status',
color='yellow',
icon='someicon.jpg',
active='1',
editable='1';

When you are finished exit MySQL:

mysql> exit

Have fun!

Waiting Room Display

I've set up a display for the waiting room in my clinic which will show patients the current waiting time for each doctor/nurse consulting that day in the clinic.

The clinic details, waiting times, and messages of the day, are displayed on a web page that can be accessed from anywhere.  (You can view our page here).

The 20" display is wall mounted and linked to a computer in our reception which displays the web page.  (The computer is a dumb terminal that just has firefox running - so no security concerns).

The webpage is served from a separate server (ie: not the one that runs our OSCAR EMR).   The OSCAR server runs a perl script every minute which sends a data file to the web server (a one way SSH key based login allows one way traffic from OSCAR to the server without comprimising security - much as MyOSCAR does).

This data file is then called by the web pages to display the names of the providers with booked appointments for that day, and whether those providers are running late or to time. The perl script and website auto-update every minute.

This is a very useful feature for patients and staff as the patients now have a good idea of how long their wait will be and can plan accordingly, and the staff don't get pestered with requests and 'evil eye' stares from punters that are in the dark about how long they are going to have to wait.

You can download the script here and the html for the webpage here - details of how to install the script on an Ubuntu machine are included in its comments section at the start of the file.

You can use a VGA splitter (or VGA twin cable if you can keep the distance short) to feed a second monitor cable through the wall to your waiting area and wall mount the monitor (or try one of these devices to run the monitor signal over your existing ethernet/cat5 cable system). You can then start up firefox when you open up in the morning (hit F11 to make it fullscreen), and close it again when you finish.




(Updated 10th Oct 2009 to new version)