7 Replies Latest reply: Dec 28, 2012 7:41 AM by Marco Gralike RSS

    Preventing listings in FTP client for other users

    977847
      Hi, we are using XDB for XML input/output to customer backoffices. We have different users in a database that access the XDB. The XDB structure is like below:
      /
      /public/
      /public/user1
      /public/user2
      /public/user3
      /public/user1/sub1
      /public/user2/sub1
      /public/user3/sub1
      I placed ACL's on the /public/user*/ containers to only allow the owner to list and access the sub-containers in the /public/ resource. This works ok when i use Filezilla or Windows Explorer. The user only can see his own subfolder, the other folders are hidden. But when i use the default Windows FTP client (ftp.exe) every user can see all resources in the /public/ container. Access to subfolders other than his own is prevented, but i don't want them to list also.

      Is this somekind of weird implemenation in the Windows FTP client or are my ACL's wrong? The /public/ resource has a ACL attached that lets every user access the folder.

      Oracle version: 11.1.0.7 x64 Enterprise - Windows 2008 STD x64

      Hope you guys can throw me some suggestions for solving this "problem".

      Edited by: user8292115 on Dec 4, 2012 12:02 PM
        • 1. Re: Preventing listings in FTP client for other users
          odie_63
          Could you show your ACL?

          I've reproduced the case by creating two users.
          Tell us if it matches your scenario :
          create user user1 identified by user1;
          grant connect to user1;
          
          create user user2 identified by user2;
          grant connect to user2;
          then as USER1, so that it owns the folder :
          DECLARE
            res BOOLEAN;
          BEGIN
            res := DBMS_XDB.createFolder('/public/user1');
            dbms_xdb.setacl('/public/user1', '/public/acls/myacl.xml');
          END;
          /
          The folder "/public/user1" is protected by :
          <acl description="myacl"
           xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
           xmlns:dav="DAV:"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd">
            <ace>
              <grant>true</grant>
              <principal>dav:owner</principal>
              <privilege>
                <read-properties/>
                <resolve/>
              </privilege>
            </ace>
          </acl>
          In FileZilla, USER2 cannot see /public/user1 : OK

          With the command line FTP (Windows 7), <tt>dir</tt> and <tt>ls -l</tt> commands don't list folder "user1" :
          C:\Users\Marc>ftp
          ftp> open localhost 2100
          Connecté à MAS.
          220- MAS
          Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
          220 MAS FTP Server (Oracle XML DB/Oracle Database) ready.
          Utilisateur (MAS:(none)) : user2
          331 pass required for USER2
          Mot de passe :
          230 USER2 logged in
          ftp> cd public
          250 CWD Command successful
          ftp> dir
          200 EPRT Command successful
          150 ASCII Data Connection
          drw-r--r--   2 DEV      oracle         0 DEC 04 19:14 acls
          drw-r--r--   2 INVALID_ oracle         0 JAN 24 18:17 conf
          -rw-r--r--   1 OOX      oracle       247 MAY 17 11:05 contact.xml
          drw-r--r--   2 OOX      oracle         0 MAY 23 19:17 dtd
          drw-r--r--   2 DEV      oracle         0 NOV 25 12:00 test
          -rw-r--r--   1 DEV      oracle    948672 SEP 26 21:23 test.dat
          -rw-r--r--   1 DEV      oracle         0 SEP 26 21:23 workbook2.xml
          drw-r--r--   2 DEV      oracle         0 NOV 23 18:18 xsl
          226 ASCII Transfer Complete
          ftp : 498 octets reçus en 0,00 secondes à 498000,00 Ko/s.
          ftp> ls -l
          200 EPRT Command successful
          150 ASCII Data Connection
          drw-r--r--   2 DEV      oracle         0 DEC 04 19:14 acls
          drw-r--r--   2 INVALID_ oracle         0 JAN 24 18:17 conf
          -rw-r--r--   1 OOX      oracle       247 MAY 17 11:05 contact.xml
          drw-r--r--   2 OOX      oracle         0 MAY 23 19:17 dtd
          drw-r--r--   2 DEV      oracle         0 NOV 25 12:00 test
          -rw-r--r--   1 DEV      oracle    948672 SEP 26 21:23 test.dat
          -rw-r--r--   1 DEV      oracle         0 SEP 26 21:23 workbook2.xml
          drw-r--r--   2 DEV      oracle         0 NOV 23 18:18 xsl
          226 ASCII Transfer Complete
          ftp : 498 octets reçus en 0,02 secondes à 31,13 Ko/s.
          However, <tt>ls</tt> shows it :
          ftp> ls
          200 EPRT Command successful
          150 ASCII Data Connection
          acls
          conf
          contact.xml
          dtd
          test
          test.dat
          user1
          workbook2.xml
          xsl
          226 ASCII Transfer Complete
          ftp : 73 octets reçus en 0,01 secondes à 4,87 Ko/s.
          And hopefully :
          ftp> cd user1
          550- Error Response
          ORA-31050: Access denied
          550 End Error Response
          • 2. Re: Preventing listings in FTP client for other users
            Marco Gralike
            I am guessing that it might be a Windows cmd FTP issue or it has been solved in my release...
            DECLARE
              --
              b BOOLEAN;
              --
            BEGIN
              --
              b := DBMS_XDB.createFolder('/public/user1');
              b := DBMS_XDB.createFolder('/public/acls');
              --
              b := DBMS_XDB.createResource(
                     '/public/acls/myacl.xml', 
                     '<acl description="myacl"
                       xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
                       xmlns:dav="DAV:"
                       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                       xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd">
                        <ace>
                          <grant>true</grant>
                          <principal>dav:owner</principal>
                          <privilege>
                            <read-properties/>
                            <resolve/>
                          </privilege>
                        </ace>
                      </acl>',
                      'http://xmlns.oracle.com/xdb/acl.xsd',
                      'acl');
               --
               dbms_xdb.setacl('/public/user1', '/public/acls/myacl.xml');
               --
            END;
            --
            -- Locally on an Oracle Enterprise Linux 5U8 64b environment
            --
            [oracle@localhost bin]$ ftp
            ftp> open localhost 2020
            Connected to localhost.localdomain.
            220- localhost.localdomain 
            Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
            220 localhost.localdomain FTP Server (Oracle XML DB/Oracle Database) ready.
            530  Please login with USER and PASS.
            530  Please login with USER and PASS.
            KERBEROS_V4 rejected as an authentication type
            Name (localhost:oracle): user2
            331 pass required for USER2
            Password:
            230 USER2 logged in
            Remote system type is Unix.
            ftp> cd /public
            250 CWD Command successful
            ftp> dir
            227 Entering Passive Mode (127,0,0,1,13,119)
            150 ASCII Data Connection
            drw-r--r--   2 USER1    oracle         0 DEC 04 22:07 acls
            226 ASCII Transfer Complete
            ftp> ls -l
            227 Entering Passive Mode (127,0,0,1,252,95)
            150 ASCII Data Connection
            drw-r--r--   2 USER1    oracle         0 DEC 04 22:07 acls
            226 ASCII Transfer Complete
            ftp> ls
            227 Entering Passive Mode (127,0,0,1,80,151)
            150 ASCII Data Connection
            drw-r--r--   2 USER1    oracle         0 DEC 04 22:07 acls
            226 ASCII Transfer Complete
            ftp> user user1
            331 pass required for USER1
            Password: 
            230 USER1 logged in
            ftp> cd /public
            250 CWD Command successful
            ftp> dir
            227 Entering Passive Mode (127,0,0,1,58,84)
            150 ASCII Data Connection
            drw-r--r--   2 USER1    oracle         0 DEC 04 22:07 acls
            drw-r--r--   2 USER1    oracle         0 DEC 04 22:07 user1
            226 ASCII Transfer Complete
            ftp> ls -l
            227 Entering Passive Mode (127,0,0,1,132,25)
            150 ASCII Data Connection
            drw-r--r--   2 USER1    oracle         0 DEC 04 22:07 acls
            drw-r--r--   2 USER1    oracle         0 DEC 04 22:07 user1
            226 ASCII Transfer Complete
            ftp> ls
            227 Entering Passive Mode (127,0,0,1,158,169)
            150 ASCII Data Connection
            drw-r--r--   2 USER1    oracle         0 DEC 04 22:07 acls
            drw-r--r--   2 USER1    oracle         0 DEC 04 22:07 user1
            226 ASCII Transfer Complete
            ftp> cd user1
            250 CWD Command successful
            ftp> pwd
            257 "/public/user1" is current directory.
            ftp> user user2
            331 pass required for USER2
            Password: 
            230 USER2 logged in
            ftp> cd /public/user1
            550- Error Response 
            ORA-31050: Access denied
            550 End Error Response 
            ftp> bye
            221 QUIT Goodbye.
            Edited by: Marco Gralike on Dec 4, 2012 11:13 PM
            • 3. Re: Preventing listings in FTP client for other users
              Marco Gralike
              By the way I was testing this on a 11.2.0.3.0 environment, yours was/is...? Would be interested if the ACL's on /public are the same as on mine...
              [oracle@localhost bin]$ ./sqlplus / as sysdba
              
              SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 14:16:12 2012
              
              Copyright (c) 1982, 2011, Oracle.  All rights reserved.
              
              
              Connected to:
              Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              
              SQL> set long 100000
              SQL> set pages 5000
              SQL> SELECT XMLSerialize(DOCUMENT DBMS_XDB.getACLDocument('/public')
                                  AS CLOB)
                FROM DUAL;  2    3  
              
              XMLSERIALIZE(DOCUMENTDBMS_XDB.GETACLDOCUMENT('/PUBLIC')ASCLOB)
              --------------------------------------------------------------------------------
              <acl description="Public:All privileges to PUBLIC" xmlns="http://xmlns.oracle.co
              m/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaL
              ocation="http://xmlns.oracle.com/xdb/acl.xsd                           http://xm
              lns.oracle.com/xdb/acl.xsd" shared="true">
                <ace>
                  <grant>true</grant>
                  <principal>PUBLIC</principal>
                  <privilege>
                    <all/>
                  </privilege>
                </ace>
              </acl>
              • 4. Re: Preventing listings in FTP client for other users
                977847
                odie_63, this is exactly the scenario i'm experiencing. As shown in your example, user2 kan list the folder "user1" with LS. I also tried the "ls -l" and indeed it only shows the folder i want to see.

                The ACL's that are in place for the user folders are like the one you shown with "dav:owner".

                So, it might be a Windows FTP issue ?

                Thanks for you testcase, it shows my problem.
                • 5. Re: Preventing listings in FTP client for other users
                  977847
                  Marco, this is happening on 11.1.0.7. I can try on 11.2.0.3 also to be sure it isnt a version related issue. This enviroment will be upgraded to 11.2.0.3 this weekend anyways so this question will be answered then.

                  Oh, and the ACL on public:

                  XMLSERIALIZE(DOCUMENTDBMS_XDB.GETACLDOCUMENT('/PUBLIC')ASCLOB)
                  --------------------------------------------------------------------------------

                  <acl description="Public:All privileges to PUBLIC" xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd" shared="true">
                  <ace>
                  <grant>true</grant>
                  <principal>PUBLIC</principal>
                  <privilege>
                  <all/>
                  </privilege>
                  </ace>
                  </acl>

                  Edited by: Ebayzo on Dec 5, 2012 8:30 AM
                  • 6. Re: Preventing listings in FTP client for other users
                    977847
                    Ok, the database has been upgraded to 11.2.0.3 but still the folders are visible when using ftp.exe. Anyone some great advice?
                    • 7. Re: Preventing listings in FTP client for other users
                      Marco Gralike
                      I can't check currently, but I get the feeling it is due to this old Wiindows ftp.exe that has probably its roots still from the command.com or cmd.com years (windows 3.1/NT)
                      On windows do all FTP clients (software) have the same problem or only the command line one?
                      If so maybe it is possible with some registry settings to get the behavior on par with the rest of the world?