Re: Directory/File Access Permissions for COPY and Generic File Access Functions

* Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
> On 10/27/14 7:36 PM, Stephen Frost wrote:
> > MySQL:
> > http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_file
> >
> > (note they provide a way to limit access also, via secure_file_priv)
>
> They have a single privilege to allow the user to read or write any
> file. I think that feature could be useful.

... Optionally limited to a specific directory with the
secure_file_priv, as I pointed out previously.

> > Oracle:
> > http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5007.htm
> > http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9013.htm#i2125999
>
> >From the description, that CREATE DIRECTORY command looks to me more
> like a tablespace, or a general BLOB space, that you reference by object
> name, not by file name.

It also allows you to use the 'external_table_clause' which "is a
read-only table whose metadata is stored in the database but whose data
in stored outside the database. Among other capabilities, external
tables let you query data without first loading it into the database."

In other words, file_fdw. If you read further down, you'll also see
that the way the file is interpreted is based on the access drivers,
which can be ORACLE_LOADER or ORACLE_DATAPUMP, which means you can read
any file you can use with imp or SQLLoader. Basically, Oracle expects
you to use this to create a table in the DB which references the
external file rather than importing using a COPY, which I agree we want
and should make file_fdw support, but it amounts to the same thing.

> > SQL Server:
> > http://msdn.microsoft.com/en-us/library/ms175915.aspx
> > (Note: they can actually run as the user connected instead of the SQL DB
> > server, if Windows authentication is used, which is basically doing
> > Kerberos proxying unless I'm mistaken; it's unclear how the security is
> > maintained if it's a SQL server logon user..).
>
> That could be useful. ;-) But it's not actually the same as the feature
> proposed here.

Huh? It's exactly the same- but done with Kerberos integration and file
shares. This proposal is essentially a poor-man's version of this where
the administrator has to go set up the allows themselves rather than
letting Kerberos and regular user permissions handle it.

The point is, they're both about giving users access to external files
for importing, exporting and querying, within certain boundaries to
avoid the user being able to trivially bypass the in-database or OS
security.

> > DB2:
> > http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dm.doc/doc/c0004589.html?cp=SSEPGG_9.7.0
>
> That's also more like the single capability system that MySQL has.

I agree that it's not clear from the docs for DB2 how or if you can
limit what can be done with this capability, but I don't see much point
in it if you can use it to bypass the in-database security.

> So while this is interesting food for thought, I don't think this really
> supports that claim that other systems have a facility very much like
> the proposed one.

While the documentation for these other products isn't as good as ours,
if you look a bit closer, I think you'll see that their features are
actually very similar to the proposed one (Oracle's even has nearly the
same syntax..).

Thanks,

Stephen