web stats
SQL Server permissions - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 05-25-2015, 03:10 PM
Tom P Tom P is offline
What's HL7?
 
Join Date: Sep 2011
Posts: 5
Tom P is on a distinguished road
Default SQL Server permissions

I would like to run Mirth 3.2.1.7650.

In my environment the DBA group will not allow the user that Mirth uses to have dbo permissions. The only permissions that Mirth and I are allow to have are database_reader and database_writer and execute for stored procedures and functions. I am allowed to submit scripts to the DBA group to be run against production to do prety much anything.

I do have full DBA authority in my test environment. So I can startup mirth with full dbo authority in test, let Mirth run the create scripts. I would then create and test my channels in test. Then ask the dba group to backup test and restore to production. Mirth will then be allowed to run in production with R&W permissions.

1) Are there any know issues with running Mirth with only Read & Write permissions?

2) I noticed that each channel that get created has a set of tables that gets created. Is it possible to script these tables.

3) Assuming that Mirth is only used to send & receive messages. After the initial startup, does Mirth ever need permission other than read & write?

4) I would think that since HIPAA(in the US) this is a common problem in the US. Is there any type of guide for running Mirth with restricted permissions.

Thanks,

Tom
Reply With Quote
  #2  
Old 05-26-2015, 08:20 AM
brentm brentm is offline
Mirth Employee
 
Join Date: Jan 2012
Posts: 85
brentm is on a distinguished road
Default

Quote:
1) Are there any know issues with running Mirth with only Read & Write permissions?
With only read/write permissions, at least the following will not work:
  • Mirth Connect will not be able to initialize the schema.
  • You cannot create or delete channels.
  • The 'remove all messages' action will not work since it performs a TRUNCATE which requires the ALTER permission
  • You cannot add or remove metadata columns from a channel (ALTER)

Mirth Connect requires full admin privileges to the database including CREATE, ALTER, READ, WRITE. There may be additional issues that you'll run into other than the ones mentioned. Also, other issues may appear in future versions of Mirth Connect since this is not supported.

Quote:
2) I noticed that each channel that get created has a set of tables that gets created. Is it possible to script these tables.
I'm not sure what you mean by "script" the tables, can you elaborate?

Quote:
3) Assuming that Mirth is only used to send & receive messages. After the initial startup, does Mirth ever need permission other than read & write?
As far as I know, the process of sending and receiving messages only requires read and write permission. Creating/deleting channels, removing all messages from a channel, adding/removing channel metadata columns requires CREATE and/or ALTER. We haven't tested Mirth Connect with only create/read permissions and we don't support it, so I can't guarantee there aren't other issues that you'll run into.

Quote:
4) I would think that since HIPAA(in the US) this is a common problem in the US. Is there any type of guide for running Mirth with restricted permissions.
I'm not sure how restricting CREATE and ALTER would add any significant protection for HIPAA.

What's probably more important is that Mirth Connect's user account in SQL Server is restricted to only it's database and that the Mirth Connect database is dedicated for use by Mirth Connect and no other purposes.
Reply With Quote
  #3  
Old 05-28-2015, 06:04 AM
Tom P Tom P is offline
What's HL7?
 
Join Date: Sep 2011
Posts: 5
Tom P is on a distinguished road
Default

First off I really want to thank you for your response. The reason that I and the user Mirth runs under only have read/write permission is to enforce the institutions rules on change control. Changes cannot be made by users without approval of the change control board. So when I want to add a column to a table, I add the column in MS SQL Server Studio Manager on my test server and save the change as script. This writes the alter table statement to a text file. I then request a change control with the script attached and a rollback procedure, if approved, a DBA runs the script. Why do we do this We didn't once - it didn't work out real well(long $tory).

In SQL Server it's possible to save a table's schema to a script so the table can be deployed on other database.

Our data flow in Mirth, I think is a simple as it comes. We're receiving HL7 messages, inserting the entire, unaltered, message into a database, and sending an ACK. My Data Pruner is set to prune daily at 1:00 am. At some point we will be adding outbound messages. In that case we will getting HL7 messages from SQL Server and transmitting them unaltered.

1. It looks like, I can alter channels with only read/write. I just can't create them?
2. In your reply to my question 3 you stated that I will not be able to add/remove channel metadata columns. You're referring to custom metadata columns correct? I'm not using any of these.
3. Also in you reply to question 3, you state that I will not be able to remove all messages. Currently, will only having read/write permissions impact pruning?

Thanks again for your help,

Tom
Reply With Quote
  #4  
Old 05-28-2015, 10:34 AM
brentm brentm is offline
Mirth Employee
 
Join Date: Jan 2012
Posts: 85
brentm is on a distinguished road
Default

Quote:
1. It looks like, I can alter channels with only read/write. I just can't create them?
Yes, editing channels should still work.

Quote:
2. In your reply to my question 3 you stated that I will not be able to add/remove channel metadata columns. You're referring to custom metadata columns correct? I'm not using any of these.
That's correct.

Quote:
3. Also in you reply to question 3, you state that I will not be able to remove all messages. Currently, will only having read/write permissions impact pruning?
Pruning should still work since it only makes use of DELETE statements in SQL Server Deleting individual messages or search results in the message browser should also work. What won't work specifically is the "Remove All Messages" action that appears when you right-click on a channel in the dashboard or in the message browser tasks panel, since that uses a TRUNCATE command.
Reply With Quote
  #5  
Old 05-29-2015, 04:59 AM
Tom P Tom P is offline
What's HL7?
 
Join Date: Sep 2011
Posts: 5
Tom P is on a distinguished road
Default

The last time I used remove all messages was in a panic. I didn't know about pruning and my database ran out of space. So I was deleting anything and everything I could in an effort to get inbound messages flowing again. I know that's not a great way to do things. But it all worked out.

Having said that. Does remove all messages effectively do the same thing as pruning without the where clause? Pruning using delete and remove all use truncate?

Thanks again,

Tom
Reply With Quote
  #6  
Old 05-29-2015, 07:07 AM
brentm brentm is offline
Mirth Employee
 
Join Date: Jan 2012
Posts: 85
brentm is on a distinguished road
Default

Quote:
Does remove all messages effectively do the same thing as pruning without the where clause? Pruning using delete and remove all use truncate?
Removing all messages will wipe out all messages with no where clause, that's correct. Pruning allows for various conditions and can also optionally delete only from the message content tables.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 03:51 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation