Why do I not have permission to use script containing frappe.db.sql?

I created an API Script to perform a SQL Query. It looks like this:

query = """
/*my query here*/

values = {'customer': 'my_customer'}
frappe.db.sql(query, values, as_dict=0)

When I navigate browser address bar to the URL for the API method, I get “You do not have enough permissions to complete the action”.

If I comment out the “frappe.db.sql()” line, the script runs fine. I even tested with a placeholder response and the browser receives the response just fine. Why is frappe.db.sql causing a permission error and how do I fix it?

What kind of query is it? I might be mistaken, but I believe the restricted Python environment used in server scripts allows only SELECT statements.

It contains a couple of SELECTS wrapped in CTEs. And the CTEs are used to perform a final SELECT statement.

Are you saying that Frappe Python code tries to parse the SQL to figure out what it might do? That completely boggles my mind – I never would have expected that.

Switched the CTEs to nested SELECT statements so that query begins with SELECT and that gets rid of the permission error.

Wowz. Just ridiculous.

I followed up with some personal commentary and investigatory questions on this subject: