Security advice for database design

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

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!

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?

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?

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

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.

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.

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

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?

The readers field also can be overriden by Full access administrator.

Marko

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

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.

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?

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.

> 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

Ok,

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