By using this website, you agree to our Terms of Use (click here)
It might be nice to have an AugSQL configuration screen that allows you to configure which commands are available (SELECT, UPDATE, INSERT, DELETE, etc.) to give you the opportunity to prevent things like DELETE and DROP statements.
Great idea, I think this would make me much more comfortable allowing this great power tool to end-users.
@robertsternberg Roger that. This has been the most frequently requested feature so far.
An idea that @AugSQL had on this was to somehow use the regular Acumatica security permissions of Insert, Delete, Edit, etc.
Since there are more SQL commands than there are Acumatica security permissions, there would need to be a way to map what SQL commands someone has access to depending on what Acumatica security permission they have.
This might be a little unconventional, but what if we had an AugSQL Preferences screen that had a section for each of the Acumatica security permission names (Access Rights), then a bunch of checkboxes underneath each section for each of the SQL command names. Then you could assign which SQL commands someone is allowed to run depending on what Acumatica security permissions they have.
The unconventional part would be that each Acumatica security permission would be treated independently whereas normally they build on each other.
I think managing by license would be convenient. 2 types of licenses would be good, like Reporting & Developer. Reporting being select only and Developer being all access.
I'd be comfortable adding a Reporting tool everywhere and controlling it with permissions but I think the full version would make customers shy away from it when you review the ease of the editing the database. I know you can do it now through a customization but I don't think most business people understand just how easy it is.
@amassey I like your "2 type" suggestion. That's a lot simpler than my complicated idea.
By "license type", are you suggesting a way to restrict what AugSQL can do outside of using Acumatica security or would it be ok to use Acumatica security to enforce the "2 types of licenses"?
I am leaning towards @amassey idea. I think this might have to end up as SELECT(Reporting) and EVERYTHING ELSE(Developer/Admin).
It is very difficult to make a SQL app that covers all scenarios. For instance, let's say a user is set to restricted for DELETE statements. Can they bypass that by using TRUNCATE or DROP? What about if they use a stored procedure?
If a user was set to only allow SELECTs then you might be able to modify the SELECT button in the customization to start a READ ONLY transaction and add some protection from that standpoint. As soon as you move outside of a READ ONLY transaction all bets are off.
This approach also allows you to use native Acumatica security and allow users permission's to be set on the button level of the form SELECT/EXEC. A popup could appear confirming you would like to run the EXEC (not READ ONLY) command when the button is selected, this could be an optional popup configured in in AUG SQL preferences.
@robertsternberg Good point about TRUNCATE, DROP, and Stored Procedures. It does indeed get very difficult when you get into the details.
For security, it might be simpler to put the security into the screen so you don't have to get into Acumatica field level security. Something like this:
1. If you only have View Only permissions on the AugSQL screen, then only the SELECT button appears on the screen.
2. If you have any permissions above View Only, then both the SELECT and EXEC buttons appear on the AugSQL screen.
What do you think?
Regarding the pop-up, I can see how this could get annoying to see if everytime so I like your idea to have it as a preferences option.
@timrodman I like the approach and it fits with Acumatica's security philosophy which makes it significantly more manageable for end-users.
My only thought is if there is a user with Edit level Acumatica permissions runs EXEC on a DELETE statement, it might be difficult for the security administrator to explain/understand.
I would like to see others provide feedback however, it might be a small tradeoff to reserve EXEC functionality to users with Delete level permissions on the screen.
@robertsternberg We could take a conservative approach and only enable EXEC for users with Delete Access Rights. All other Access Rights would only get the SELECT button.
I just updated this to Idea - Planned (click here)
Our current goal is to only allow you to run SELECT statements when you have Access Rights of View Only, Edit, or Insert on the AugSQL (QQ900001) screen, but you can run any SQL that you want if you have Access Rights of Delete on the AugSQL (QQ900001) screen.