# Defining access right for each database entry

**URL:** <https://www.ruby-forum.com/t/defining-access-right-for-each-database-entry/139815>\
**Category:** Rails\
**Created:** [June 7, 2008, 3:30pm UTC](https://www.ruby-forum.com/t/defining-access-right-for-each-database-entry/139815 "2008-06-07T15:30:26Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [June 7, 2008, 3:30pm UTC](https://www.ruby-forum.com/t/defining-access-right-for-each-database-entry/139815/1 "2008-06-07T15:30:26Z")

</div>

I have a table with information about companies and a table  
with several users.

Now I need a solution where I can define access rights for each  
entry in the company table.

```
user1 | user2

```

* * *

## company1 ved | v

## company2 ved | ved

…

v = view  
e = edit  
d = delete

My suggestions was to define a extra table with somthing like this

company\_id 12  
user\_id 1  
rights 4

v = 1  
e = 2  
d = 3

So the entry above would mean that user 1 can view and delete (v = 1  
and d = 3)  
the companies information.

What do you think?

Are there any plugins out there?

I run Rails 2.1 and MySQL.

Thanx.

–  
Jochen

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [June 7, 2008, 9:08pm UTC](https://www.ruby-forum.com/t/defining-access-right-for-each-database-entry/139815/2 "2008-06-07T21:08:03Z")

</div>

On Jun 7, 2008, at 6:29 AM, Jochen kaechelin wrote:

> company2 ved | ved
> 
> v = 1  
> e = 2  
> d = 3
> 
> So the entry above would mean that user 1 can view and delete (v = 1  
> and d = 3)  
> the companies information.

I have designed several user management systems (and eventually  
merged all the features into one flexible, yet easy to use reusable  
system (got tired of rewriting them). Based on that experience, I  
have some observations.

First, does company1 and company2 imply the company the user works  
for? Or are company1 and 2 records just data records, and could just  
as easily be widet1 and widget2?

What I’m asking is whether you really need each user to have unique  
permissions per individual record of some data table (that’s a rather  
immense implication).

Let’s start with that question first.

–  
def gw  
writes\_at ‘www.railsdev.ws’  
end

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [June 8, 2008, 6:43am UTC](https://www.ruby-forum.com/t/defining-access-right-for-each-database-entry/139815/3 "2008-06-08T06:43:57Z")

</div>

Am 07.06.2008 um 21:07 schrieb Greg W.:

> > * * *
> > 
> > company\_id 12
> 
> I have designed several user management systems (and eventually  
> merged all the features into one flexible, yet easy to use reusable  
> system (got tired of rewriting them). Based on that experience, I  
> have some observations.
> 
> First, does company1 and company2 imply the company the user works  
> for? Or are company1 and 2 records just data records, and could just  
> as easily be widet1 and widget2?

No, the users did not work for the company. “company” could be  
replaced with anything else.

–  
Jochen

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [June 8, 2008, 11:42pm UTC](https://www.ruby-forum.com/t/defining-access-right-for-each-database-entry/139815/4 "2008-06-08T23:42:53Z")

</div>

On Jun 7, 2008, at 6:29 AM, Jochen kaechelin wrote:

> * * *
> 
> v = 1  
> e = 2  
> d = 3
> 
> So the entry above would mean that user 1 can view and delete (v = 1  
> and d = 3) the companies information.

Except that an entry of 3 could mean d only, or v + e – so a little  
better number system will be needed for that plan.

Being blind as to the exact data/domain nature, my first instinct is  
to say that having that high of a resolution where every user needs  
explicitly unique rights to every company is excessive. You’re  
essentially saying there is absolutely nothing in common among all  
companies and how they relate to users.

I may be wrong, but I would expect there to be some way to categorize  
companies from the perspective of the user: companies in a given  
arbitrary territory or classification – something that allows user2  
to always be able to “edit” a company based on some rule.

I have always ascribed to the notion that if a human can logic out  
something, so can a program. If you have people deciding based on  
rules (even if there’s a lot of exceptions) and not emotion, they can  
be codfied.

So, assuming that, I prefer to define two distinct categories of  
rights. First is privileges–the ability to view, edit, delete. No  
particular data assigned yet. A person either has the right to edit  
nothing at all, or they have the right to edit at least something. If  
so then the person gets an edit privilege.

Second, we determine what is allowed to be edited through what I call  
filters. If a person is allowed to view, well, _what_ can he view? I  
codify filters as query fragements with a label, table, field, match  
operator, and match value. So, a person is allowed to view records  
where the field “territory” in table “customers” is “equal” to  
“southwest.” That info can be used to modify queries on the fly to  
present lists of data the user is allowed to view, or in a mixed list  
where maybe he’s allowed to view two territories but edit only one of  
them, then that same filter spec can be used to conditionally enable  
Edit buttons/links.

By using certain data structures and UIs for defining users, it  
becomes very easy to modify the rules on the fly without impacting  
existing user definitions.

One of the advantages this system has is that it gets away from the  
classic concept of a group or a role – both of which I find to be  
utter failures everywhere I’ve used them. There’s _always_ an  
exception, and this system allows that. However, I can still embrace  
the idea of a user type (or role I guess), which allows me to define  
macros of default settings as a starting point to minimize the  
admin’s task of defining new users.

It’s kind of difficult to explain the whole system in a couple of  
email paragraphs, and I find that the system is unique enough that it  
takes most people a round or two to understand how it is different  
and see the advantages, but once they get it, it’s an “of course”  
moment. If it sounds like I’m striking a chord, let me know and I’ll  
delve further.

– gw

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [June 8, 2008, 5:08pm UTC](https://www.ruby-forum.com/t/defining-access-right-for-each-database-entry/139815/5 "2008-06-08T17:08:40Z")

</div>

> So the entry above would mean that user 1 can view and delete (v = 1  
> and d = 3)

Just an observation… you might want to use prime numbers as the  
value of v, e, and d then multiply the users permissions to arrive at  
their ‘rights’ value ({‘v’ =\> 3, ‘e’ =\> 5, ‘d’ =\> 7). That way to find  
out if a user has permission to some feature you can test if their  
rights value can be factored by the prime associated with that  
feature.

SH