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 <[email protected]> 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 < > [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. > > >
