Is it possible to grant update/insert privilege to any user who clicks the UI action button?

Kayes Kaykobad

First of all,  sql statements in UI model should be avoided if possible according to this post:
https://www.bbdevnetwork.com/qa/45780/grant-permission-to-tvf-sql-function-for-custom-ui-model


I created an SP based extension for the Modeling and "Propensity Attribute Edit Form 2" form. I also added a UI action button through edit form extension spec and created a UI model from the extension spec. I wrote an insert statement in the invoke action event for the button but got the error below:


"Cannot find the object 'USR_MU_MODELSCOREEXTENSION', because it does not exist or you do not have permission."


Here is the code below:


Private Sub SaveExtention_InvokeAction(ByVal sender As Object, ByVal e As UIModeling.Core.InvokeActionEventArgs) Handles _saveext.InvokeAction

        Using con As SqlClient.SqlConnection = GetRequestContext.OpenAppDBConnection() 'RequestContext.OpenAppDBConnection()

            Using command As SqlClient.SqlCommand = con.CreateCommand()

                command.CommandText = " Grant insert on dbo.USR_MU_MODELSCOREEXTENSION to [my username]"

                command.ExecuteScalar.ToString()



                command.CommandText = "insert into dbo.USR_MU_MODELSCOREEXTENSION(ID, SOURCE, AUTHOR, ADDEDBYID, CHANGEDBYID)

                                                values ('19A501D9-B349-4055-BDBF-C0E17F34743A', 'source code d', 'test', 'C5874A28-5F46-42BD-895A-33E0B8DD1ADD', 'C5874A28-5F46-42BD-895A-33E0B8DD1ADD' )"

                command.ExecuteScalar.ToString()

            End Using

        End Using

    End Sub

Michael Simion

I believe what Chris was referring to was granting your user permissions on that table outside of your UI Model (i.e. through SSMS or your DBA). You shouldn't have to grant permission each time you invoke the action.


Secondly can you confirm via SSMS that your table exists and that the insert statement you've got works? If you connect to the DB using the same user you access CRM with, you can isolate whether this is a permissions issue.


Also, while I realize this could be some sample code to test, I'd recommend you to use a store procedure for the insert or at least parameterized queries in order to mitigate any sort of SQL injection.

Kayes Kaykobad

I have not tried to grant permission from SSMS but I noticed that in SSMS my account had the permission required to insert/update. The reason I granted permission in the UI model code is, I was planning to revoke permissions after the insert statement thinking, nobody would have permission to insert/update while not using the form. But this thought is probably flawed because the invoke action code in the UI model is going to be executed by a user and if any user could write a grant statement to insert/update it certainly would not be secure.


Yes, I've checked in SSMS the table exists and the same statement runs in SSMS.


Also, while I realize this could be some sample code to test, I'd recommend you to use a store procedure for the insert or at least parameterized queries in order to mitigate any sort of SQL injection. -> I totally agree with you. I was working with a dynamic query and I was not able to write a function that returns the ID that I need probably because there are limitations in what can be done with dynamic queries inside of a function. I also tried writing a  stored procedure but it did not work for me. If I spend a little more time I think I'll be able to do this.


Thanks for the questions and suggestions.

Kayes Kaykobad

I ran the following grant statement in SSMS:

Grant insert on dbo.USR_MU_MODELSCOREEXTENSION to [my username].


I also tried:

Grant insert on dbo.USR_MU_MODELSCOREEXTENSION to public


It did not work.

 

Joseph Styons

Hi Kayes,

If you are self-hosted, then the answer is probably 'yes, it is possible'.  But it isn't a good idea.

If you want your UIModel code to insert data, the "right" way to do it is to take two steps:

1) Create an add form that does the insert for you (it can be ugly, nobody ever has to see it)

2) Call that add form as an endpoint from your UIModel code.


For part 2, the below snippet may be helpful.

Just change the data form id and field names, and it should work for you.

    Private Sub SaveSomeData()
        'Example: create a new constituent with a name of 'Generic Person'
        Dim request As New Blackbaud.AppFx.Server.DataFormSaveRequest()
        With request
            .FormID = New Guid("1f9671b3-6740-447c-ad15-ef2718c0e43a") 'this is the OOB form called "Individual, Spouse, Business Add Form"
            request.DataFormItem = New Blackbaud.AppFx.XmlTypes.DataForms.DataFormItem()
            request.DataFormItem.Values.Add("FIRSTNAME", "Generic")
            request.DataFormItem.Values.Add("LASTNAME", "Person")
        End With
        Dim reply = Blackbaud.AppFx.Server.DataFormSave(request, GetRequestContext())
        Dim createdRecordID = reply.ID 'this will be the ID of the record that the add form has created for you
    End Sub

Joseph Styons

https://www.styonssoftware.com

Kayes Kaykobad

That's exactly how I solved this. Hope this helps someone.