Security advice for database design

Hello all,

I am currently working on a database which is planned to be used "worldwide"

What is the best/easiet way to restrict editing of this database by country?

i.e. If someone enters a record in Spain, how can I restrict an editor in England from changing it?

Can this be achieved somehow using Roles? - I'm thinking not as every country is using the same form...

Thanks for you help as always
LVL 21
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi shuboarder,
here's a few ways I see (if this is the db that will be destributed in various countries, not web app):

    - If the db is going to be used by the members of the same company then the best way I see is to use @Name([C]; @UserName ) - country part of hierarchy name
    - you can use the db profile document (every db has it's own doc) in which you have field Country (must be filled)
    - you can use the same thing as above but with user's profile doc
    - of course you can use roles but you depend on someone to give the propper roles to users in each country

Hope this helps,
I was thinking of groups per country myself, but you have a good point there Marko, using the C-fragment of the username (if available).

To add to that solution: use Marko's @Name example to retrieve the country from a user's ID; prefix "*/" and drop it in an authors field.

If your id's do not show the country fragment, I'd recommend using groups to be put in that authors field (using a user profile doc). Userroles would be a logical 1st choise, but even R7 is limited to 75 roles in an ACL. It can hold approx. 950 names (32kB) in groups etc. Include the expected no. of countries in your decision!
shuboarderAuthor Commented:
If it helps, each created document will contain the country it is being created for in a computed field.
Is there any way of just comparing the current user with this field and if the country doesn't match they can't edit it?
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

the question here is how to find out which Country is the current user from.
If you have the C component of the username then it's easy:

In the QueryModeChange form event:

    Dim s As New NotesSession
    Dim user As NotesName
    Set user = New NotesName( s.UserName )

    'he's trying to enter edit mode (which means that he's currently in read mode)
    If Not source.EditMode And LCase( source.document.Country(0) ) <> LCase( user.Country ) Then
        Continue = False
        Exit Sub
    End If

The same thing should be checked in the QueryOpen form event if the Mode is 1 (user is trying to open doc directly in edit mode Ctrl + e).

Hope this helps,
shuboarderAuthor Commented:
Ok, our usernames appear as follows:


We have multiple sites per country, but it is ok if one site edits another site as long as it is in the same country.

I have added the code above into the QueryModeChange form event - thanks for this
What else do I need to do now?
obviously you don't have the Country part of the hierarchical name, which means that the code wont work - user.Country will return empty string.
You have to figure out a different approach.

How do you know in which country the user is in?
Maybe by the sitecode (what is that) part of username?
Maybe by the Country field in the person's document in the address book?

Hope this helps,
shuboarderAuthor Commented:
The site code relates to the site name which must ultimately relate to country somewhere - although this is not in the country field in the address book.

We have a site information database. I am using @dbcolumn to lookup this database in order to get the site name.

I have then used @dblookup to poulate hidden fields on the form with the site code and country based on the @dbcolumn selection.
then find the view in site info db that lists site codes by countries and use DbLookup to get the country for the current site code (create the new view if you can't find it).
Then when you have the country simply substitute
with that information and there you are.

Hope this helps,
If you know the country the form is restricted to (stored value), and you know the country the user is in (computer for display, as indicated in your previous post from the lookup you are doing) then just update the code in QueryOpen and QueryModeChange to check for differences between the two and prevent switching to edit mode if they don't match. For QueryOpen you only need to check if they are trying to open it in edit mode directly from the view (i.e. if the press Ctrl+E) and in QueryModeChange you need to check if they are in read mode going to edit mode.
That only keeps users from doing the obvious thing.
However, it's NOT a security measurement!
Users can still force changes into those documents (agents, toolbar icons, different views/forms)!
Sjef BosmanGroupware ConsultantCommented:
As CRAK said in his first comment: use Author-fields. Fooling around with QueryOpen is not the way to go.
CRAK & sjef,
if he is to use Author field then he has to make sure that all users have author access in the ACL, hasn't he?

Not neccessarily:
People in England aren't allowed to edit Spanish documents. Is has not been said that EVERYONE in Spain can edit those documents. Some may be author, others may have no edit rights at all (reader access).

But you probably tried to warn for access HIGHER than author.... you've got another good point there!
Explanation from designer help:
"...Users who already have Editor (or higher) access in the ACL are not affected by an Authors field. Authors fields affect only users who have Author access in the ACL....".
That means that a manager or an editor in England will still be able to update Spanish documents (if they are allowed to read them). Unfortunately those are often the same people that best know their way around QueryOpen and QueryModeChange!
shuboarderAuthor Commented:
Yes, thanks for confirming that CRAK, I had already read that in designer help, and hence this question...

From what I understand I can't really achieve this with author fields and roles.

Perhaps there is a way to hide documents in a view based on a formula?
Sjef BosmanGroupware ConsultantCommented:
I assumed you want the documents to be readable by all. But if that's not true, you  might need some Reader-fields as well. If someone is not a Reader and he's not an Author of a document (according to Reader and Author fields), he won't even see the document. Author fields only work for people with Author-rights, but Reader fields are always there.
The readers field also can be overriden by Full access administrator.

Those are the options that we all generally apply, shuboarder.
Don't be afraid to do the same! Current editors will hardly be able to tell the difference if their access rights are reduced author. Unless the're used to editing foreign doc's of course.
And regarding designers and managers... oh well, if you can't trust people having that kind of access in your database, why allow them to edit the design and/or change the ACL in the first place? You will definately need people with such privilages in a database. Just don't allow virtually everyone to have such access levels. You'll only need a handfull of them world wide!

Another thing we all generally do (anyone telling you different is probably lying) is screw up and lose access to documents, the first few times we fiddle around with reader- and author rights to documents.
Two bits of advice to avoid drama:
1) Always make those fields multi value and always include an "escape" userrole (e.g. "[Admin]") in the list of values.
2) Always test your intentions on dummy data first; do not experiment on live data that your collegues may need later on.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sjef BosmanGroupware ConsultantCommented:
My ideas for the ACL, combined with roles and Author/Reader fields:
- 2-3 Managers, for just in case
- 0-1 Designers (in an operational database)
- 1-3 Editors per functional unit of your company, people who know what they are doing
- and the other mortals as Author
Most of these can be in groups, in order to keep the ACL tidy.

An addition to CRAK's post:
1a) You might use 2 independent Readers fields: one computed or editable, the other one Computed when Composed, and filled with a standard role "[Manager]"
CRAK & sjef,
I agree with both of you.
My posts were just hard facts (the orig question was about restricting access - now he knows all).


