Looking up card numbers in an external database

PaperCut MF can import user card/identity numbers from Active Directory and LDAP. This is the recommended approach because it allows the card/ID numbers to be associated with users in a centralized location. For more information see the section called “Importing Card/Identity numbers from Active Directory or LDAP”.

Card numbers can also be imported using the import file described in the section called “Batch User Card/Identity Update”.

In some circumstances the mapping between card numbers and users may be stored in another external database (e.g. a database used for secure door access). In this case, it may be more convenient to look up the card numbers in this database in real-time.

Tip

This also allows users to be associated with multiple card/ID numbers. This is useful where users are allocated different types of authentication cards, or there are alternate card systems used throughout the organization.

To allocate multiple card numbers to a user, simply populate the mapping table with multiple entries per user where different card numbers map to the same username.

Once external user lookups are enabled, PaperCut MF will do the following when looking up a user by card number:

  1. Find a user with the matching card number in the PaperCut MF database.

  2. If not found, the card number will be looked up in the external database.

  3. If a match is found the information returned is used to find the matching user in the PaperCut MF database. If a user is found the lookup is successful.

Database lookup configuration

To enable external card number lookups:

  1. Navigate to OptionsAdvancedExternal User Lookup.

  2. Enable the option Use external database for card number lookup.

  3. Select the database type. If using Oracle or MySQL you must install the database driver as described in the the section called “Database specific configuration”, and the application server must be restarted.

  4. Enter the database connection URL. For examples see the section called “Step 4 - Change the PaperCut MF connection details” of the section called “Upsizing to an External RDBMS”.

  5. Enter the database connection username and password.

  6. The option SQL to map card number in external database to: allows you to choose what the card number in your external database maps to. The options include:

    • username, used if your external database contains a mapping between card numbers and usernames, and

    • user's identity number, used if your external database contains a mapping between card numbers and user ids (and the user ids have been imported and stored on users in PaperCut).

    Select the option that matches the mapping in your external database.

  7. Enter an SQL select query that looks up the card number in your external database and returns either a username or user id as selected above. The query must return a single row with the first field being the username or user id (as found in PaperCut MF). The SQL statement must contain {cardnumber}, which will be replaced with the card number to find.

    An example select query that looks up a card number and returns a username is:

    select user_name from users_table where card_number = {cardnumber}

    An example select query that looks up a card number and returns an indentity number is:

    select user_id from users_table where card_number = {cardnumber}

    Note

    The {cardnumber} replacement does not require quotations (it is sent as a parameter). This also serves to prevent SQL injection attacks sent via card numbers.

Testing

To test the lookup is working as expected:

  1. Navigate to the Users tab.

  2. Pick a card number from your external database that maps to a user in PaperCut MF.

  3. Enter this card number in the Quick Find field and press Go.

  4. Verify that the matching user is displayed. If the expected user is not displayed check the App. Log tab for errors.