How can an ODBC Query be issued to return a Filemaker Database account information (Accounts, Types, and Privilege Sets)?
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.
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.
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: email@example.com <mailto:firstname.lastname@example.org
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.
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.
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.
Edit: Stephen and I were apparently adding comments at the same time. Blasted stateless nature of the Web again ...
Retrieving data ...