I'm not sure how to respond.... You make it sound as if you have the impression that we disagree with you!?
Please reread my 1st comment: you've moved this question in the right direction (both the the country component- and roles approach would work great)! I only added a bit more detail.
shuboarderAuthor Commented:
Something that has been suggested to me is that I base the database on a template.
This way I will only actually store each regions documents on their server.

What does everyone think to this idea?

The obvious downside I see, is having all the separate databases. Personally I would rather have it all kept together,
but is there some kind of benefit here?
Sjef BosmanGroupware ConsultantCommented:
Whether you use a template or not has little to do with your problem. A template only serves to simplify maintenance. In this case, multiple databases based on the same template could be a way. I agree with you that it won't be possible to see all documents in all databases at the same time.

A similar solution is to use one large central database (hub) with many decentralized replicas (spokes). Each spoke replica will only contain the documents required for that country/region. To achieve this, you need to use a different replication formula per spoke replica. Can be done, it's not to difficult, and it also reduces communication between the hub and spoke servers. Nevertheless, the method is more complex because it relies on a administrative action. It's not something you can enforce in the design.
shuboarderAuthor Commented:
Interesting idea...

I'm thinking now though that this may be even less secure (like you say has little to do with my problem)
seeing as a site could open the database on another site's server and access/edit documents that way.

I'll have a chat with our admins and see what they say.
Sjef BosmanGroupware ConsultantCommented:
> open the database on another site's server
... only if you allow them access to that server; and they should know how to reach that server
shuboarderAuthor Commented:

time to close this post. Thanks for all your suggestions!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.