6 Replies Latest reply on Jun 26, 2013 11:47 AM by Mike_Mitchell

    Quering account information (Accounts, Types, and Privilege Sets)?

    afitness

      How can an ODBC Query be issued to return a Filemaker Database account information (Accounts, Types, and Privilege Sets)?

        • 1. Re: Quering account information (Accounts, Types, and Privilege Sets)?
          mikebeargie

          Hi, and welcome to technet.

           

          Are you saying that you want to get that info from another program using filemaker as a source? ODBC doesn't work that way as far as I know. Filemaker only acts as a data source, aside from the details stored in your DSN to connect, you can only access the data tables in filemaker, and not any of the meta or system info from filemaker.

           

          Unless you're storing that data in a filemaker "users" table that you create, you can't query against it.

          • 2. Re: Quering account information (Accounts, Types, and Privilege Sets)?
            Stephen Huston

            I believe the only way to extract this information in a chunk from a FileMaker file or file system is via the Database Design Report (DDR) using FileMaker Advanced. If you check the Accounts part of the DDR setup, the priv sets and accounts for each will be included as a section of the report. You can even run a DDR which contains little but the Accounts info if that's all you want.

             

            You can save a DDR as HTML or as XML; XML is most useful if you want to then import the account data into FM as records.

            • 3. Re: Quering account information (Accounts, Types, and Privilege Sets)?
              afitness

              Thanks for the info.  I knew the information I want was available in the DDR, but I wanted to access it via script or via ODBC so that I could run a single program that automated the collection and documentation of this info, which is constantly changing as we add/remove users from out many databases.

               

               

               

               

               

              John M. Pfuntner

               

              Vice President, Owner, Director of Operations

               

              Advantage Sport & Fitness, Inc.

               

              Phone: 607-257-2107 x11

               

              Email: jmpfuntner@advantagefitness.com <mailto:jmpfuntner@advantagefitness.com

              • 4. Re: Quering account information (Accounts, Types, and Privilege Sets)?
                mikebeargie

                John, if this is the case (mission critical), then you should consider doing your user control in your frontend application.

                 

                ODBC by design (given it's platform independence) is only supposed to allow for cross-application/platform data access, as noted above, unless your user control is table based, you won't be able to pull that data from filemaker, or any other ODBC capable backend that does not use table based user access control.

                 

                There would still be ways to record access/user data (IE username, connection time, etc..) back into filemaker for your backend use if you switched your user control to the front end.

                • 5. Re: Quering account information (Accounts, Types, and Privilege Sets)?
                  Stephen Huston

                  You might want to consider adding a FileMaker account management file to the system, accessible only to the people with permission to add/removuce users, and automate the addition and removal of accounts across all files based on "roles".

                   

                  We did this with a 160 file system which was a nightmare to handle using a file-by-file approach, but took only a few seconds to add or remove someone once it was all set up. Adding the local scripts to each file to add/remove an account based on a "role" took me a couple of days in 160 files, but it saved roughly an hour per user for ongoing management.

                   

                  It also allowed us to have that account management file tell us who was authorized with which accounts everywhere. A huge time savings in the long run.

                   

                  There are several such account management file systems available from third parties -- not a plug in -- or role your own as we did.

                  • 6. Re: Quering account information (Accounts, Types, and Privilege Sets)?
                    Mike_Mitchell

                    Building on what Mike B. has laid out, you can set up a "users" table that includes the user name and privilege set (but no passwords!), using scripting to add / delete / modify accounts. That table can then be queried externally. Just be careful about who you give the access to, since user name is half of what a hacker needs to get access to the application. It's not optimal, but it would allow you to collect your account information centrally if you need to.

                     

                    Mike

                     

                    Edit: Stephen and I were apparently adding comments at the same time. Blasted stateless nature of the Web again ...