Updating User Card Numbers Using SQL

Some large sites maintain an external database of user’s and their card numbers. To make use of these card numbers in PaperCut we usually recommend sites use one of the built-in options:

1. the built-in card import (see here),
2. directly from Active Directory or LDAP (see here).
3. the SQL card look-up feature. This allows you to define your own SQL statement to match a user to a card number. This is described in detail in our manual here.

However in some situations it is more convenient to use SQL to efficiently update the user’s card numbers (and optionally card PINs) in PaperCut. The approach involves creating and populating an import table and then running an SQL statement to update the card numbers on the PaperCut users.

This article describes how to achieve this using Microsoft SQL Server database.

1. Prerequisites

• PaperCut must be configured to run on Microsoft SQL Server as described here.
• A source of card numbers linking them to usernames is required.
• Familiarity with SQL Server to run and configure database scripts.

2. Create the card import table in the PaperCut database.

Create a table in the PaperCut database (in the SQL Server instance PaperCut is currently running on) called ext_user_card_number_import. This table should be defined with the following fields:

• user_name varchar(50) not null (primary key)
• card_number varchar(100) not null (unique index)

A script for creating this table can be downloaded here: create_user_card_table.sql. This should be run using the SQL Server Management Studio or equivilent.

3. Populate the card import table

The next step is to populate the import table (ext_user_card_number_import) with usernames and card numbers from the external system. How this is achieved is depends on where this data is stored its format. Examples of how the import table can be populated may include:

• Write a custom program or script to transform and import the data into the table.
• Use SQL Server integration services to import the data from another data source.
• If the data is in the same SQL server instance it may be possible to import it directly with a single cross-database SQL statement.

The following rules should be applied to imported data:

• user_name field should match a name in the the tbl_user.user_name field.
• card_number field must be unique, and must be normalized to lower-case and remove leading and trailing spaces.

This step should be scheduled to regularly when card numbers in the source system are updated.

4. Run SQL to update card numbers in PaperCut

Once the import table ext_user_card_number_import is populated/updated, the PaperCut user records can be updated with the new card numbers.

The following is an example SQL for Microsoft SQL Server. ``IMPORTANT: If you modify this for another DB ensure that the card number is lower-cased and trimmed of white-space when copying into PaperCut.

update tbl_user set 
    card_number = LOWER(LTRIM(RTRIM(eucn.card_number))),
    modified_date = GETDATE(),
    modified_by = ‘[external card number import]’
from
    tbl_user inner join ext_user_card_number_import eucn
        on tbl_user.user_name = eucn.user_name
where
    tbl_user.deleted = ‘N’
    and tbl_user.internal = ‘N’
    and tbl_user.card_number <> eucn.card_number

Manually run this SQL command and verify that numbers are updating as expected.

This statement is very efficient, only updating the records that require updating. Performance testing on database containing 350,000 users and 350,000 records in the ext_user_card_number_import table showed:

• Updating all 350,000 users’ card numbers - 30 seconds.
• Updating 1 user’s card number in 350,000 users - less than 1 second.

The above SQL will only update users that are in the ext_user_card_number_import table. If you would prefer to clear the card number field in PaperCut if the user doesn’t exist in the ext_user_card_number_import table then you should also run the following SQL statement.

update tbl_user set
    card_number = ‘ ’,
    modified_date = GETDATE(),
    modified_by = ‘[external card number import]’
where 
tbl_user.deleted = ‘N’
and tbl_user.internal = ‘N’
and tbl_user.card_number <> ‘ ’
and not exists (
	select user_name 
	from ext_user_card_number_import eucn 
	where 
		tbl_user.user_name = eucn.user_name
)

5. Scheduling regular updates

To keep the PaperCut card numbers up-to-date with the source card system, steps 3 and 4 above should be scheduled to run on a regular basis.

If steps 3 and 4 are scheduled independently, ensure that step 4 is scheduled to start after step 3 has completed.

Note on data consistency

This method should be used to only update existing users in the database. PaperCut does not support adding new users via SQL.

Is is very important to ensure no duplicate card numbers are imported into PaperCut. If two users are assigned the same card number, then neither user will be able to use the card number for authentication in PaperCut.

If card numbers are never reassigned to users, then it should not be possible to create duplicate card numbers in PaperCut using this technique.

To check that there are no duplicate card numbers in PaperCut, the following query can be run and should return no results.

select card_number, count(*)
from tbl_user 
where deleted = ‘N’ and (card_number is not null and card_number <> ‘ ’)
group by card_number having count(*) > 1 

SQL Administrators may wish to run this command from time to time.