PHP, MySQL & Limiting Access

[Jay Moore]

Greetings, all!

I have a project for work, and I'm not sure how to efficiently do what I
need to do. I'm hoping someone out there can help.

Project is this: I'm creating a web-based interface where people at my
company (operators) can enter data for service calls. All data entered
is run thru one or more PHP scripts for error checking and then stored
in a MySQL database on a server here in the office.

What I'm looking to do is to limit access to certain operators so they
can only do certain functions (ex: add a service call, but not delete
one), whereas admins such as myself would have full access to
everything. Ideally, at some point in the future, we plan to roll this
interface out to our clients so they may do the same features within
their company. Obviously, they would only gain access to data related
to their company, as to protect the privacy of others.

Hierarchy would be something like this:

1. UberAdmins (such as myself)
-Have access to everything and to all commands.

2. Operators
-Have access to everything, but not all commands.

3. Our clients
-Have access to their data only, and to all commands.

4. Our clients' operators
-Have access to their data only, but not all commands.

The difficult thing is that our clients may run several businesses, so
they would have to have access to several groups. In essence, they'd
have multiple groups (their businesses) within a group (their group)
within a group (everything).

What is the easiest and most secure way to do something like this? I'm
not looking for actual code but merely suggestions. Please reply if
there is something I wasn't clear on.

TIA,
-Jay

[Zac Hester]

Re: PHP, MySQL & Limiting Access

Jay Moore wrote:[color=blue]
> Greetings, all!
>
> I have a project for work, and I'm not sure how to efficiently do what I
> need to do. I'm hoping someone out there can help.
>
> Project is this: I'm creating a web-based interface where people at my
> company (operators) can enter data for service calls. All data entered
> is run thru one or more PHP scripts for error checking and then stored
> in a MySQL database on a server here in the office.
>
> What I'm looking to do is to limit access to certain operators so they
> can only do certain functions (ex: add a service call, but not delete
> one), whereas admins such as myself would have full access to
> everything. Ideally, at some point in the future, we plan to roll this
> interface out to our clients so they may do the same features within
> their company. Obviously, they would only gain access to data related
> to their company, as to protect the privacy of others.
>
> Hierarchy would be something like this:
>
> 1. UberAdmins (such as myself)
> -Have access to everything and to all commands.
>
> 2. Operators
> -Have access to everything, but not all commands.
>
> 3. Our clients
> -Have access to their data only, and to all commands.
>
> 4. Our clients' operators
> -Have access to their data only, but not all commands.
>
> The difficult thing is that our clients may run several businesses, so
> they would have to have access to several groups. In essence, they'd
> have multiple groups (their businesses) within a group (their group)
> within a group (everything).
>
> What is the easiest and most secure way to do something like this? I'm
> not looking for actual code but merely suggestions. Please reply if
> there is something I wasn't clear on.
>
> TIA,
> -Jay
>[/color]

Hi Jay,

Try not to reinvent the wheel when designing user permissions. The most
simple to understand (and, IMO, most useful/powerful) permission scheme
is that used on UNIX-like operating systems. Since you said you will
have "groups" of users, this seems like the most logical thing to do.
Make each resource (viewing data, operating on data, etc) have its own
permission set associated with it. Then assign an owner user and an
owner group to each thing. (This can all be done in your database.)
For instance, you only want administrators to be able to delete a
service entry, so make the service entry "writable" by the admin group
and an admin member user.

I used a similar system for a community managed bulletin board (many
tiers of users and groups with all kinds of different permissions) and
it worked out really well.

As an example, each user (in a user table, I'm assuming) would need this
information stored:

username, member group, [attached groups]

Then, keep a list of permissions for each database function/resource:

user permission, group permission, world permission,
user owner, group owner

If you use a little relational database design, this will allow you to
link together users/groups with resources in nearly any conceivable way.

I would suggest doing the standard "read-write-execute" bits. Even
though you probably won't use the "execute" permission on a typical
database design, it keeps you sane if you're used to working with UNIX
and it's only 3 bits of extra data. There's even a nice MySQL data type
that makes life easy:

create table some_table (
id int not null auto_increment primary key,
user_perm set('execute',' write','read') not null default 'read',
group_perm set('execute',' write','read') default 0,
world_perm set('execute',' write','read') default 0
);

Then, get a user permission:

select user_perm+0 from some_table where id = 35;

This will return a decimal representation of the bit field (the "+0"
casts it to an integer type on return). So, if you have set 'read' and
'write' permissions for user 35, you will get a "6" returned from the
query since the bitfield is "110" (backwards from how thery are listed
since the low-order bits come first).

If you only make your permission sets three elements long, you will
always get the same numbers you would see in a UNIX filesystem. Running
a change of permission on a resource, could look like the same thing as
running "chmod" in a shell.

I would do some looking around in the manual of your DBMS to see how
sets work before diving into this scheme. If you're interested in
learning about the secure filesystem in UNIX, check the man pages (I
would start with `man chmod`).

HTH,
Zac

[Jay Moore]

Re: PHP, MySQL & Limiting Access

<snip Zac's reply>

Zac,

Thanks for the prompt reply. I appreciate the help. I had actually
kinda considered doing what you suggested, only I didn't know how to
actually DO it.

My next question would be, "How would I 'label' the data being entered
so it's associated with the proper user/group?"

I'm looking to keep the layout like so:

Admins
|
+- Operators
|
+- Our clients
|
+- Our clients' operators (1)
|
+- Our clients' operators (2)

and so forth.

I hope I'm making sense. ;)

-Jay