10 Replies Latest reply on Mar 21, 2019 8:38 PM by shawkins

    Security in Stored Procedures

    cjohn001

      Hello together,

      I have a question regarding security in stored procedures. I have written a stored procedure which should update some configuration values of a user. Let"s assume the following example. I have a table with configuration records. Each user has 7 records for each day of the week in this table which has a column param that can take the value A or B. Hence the table looks something like

       

      id, userid, dayOfWeek, param

      0      1          0                    A

      1      1          1                    B

      2      1          2                    B

      ...

      7      2          0                    B

      ...

       

      now the stored procedure should take the param value of a given day from the user and change the values of param for the user days to the value of the selected day. I want ensure that a user now can only change his records and not call the procedure to change records of other users.

       

      I am using Teiid together with keycloak, which allows me for tables to use row based security constraints. For table access something like the following results in a where clause that constrains access to the logged in user()

       

      ConfigOptions.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36))

       

      No my first question is

      1.How can I reach the same for a stored procedure?

      2. Morover, I am wondering what I can configure regarding security in the model view of teiid designer. For my stored procedure called "ApplyMealConfigToEntireWeek" I can set constraints for CREATE, READ, WRITE, DELETE (see attached screenshot). I am wondering what this denotes in presence of a stored procedure. Would a disallowed WRITE for example denote that the stored procedure should not be allowed to do any changes in the database? It would be great if you could explain me how security is meant to be configured for stored procedures. Thanks for your help.

       

      Best regards,

      Christoph

        • 1. Re: Security in Stored Procedures
          rareddy

          Stored procedures can be executed or not. If you are writing a virtual stored procedure, you can still use user checking logic you showed above to control your updates.

          • 2. Re: Security in Stored Procedures
            cjohn001

            Hello Ramesh,

             

            thanks for the feedback. With a virtual stored procedure you mean some kind of procedure which is living in the vdb? So native store procedures on the mysql database or not a source to which I can apply access rules? Would you have an example how I can create such a procedure in Teiid Designer?

            • 3. Re: Security in Stored Procedures
              rareddy

              > you mean some kind of procedure which is living in the vdb?

              yes, Virtual Procedures [1] and [2]

               

              > So native store procedures on the mysql database or not a source to which I can apply access rules?

              no, native procedures execute in source databases Teiid has no control over it. All Teiid can do is allow one to execute or not.

               

              >Would you have an example how I can create such a procedure in Teiid Designer?

              see [3]

               

              [1] Virtual Procedures · GitBook

              [2] Procedure Language · GitBook

              [3] Teiid Designer User Guide

              • 4. Re: Security in Stored Procedures
                cjohn001

                Thanks!

                • 5. Re: Security in Stored Procedures
                  cjohn001

                  Hello Ramesh,

                  I would like to come back on you regarding the virtual procedure method. From my understanding, it should be possible to write a virtual procedure which calls a stored procedure given in the database. At least my idea is to use the virtual procedure as a wrapper which does checking of the access policy. I started small and just wrote and exported a stored procedure "getRecentProducts" with a single parameter of type BIGINT. For the moment I try to execute it without a virtual function as a wrapper. in the odata service metadata.xml it appears as follows :

                   

                  <Action Name="getRecentProducts" IsBound="false">

                   

                  <Parameter Name="idProfile" Type="Edm.Int64"/>

                   

                   

                  <Annotation Term="teiid.NAMEINSOURCE">

                   

                   

                  <String>`getRecentProducts`</String>

                   

                  </Annotation>

                   

                  <Annotation Term="my_nutri_diary.teiid_rel:aggregate">

                   

                   

                  <String>false</String>

                   

                  </Annotation>

                   

                  <Annotation Term="my_nutri_diary.teiid_rel:allows-distinct">

                   

                   

                  <String>false</String>

                   

                  </Annotation>

                   

                  <Annotation Term="my_nutri_diary.teiid_rel:allows-orderby">

                   

                   

                  <String>false</String>

                   

                  </Annotation>

                   

                  <Annotation Term="my_nutri_diary.teiid_rel:analytic">

                   

                   

                  <String>false</String>

                   

                  </Annotation>

                   

                  <Annotation Term="my_nutri_diary.teiid_rel:decomposable">

                   

                   

                  <String>false</String>

                   

                  </Annotation>

                   

                  <Annotation Term="my_nutri_diary.teiid_rel:deterministic">

                   

                   

                  <String>false</String>

                   

                  </Annotation>

                   

                  <Annotation Term="my_nutri_diary.teiid_rel:non-prepared">

                   

                   

                  <String>false</String>

                   

                  </Annotation>

                   

                  <Annotation Term="my_nutri_diary.teiid_rel:null-on-null">

                   

                   

                  <String>false</String>

                   

                  </Annotation>

                   

                  <Annotation Term="my_nutri_diary.teiid_rel:uses-distinct-rows">

                   

                   

                  <String>false</String>

                   

                  </Annotation>

                   

                  <Annotation Term="my_nutri_diary.teiid_rel:varargs">

                   

                   

                  <String>false</String>

                   

                  </Annotation>

                  </Action>

                   

                  In addition, in the VDB I have set access permissions to the procedure as full CRUD + Execute.

                   

                  When I am trying to call the procedure via the following link (Not sure if the syntax is correct):

                   

                  https://morpheus.fritz.box/odata4/svc/my_nutri_diary/getRecentProducts?idProfile=3

                   

                  I get the following error:

                   

                  
                  null
                  HTTP method 'GET' not allowed for this resource.

                   

                  Do you have an idea what I am doing wrong?

                   

                  The musql procedure looks as follows and I can execute it in mysql workbench.

                   

                  CREATE DEFINER=`root`@`%` PROCEDURE `getRecentProducts`(IN idProfile BIGINT)
                  BEGIN
                  SELECT DISTINCT fkIdProductCode, FDBProducts.product_name
                  FROM Diary
                  JOIN  FDBProducts ON FDBProducts.idCode = Diary.fkIdProductCode
                  AND Diary.fkProfile = idProfile
                  Limit 30;
                  END

                  Thanks for your help.

                   

                  Best regards,

                  Christoph

                  • 6. Re: Security in Stored Procedures
                    shawkins

                    A procedure without side effects, that has a return value or resultset, and no lob parameters can be represented as an odata function.

                     

                    Functions can be called with a GET - /function(param=...)

                     

                    Actions require a POST.  You should see an Allow header indicating a POST is needed when you issue a GET against an action url.

                    • 7. Re: Security in Stored Procedures
                      cjohn001

                      Hell Steven,

                      sorry for the late reply. Just came back from holiday

                       

                      I am not sure if I understand you correcly

                       

                      1. "Procedure without side effects": Means stored procedure does not do a create, update or delete on the tables, correct? My procedure just contains a select statement, i.e. no side effect. Hence from that point of view the procedure I posted above shall work, correct?

                       

                      2. If I use the syntax like I think you have described and I also can find here:

                       

                      OData Version 4.0 Support · GitBook  / How to execute a stored procedure?

                       

                      i.e.:

                       

                      https://morpheus.fritz.box/odata4/svc/my_nutri_diary/getRecentProducts(idProfile=2)

                       

                      resulting in

                       

                        1. Request URL: https://morpheus.fritz.box/odata4/svc/my_nutri_diary/getRecentProducts(idProfile=2)
                        2. Request Method: GET
                        3. Status Code: 400 Bad Request
                        4. Remote Address: 192.168.0.33:443
                        5. Referrer Policy: no-referrer-when-downgrade
                      1. Response Headersview source
                        1. Access-Control-Allow-Origin: *
                        2. Cache-Control: no-cache, no-store, must-revalidate
                        3. Content-Encoding: gzip
                        4. Content-Length: 152
                        5. Content-Type: application/xml
                        6. Date: Mon, 18 Mar 2019 20:05:04 GMT
                        7. Expires: 0
                        8. OData-Version: 4.0
                        9. Pragma: no-cache
                        10. Server: WildFly/11
                        11. X-Powered-By: Undertow/1
                      2. Request Headersview source
                        1. Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8
                        2. Accept-Encoding: gzip, deflate, br
                        3. Accept-Language: en,de;q=0.9
                        4. Cache-Control: max-age=0
                        5. Connection: keep-alive
                        6. Cookie: JSESSIONID=oCXI5uqReGFOK964qpCUHTvgaOydZ6sdC_M2nZgp.c881ecf4fae5
                        7. DNT: 1
                        8. Host: morpheus.fritz.box
                        9. Upgrade-Insecure-Requests: 1
                        10. User-Agent: Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A372 Safari/604.1

                       

                       

                      I get the following error

                       

                      <?xml version='1.0' encoding='UTF-8'?><error xmlns="http://docs.oasis-open.org/odata/ns/metadata"><code>null</code><message>The URI is malformed.</message></error>


                      I tried to set org.teiid.CONNECTOR and
                      org.teiid.COMMAND_LOG to Trace. However, I do not get more than the following log message:

                       

                      2019-03-18 20:05:04,161 WARN  [org.teiid.PROCESSOR] (default task-3)  TEIID16053 Unable to process odata request due to: The URI is malformed. Increase the log level to see the entire stack trace.

                       

                      Is there a further module on which I can get logging output to see what is going wrong here?

                       

                      Further question: You mean for any kind of manipulation on a table ( side effect?) I need to use a POST request? The naming convention "Action" is not clear to me. Does this mean, it is not allowed to use a stored procedure to manipulate tables? Would this in TEIID nomenclature then have to be a virtual function which I need to use?

                       

                       

                      I am also feeling quite lost with the linked documentation that Ramesh provided. The Teiid Designer documentation speaks about relational functions (with two subtypes, source function=stored procedure? and user defined function= Teiid virtual function?),  and the Teiid Gitbook about virtual functions and  triggers. To me it is not clear  what the difference between the different things is and how to use what kind of entity.

                       

                       

                      To my understanding I have two types of entity in a mysql database, stored procedures and triggers. Stored procedures as a general function doing some kind of CRUD operation and before or after triggers that apply to insert,update,delete operations and a specific table. How do these now fit to the documented entities?

                       

                      virtual functions live on Teiid layer like stored procedures on database layer? Do I need to wrap  each stored procedure on mysql layer with a virtual function on Teiid layer to be able to call it?

                      In my example above Teiid created the function in the vdb from a database import. As Teiid exports this function into metadata.xml I would expect that I can call it directly without defining an additional virtual function as wrapper on Teiid level.

                       

                      What are the mentioned triggers good for on the Teiid layer? Just to work on virtual views on top of different tables on Teiid layer? Hence being semantically evivalent to a trigger function on mysql layer? Like mentioned previously, on the native database layer I would understand a trigger to be some function which does sanity checks before or after  an insert, delete or update statement on a defined table. The triggers on Teiid layer are meant to be the same on virtual database layer?

                       

                      How do the Actions that you mentioned come into play? There is not such an entity described in the Teiid docs as far as I am aware of yet. Do you mean I should just use a post message on the exported procedure to execute it, rather than a get and there is no difference for the stored procedure export on vdb layer? In the metadata.xml excerpt above on odata layer the term action is also used. Does this mean that I can only call the exported function via post? What would I than have to do to export it as a procedure? Or is everything, trigger, virtual function, procedure exported as a action in odata terms?

                       

                      Thanks for your help.

                       

                       

                      Best regards,

                      Christoph

                      • 8. Re: Security in Stored Procedures
                        shawkins

                        > 1. "Procedure without side effects": Means stored procedure does not do a create, update or delete on the tables, correct? My procedure just contains a select statement, i.e. no side effect. Hence from that point of view the procedure I posted above shall work, correct?

                         

                        I was just trying to describe generically why some procedures will be functions and some will be actions.  Without seeing the Teiid DDL for the procedure I am guessing that it's represented by an action because it has no resultset/return defined. 

                         

                        > Is there a further module on which I can get logging output to see what is going wrong here?

                         

                        From the edm metadata you are showing the procedure is represented by an odata action, not a function:

                         

                        <Action Name="getRecentProducts" IsBound="false">

                         

                        That means you have to use a POST not a GET.

                         

                        > How do these now fit to the documented entities?

                         

                        Generally things break down between Teiid/virtual and foreign/source objects.  We don't do anything directly with source triggers, so those can be ignored for now.

                         

                        create virtual procedure ... - defines a Teiid procedure

                        create foreign procedure ... - defines a source stored procedure/function.  The notion of a foreign procedure marked as a function was the only way for legacy designer to define a function on a source model.  But in DDL you can use "create foreign function ..." instead.

                         

                        The mapping of a virtual/foreign procedure to an odata function/action is then as I described in the last comment.

                         

                        > How do the Actions that you mentioned come into play? There is not such an entity described in the Teiid docs as far as I am aware of yet.

                         

                        Action is an odata term - .net - oData v4 what are Functions and Actions in simple terms? - Stack Overflow

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                        • 9. Re: Security in Stored Procedures
                          cjohn001

                          Hello Steven,

                          sorry to bother you with this, but I have still not made progress on the topic

                           

                          create foreign procedure ... - defines a source stored procedure/function.  The notion of a foreign procedure marked as a function was the only way for legacy designer to define a function on a source model.  But in DDL you can use "create foreign function ..." instead.

                           

                          1. Not clear to me how  I then can specify when to use a procedure or a function. Is this automatically inferred by Teiid depending on the statements used in the stored procedure? Or are there any switches I have to use?

                           

                          2. ..because it has no resultset/return defined. 

                          I am not very familiar with stored procedures yet. Used them 10 years ago for the last time. However, from the  description of how to implement them in mysql, the select should return a resultset.

                           

                          How does MySQL return a result set from a stored procedure? - Database Administrators Stack Exchange

                          MySQL :: MySQL 5.5 Reference Manual :: A.4 MySQL 5.5 FAQ: Stored Procedures and Functions  search for A.4.14.

                           

                           

                          Is there any further keyword needed for Teiid to generate a return value for this result set?

                           

                          3. <Action Name="getRecentProducts" IsBound="false">

                          What does the IsBound mean, and how can I influence this parameter? seems like OpenUI5 also supports binding of procedures.

                          • 10. Re: Security in Stored Procedures
                            shawkins

                            > 1. Not clear to me how  I then can specify when to use a procedure or a function. Is this automatically inferred by Teiid depending on the statements used in the stored procedure? Or are there any switches I have to use?

                             

                            It is automatically inferred.  A procedure without side effects, that has a return value or resultset, and no lob parameters can be represented as an odata function.  Otherwise it will be represented as an odata action.

                             

                            > Is there any further keyword needed for Teiid to generate a return value for this result set?

                             

                            Take a step back for a second.  What is the DDL for the source procedure? And how was it created - what importer or was it manually defined?  Ideally if you were using an importer it would have gotten the resultset parameter metadata from the driver and that would have already been present.

                             

                            I'm assuming that your Teiid procedure ddl currently looks something like:

                             

                            create foreign procedure getRecentProducts(idProfile long) options (...);

                             

                            Teiid has support for returning a single result set using the returns clause:

                             

                            create foreign procedure getRecentProducts(idProfile long) returns table (fkidproductcode long, product_name string) options (...);

                             

                            > What does the IsBound mean, and how can I influence this parameter? seems like OpenUI5 also supports binding of procedures.

                             

                             

                            IsBound is more OData specifics.  See https://olingo.apache.org/doc/odata4/tutorials/action/tutorial_bound_action.html

                             

                            When it's unbound there isn't some prefix that's need to call the action/function.

                             

                            Steve