[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Security advice for database design

Posted on 2006-03-20
25
Medium Priority
?
300 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:shuboarder
  • 7
  • 7
  • 5
  • +2
25 Comments
 
LVL 22

Expert Comment

by:mbonaci
ID: 16234711
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,
Marko
0
 
LVL 13

Expert Comment

by:CRAK
ID: 16234869
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!
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16235108
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 22

Assisted Solution

by:mbonaci
mbonaci earned 600 total points
ID: 16235196
shuboarder,
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,
Marko
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16235323
Ok, our usernames appear as follows:

SERVERNAME/SITECODE/COMPANY NAME

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?
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 16235499
shuboarder,
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,
Marko
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16235919
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.
0
 
LVL 22

Assisted Solution

by:mbonaci
mbonaci earned 600 total points
ID: 16235971
shuboarder,
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
    user.Country
with that information and there you are.

Hope this helps,
Marko
0
 

Expert Comment

by:Tasajara
ID: 16240269
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.
0
 
LVL 13

Assisted Solution

by:CRAK
CRAK earned 700 total points
ID: 16240967
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)!
0
 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 700 total points
ID: 16241957
As CRAK said in his first comment: use Author-fields. Fooling around with QueryOpen is not the way to go.
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 16244808
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?

Marko
0
 
LVL 13

Assisted Solution

by:CRAK
CRAK earned 700 total points
ID: 16246139
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!
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16268829
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?
0
 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 700 total points
ID: 16269080
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.
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 16269376
The readers field also can be overriden by Full access administrator.

Marko
0
 
LVL 13

Accepted Solution

by:
CRAK earned 700 total points
ID: 16272883
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.
0
 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 700 total points
ID: 16274949
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]"
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 16278401
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).

Marko
0
 
LVL 13

Expert Comment

by:CRAK
ID: 16284039
Marko,

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.
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16298762
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?
0
 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 700 total points
ID: 16298932
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.
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16298984
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.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16299273
> 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
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16476426
Ok,

time to close this post. Thanks for all your suggestions!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month19 days, 7 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question