You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@nifi.apache.org by Brajendra Mishra <br...@persistent.com> on 2018/06/11 06:13:39 UTC
Why "ExecuteSQL" processor serving DELETE and UPDATE SQL queries.
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:
[cid:image001.jpg@01D40179.6CF43000]
[cid:image002.png@01D40179.6CF43000]
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.
Re: Why "ExecuteSQL" processor serving DELETE and UPDATE SQL queries.
Posted by Mike Thomsen <mi...@gmail.com>.
If you really need to restrict the commands that a particular user can
execute, it should be done via the database users that NiFi's services are
using. In 1.7, there's a new database service coming along that wraps two
or more database pools. It could probably be used in scenarios like this to
associate a particular pool with a particular limited database user account.
On Mon, Jun 11, 2018 at 3:13 AM Sivaprasanna <si...@gmail.com>
wrote:
> 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 <
> brajendra_mishra@persistent.com> 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 <si...@gmail.com>
> > Sent: Monday, June 11, 2018 11:55 AM
> > To: dev@nifi.apache.org
> > 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 <
> > brajendra_mishra@persistent.com> 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.
> >
>
Re: Why "ExecuteSQL" processor serving DELETE and UPDATE SQL queries.
Posted by Sivaprasanna <si...@gmail.com>.
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 <
brajendra_mishra@persistent.com> 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 <si...@gmail.com>
> Sent: Monday, June 11, 2018 11:55 AM
> To: dev@nifi.apache.org
> 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 <
> brajendra_mishra@persistent.com> 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.
>
RE: Why "ExecuteSQL" processor serving DELETE and UPDATE SQL queries.
Posted by Brajendra Mishra <br...@persistent.com>.
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 <si...@gmail.com>
Sent: Monday, June 11, 2018 11:55 AM
To: dev@nifi.apache.org
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 < brajendra_mishra@persistent.com> 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.
Re: Why "ExecuteSQL" processor serving DELETE and UPDATE SQL queries.
Posted by Sivaprasanna <si...@gmail.com>.
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 <
brajendra_mishra@persistent.com> 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.
>