Limit number of rows in a table

15 pts.
Tags:
Auto increment
Database programming
SQL
Dear all, I'd just like to ask if anyone can help me find a solution to the following problem: I want to limit the number of records in a table. So when all the available rows are used, the customer can purchase additional ones. Say for example that the customer is allowed to enter 100 sites. When he uses them all up, he can purchase an extra 100. But if i add a constraint to the table, he will be able to remove it and i can't give him a different account with limited permissions since it's his server. I also can't limit it from the program since then he'll be able to add them from the sql manager. And if i try to check the id and drop any sites that have an id > 100, he'll have the ability to change the id even if it's an auto-increment field (he can remove the auto-increment feature and change the values, which creates program instability on top of that). Also a disadvantage with auto-increment is that if he deletes records, those ids will not be used again so if he deletes 90 sites, he will only be able to add another 10. Any ideas on how to solve this issue? Thank you and sorry for the long post.

Answer Wiki

Thanks. We'll let you know when a new response is added.

If they are going to have full control over the database, I think there is not much you can do. If you are going to administer and support this system, they should have limited access to the database.

Maybe knowing a little more about how applications will use the information from that table someone can offer a better option.

——————————————
{kccrosser} First, keep in mind that you can create stored procedures and functions that are “obfuscated” – i.e., they are encrypted, so that users cannot easily view/edit the text. Therefore, you can create “protected” functions that are not susceptible to customers changing them. (“Create function … with encryption …”)

What this means is that you can implement a “key” manager function that checks a database record for an authorized count AND an encrypted code that validates that count. By obfuscating the algorithm used to compute the code from the count, you make it extremely difficult for the customer to alter the count.

There are many complicating factors in building a commercial product like this.

If you are worried about different customers sharing information (“My count is 200 and my key code is “a25b339rxy2″”) then you should compute the key code such that it includes a customer id code (or a processor id code – although that can be a problem if the customer’s primary system fails and they move to a backup or replacement system).

A very simple approach would be a function that took the count, a customer id, and a key code and returned a true/false value if the key code is valid. The CHECKSUM function can be used to generate the code, such as:

create function fVerifyCode (
@iCount integer
@iCustomerID integer
@sKeyCode varchar(255)
)
return int
with encryption
as
begin
if CHECKSUM(@iCount, @iCustomerID) = @sKeyCode
return 1
else
return 0
end

Obviously, you would need a separate utility (not given to customers) that took the count and customer id parameters and returned the checksum.

This is a very simple approach – the algorithm can be made more complex in many ways. For example, you could add a “secret” pass-phrase as one of the arguments to the CHECKSUM function, like:
CHECKSUM(@iCount, @iCustomerID, ‘supercalifragilisticexpialidocious’).

From this, you should see how you could implement the validation and control the number of items.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ElArZ
    I can't give them limited permissions because the clients administer the system, we only provide support. The application is actually a call accounting software, and we want to limit the number of extensions and sites/branches. The branches limitation is easy since if we only want 5 branches to connect, we'll only open 5 connections. But the extensions limitation is the intriguing part, since the problem is that there is no loading part in here. So when the call is parsed, the extension is retrieved and is added to the table if it doesn't already exist. I can stop adding extensions if the count is > 100, but he'll be able to add extensions from the sql manager. I can rely also on on not treating extensions if the id is > 100, but then again, he can delete old extensions but the old ids will not be reused.
    15 pointsBadges:
    report
  • msi77
    Try using triggers for that with additional control table.
    1,660 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following