This discussion is archived
7 Replies Latest reply: Dec 28, 2012 5:41 AM by MarcoGralike RSS

Preventing listings in FTP client for other users

977847 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points