title: Spark SQL security considerations
body: <p>What are the security considerations when accepting and executing arbitrary spark SQL queries? </p>
  
  <p>Imagine the following setup:</p>
  
  <p>Two files on hdfs are registered as tables <code>a_secrets</code> and <code>b_secrets</code>:</p>
  
  <pre><code># must only be accessed by clients with access to all of customer a' data
  spark.read.csv("/customer_a/secrets.csv").createTempView("a_secrets")
  
  # must only be accessed by clients with access to all of customer b's data
  spark.read.csv("/customer_b/secrets.csv").createTempView("b_secrets")
  </code></pre>
  
  <p>These two views, I could secure using simple hdfs file permissions. But say I have the following logical views of these tables, that I'd like to expose:</p>
  
  <pre><code># only access for clients with access to customer a's account no 1
  spark.sql("SELECT * FROM a_secrets WHERE account = 1").createTempView("a1_secrets")
  
  # only access for clients with access to customer a's account no 2
  spark.sql("SELECT * FROM a_secrets WHERE account = 2").createTempView("a2_secrets")
  
  
  # only access for clients with access to customer b's account no 1
  spark.sql("SELECT * FROM b_secrets WHERE account = 1").createTempView("b1_secrets")
  
  # only access for clients with access to customer b's account no 2
  spark.sql("SELECT * FROM b_secrets WHERE account = 2").createTempView("b2_secrets")
  </code></pre>
  
  <p>Now assume I receive an arbitrary <code>(user, pass, query)</code> set. I get a list of accounts the user can access:</p>
  
  <pre><code>groups = get_groups(user, pass)
  </code></pre>
  
  <p>and extract the logical query plan of the user's query:</p>
  
  <pre><code>spark.sql(query).explain(true)
  </code></pre>
  
  <p>giving me a query plan along the lines of (this exact query plan is made up)</p>
  
  <pre><code>== Analyzed Logical Plan ==
  account: int, ... more fields
  Project [account#0 ... more fields]
  +- SubqueryAlias a1_secrets
     +- Relation [... more fields]
        +- Join Inner, (some_col#0 = another_col#67)
           :- SubqueryAlias a2_secrets
           :  +- Relation[... more fields] csv
  == Physical Plan ==
  ... InputPaths: hdfs:/customer_a/secrets.csv ...
  </code></pre>
  
  <p>Assuming I can parse a logical query plan to determine exactly which tables and files are being accessed, is it safe to grant access to the data produced by the query? I'm thinking of potential problems like:</p>
  
  <ul>
  <li>Are there ways to access registered tables without them showing up in a logical query plan?</li>
  <li>Are the ways to load new data and register it as tables through pure spark SQL? (input to <code>spark.sql(1)</code>)?</li>
  <li>Do users have access to any sql functions with side effects (that modifies or accesses unathorized data)?</li>
  <li>Are there ways to register UDFs/execute arbitrary code purely through <code>spark.sql(1)</code>?</li>
  </ul>
  
  <p>To summarise: Can I safely accept arbitrary SQL, register it with <code>df = spark.sql(1)</code>, analyse data access using <code>df.explain(True)</code>, and then return results using e.g. <code>df.collect()</code>?</p>
  
  <p><strong>Edits:</strong>
  - <em>23 Jan 15:29: edited to include an "EXPLAIN" prefix in</em></p>