I understand that. I linked that Jira to initiate a discussion (so that the team can pitch in their thoughts) on to have necessary changes done on ExecuteSQL to enable support for both SELECT and DELETE operations and make the necessary documentation changes, explaining that this processor supports SELECT and DELETE.
For your case of restricting to accept "SELECT" statement alone: IMHO, this sounds like a case specific requirement and it may not be possible or seem logical to enforce this restriction for the whole user base so you can have two things: - If you have authorization management tools like Apache Ranger, you can have the restriction by having policies that allows only certain people to do DELETE operations so even if someone using NiFi tries to delete and that person doesn't have the necessary privileges, it will fail. This is kinda complex solution and the changes are externalized to NiFi - The simple solution is to customize the ExecuteSQL to accept only SELECT statements. Thanks, Sivaprasanna On Mon, Jun 11, 2018 at 12:19 PM, Brajendra Mishra < [email protected]> wrote: > Hi Sivaprasanna, > > Thanks for prompt response. > Mentioned Jira defect (https://issues.apache.org/jira/browse/NIFI-4843) > talks about, to support delete queries through ExecuteSQL processor, but in > our case it works fine for 'Delete' as well as 'Update' queries. > > IN documentation is mentioned only about catering 'Select' SQL queries: > > "SQL select query: The SQL select query to execute. The query can be > empty, a constant value, or built from attributes using Expression > Language. If this property is specified, it will be used regardless of the > content of incoming flowfiles. If this property is empty, the content of > the incoming flow file is expected to contain a valid SQL select query, to > be issued by the processor to the database. Note that Expression Language > is not evaluated for flow file contents. > Supports Expression Language: true" > > Is there a way to restrict user to allow only select statement/queries > through expression language? > > Brajendra Mishra > Persistent Systems Ltd. > > -----Original Message----- > From: Sivaprasanna <[email protected]> > Sent: Monday, June 11, 2018 11:55 AM > To: [email protected] > Subject: Re: Why "ExecuteSQL" processor serving DELETE and UPDATE SQL > queries. > > Brajendra, > > As you have said, even though the documentation for ExecuteSQL mentions, > "It is meant to execute SELECT", it ultimately accepts and executes DML > commands. Looking at the code, there are no way of restricting it to accept > & execute SELECT query only. There is this Jira NIFI-4843 < > https://issues.apache.org/jira/browse/NIFI-4843> which mentions something > similar. Either we can do one thing: Make ExecuteSQL support two types of > operation "SELECT" and "DELETE" and be it exposed as a property and in the > code we do the check and perform the execution. Thoughts? > > Thanks, > Sivaprasanna > > On Mon, Jun 11, 2018 at 11:43 AM, Brajendra Mishra < > [email protected]> wrote: > > > Hi Team, > > > > > > > > We are currently using ExecuteSQL processor in our flow. > > > > As per the documentation, ExecuteSQL processor should only be worked > > for SELECT queries, but it is working for other SQL commands as well > > for DELETE and UPDATE queries. > > > > > > > > In our current flow implementation we want to restrict user to execute > > only SELECT queries. How we can achieve this requirement? > > > > > > > > For your reference, here I have attached screen prints of ‘ExecuteSQL’ > > processor: > > > > > > > > Brajendra Mishra > > > > Persistent Systems Ltd. > > > > > > DISCLAIMER > > ========== > > This e-mail may contain privileged and confidential information which > > is the property of Persistent Systems Ltd. It is intended only for the > > use of the individual or entity to which it is addressed. If you are > > not the intended recipient, you are not authorized to read, retain, > > copy, print, distribute or use this message. If you have received this > > communication in error, please notify the sender and delete all copies > of this message. > > Persistent Systems Ltd. does not accept any liability for virus > > infected mails. > > > DISCLAIMER > ========== > This e-mail may contain privileged and confidential information which is > the property of Persistent Systems Ltd. It is intended only for the use of > the individual or entity to which it is addressed. If you are not the > intended recipient, you are not authorized to read, retain, copy, print, > distribute or use this message. If you have received this communication in > error, please notify the sender and delete all copies of this message. > Persistent Systems Ltd. does not accept any liability for virus infected > mails. >
