[MACS-Dev] New Schema

[Blake <bl...@ba...>]

Here's a first run at the new schema, I'm sure it will need a few chages.

AIDs are now called name spaces. There is no separate table for users, 
groups, or name spaces. All three are now individual resource hierarchys in 
the resource table.

Because users and groups are now just resources, the distiction becomes 
blurred. You can--for example--treat a user as a group, and avoid needing to 
creaet a group for each user if the need arose. We will lay out the system as 
it makes sense to us, but this more flexable schema should allow us to chage 
things down the road with minimal trama.

The UPS and accompanying table are gone completely, replaced by the RPS and 
it's table. The keys are both resource ids, one for the resource to profile, 
the other for the resource representing the name space. Since name spaces are 
a hierarchy, there is no reason to have a separate key. There are both a 
numeric and string values, you must know which one you want when you retrieve it.

There are no more login method or user map tables. The login methods are name 
spaces, like "macs/method/ldap" and are applied directly to the users via the 
RPS. The value being the values that would have been in the user map table.

Now to retrofit the apps to use this schema...

-Bam


# $Id: macs.sql.mysql,v 1.12 2002/06/12 19:19:58 blake Exp $
# Users: macs, test, guest
# Groups: dmins, staff, guest
#   macs is in admin and staff, test is in staff, guest is in guest
# Resources: macsweb, macs/secure
#   admin can own macsweb, staff and gues can read macsweb,
#   guest can not read macsweb/secure


drop table macs_temp;
drop table macs_resource;
drop table macs_resource_profile;
drop table macs_resource_flag;
drop table macs_group_member;


create table macs_temp (
	id int unsigned default 0 not null auto_increment,
	name varchar(255) unique,
	charval varchar(255),
	numval int,
	primary key (id)
);


create table macs_resource (
	id int unsigned default 0 not null auto_increment,
	name varchar(255) not null,
	parent int unsigned not null,
	active tinyint unsigned default 1 not null,
	primary key (id),
	unique (name,parent)
);

insert into macs_resource(name, parent) values('macsweb', 0);
insert into macs_resource(name, parent) values('macs_user', 0);
insert into macs_resource(name, parent) values('macs_group', 0);
insert into macs_resource(name, parent) values('macs_name_space', 0);
update macs_resource set parent=id where parent=0;

delete from macs_temp where name = 'resparent';
insert into macs_temp (name, numval)
         select 'resparent', id from macs_resource
         where name = 'macsweb' and parent = id;
insert into macs_resource (name, parent)
         select 'secure', numval from macs_temp where name = 'resparent';

delete from macs_temp where name = 'resparent';
insert into macs_temp (name, numval)
         select 'resparent', id from macs_resource
	where name = 'macs_user' and parent = id;
insert into macs_resource (name, parent)
         select 'macs', numval from macs_temp where name = 'resparent';
insert into macs_resource (name, parent)
         select 'guest', numval from macs_temp where name = 'resparent';
insert into macs_resource (name, parent)
         select 'test', numval from macs_temp where name = 'resparent';

delete from macs_temp where name = 'resparent';
insert into macs_temp (name, numval)
         select 'resparent', id from macs_resource
	where name = 'macs_group' and parent = id;
insert into macs_resource (name, parent)
         select 'admin', numval from macs_temp where name = 'resparent';
insert into macs_resource (name, parent)
         select 'guest', numval from macs_temp where name = 'resparent';
insert into macs_resource (name, parent)
         select 'staff', numval from macs_temp where name = 'resparent';

delete from macs_temp where name = 'resparent';
insert into macs_temp (name, numval)
         select 'resparent', id from macs_resource
	where name = 'macs_name_space' and parent = id;
insert into macs_resource (name, parent)
         select 'macs', numval from macs_temp where name = 'resparent';

delete from macs_temp where name = 'resparent';
insert into macs_temp (name, numval)
         select 'resparent', a.id from macs_resource a, macs_resource b
	where a.name = 'macs' and a.parent = b.id
	and b.name = 'macs_name_space' and b.parent = b.id;
insert into macs_resource (name, parent)
         select 'user', numval from macs_temp where name = 'resparent';
insert into macs_resource (name, parent)
         select 'group', numval from macs_temp where name = 'resparent';
insert into macs_resource (name, parent)
         select 'method', numval from macs_temp where name = 'resparent';

delete from macs_temp where name = 'resparent';
insert into macs_temp (name, numval)
         select 'resparent', a.id
	from macs_resource a, macs_resource b, macs_resource c
	where a.name = 'user' and a.parent = b.id
	and b.name = 'macs' and b.parent = c.id
	and c.name = 'macs_name_space' and c.parent = c.id;
insert into macs_resource (name, parent)
         select 'name', numval from macs_temp where name = 'resparent';
insert into macs_resource (name, parent)
         select 'email', numval from macs_temp where name = 'resparent';
insert into macs_resource (name, parent)
         select 'login', numval from macs_temp where name = 'resparent';
insert into macs_resource (name, parent)
         select 'passwd', numval from macs_temp where name = 'resparent';
insert into macs_resource (name, parent)
         select 'active', numval from macs_temp where name = 'resparent';

delete from macs_temp where name = 'resparent';
insert into macs_temp (name, numval)
         select 'resparent', a.id
	from macs_resource a, macs_resource b, macs_resource c
	where a.name = 'group' and a.parent = b.id
	and b.name = 'macs' and b.parent = c.id
	and c.name = 'macs_name_space' and c.parent = c.id;
insert into macs_resource (name, parent)
         select 'name', numval from macs_temp where name = 'resparent';

delete from macs_temp where name = 'resparent';
insert into macs_temp (name, numval)
         select 'resparent', a.id
	from macs_resource a, macs_resource b, macs_resource c
	where a.name = 'method' and a.parent = b.id
	and b.name = 'macs' and b.parent = c.id
	and c.name = 'macs_name_space' and c.parent = c.id;
insert into macs_resource (name, parent)
         select 'internal', numval from macs_temp where name = 'resparent';
insert into macs_resource (name, parent)
         select 'test_ausd', numval from macs_temp where name = 'resparent';
insert into macs_resource (name, parent)
         select 'ldap', numval from macs_temp where name = 'resparent';


create table macs_resource_profile (
	resource_id int unsigned not null,
	name_space_id int unsigned not null,
	numval int,
	charval varchar(255),
	primary key (resource_id,name_space_id)
);

insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'MACS Admin'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'macs' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'name' and ba.parent = bb.id
	and bb.name = 'user' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'ma...@me...'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'macs' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'email' and ba.parent = bb.id
	and bb.name = 'user' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'macs'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'macs' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'login' and ba.parent = bb.id
	and bb.name = 'user' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'mayPtFHfAbUfs'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'macs' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'passwd' and ba.parent = bb.id
	and bb.name = 'user' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'macs'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'macs' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'internal' and ba.parent = bb.id
	and bb.name = 'method' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;

insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'Guest User'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'guest' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'name' and ba.parent = bb.id
	and bb.name = 'user' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'gu...@me...'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'guest' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'email' and ba.parent = bb.id
	and bb.name = 'user' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'guest'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'guest' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'login' and ba.parent = bb.id
	and bb.name = 'user' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'guVeRgi5kAY4k'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'guest' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'passwd' and ba.parent = bb.id
	and bb.name = 'user' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'guest'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'guest' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'internal' and ba.parent = bb.id
	and bb.name = 'method' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;

insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'Test User'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'test' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'name' and ba.parent = bb.id
	and bb.name = 'user' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'te...@me...'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'test' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'email' and ba.parent = bb.id
	and bb.name = 'user' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'test'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'test' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'login' and ba.parent = bb.id
	and bb.name = 'user' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'teH0wLIpW0gyQ'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'test' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'passwd' and ba.parent = bb.id
	and bb.name = 'user' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'test'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'test' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'internal' and ba.parent = bb.id
	and bb.name = 'method' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'usr2'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'test' and aa.parent = ab.id
	and ab.name = 'macs_user' and ab.parent = ab.id
	and ba.name = 'ldap' and ba.parent = bb.id
	and bb.name = 'method' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;

insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'Administrators'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'admin' and aa.parent = ab.id
	and ab.name = 'macs_group' and ab.parent = ab.id
	and ba.name = 'name' and ba.parent = bb.id
	and bb.name = 'group' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'Guests'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'guest' and aa.parent = ab.id
	and ab.name = 'macs_group' and ab.parent = ab.id
	and ba.name = 'name' and ba.parent = bb.id
	and bb.name = 'group' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;
insert into macs_resource_profile (resource_id, name_space_id, charval)
	select aa.id, ba.id, 'Staff'
	from macs_resource aa, macs_resource ab, macs_resource ba,
	macs_resource bb, macs_resource bc, macs_resource bd
	where aa.name = 'staff' and aa.parent = ab.id
	and ab.name = 'macs_group' and ab.parent = ab.id
	and ba.name = 'name' and ba.parent = bb.id
	and bb.name = 'group' and bb.parent = bc.id
	and bc.name = 'macs' and bc.parent = bd.id
	and bd.name = 'macs_name_space' and bd.parent = bd.id;


create table macs_resource_flag (
	resource_id int unsigned not null,
	group_id int unsigned not null,
	is_disabled tinyint unsigned default 0 not null,
	is_owner tinyint unsigned default 0 not null,
	is_admin tinyint unsigned default 0 not null,
	is_read tinyint unsigned default 0 not null,
	is_write tinyint unsigned default 0 not null,
	primary key (resource_id,group_id)
);

insert into macs_resource_flag (resource_id,
	group_id,
	is_disabled,
	is_owner,
	is_admin,
	is_read,
	is_write)
	select aa.id, ba.id, 0, 1, 0, 0, 0
	from macs_resource aa, macs_resource ba, macs_resource bb
	where aa.name = 'macs_user' and aa.parent = aa.id
	and ba.name = 'admin' and ba.parent = bb.id
	and bb.name = 'macs_group' and bb.parent = bb.id;
insert into macs_resource_flag (resource_id,
	group_id,
	is_disabled,
	is_owner,
	is_admin,
	is_read,
	is_write)
	select aa.id, ba.id, 0, 1, 0, 0, 0
	from macs_resource aa, macs_resource ba, macs_resource bb
	where aa.name = 'macs_group' and aa.parent = aa.id
	and ba.name = 'admin' and ba.parent = bb.id
	and bb.name = 'macs_group' and bb.parent = bb.id;
insert into macs_resource_flag (resource_id,
	group_id,
	is_disabled,
	is_owner,
	is_admin,
	is_read,
	is_write)
	select aa.id, ba.id, 0, 1, 0, 0, 0
	from macs_resource aa, macs_resource ba, macs_resource bb
	where aa.name = 'macs_name_space' and aa.parent = aa.id
	and ba.name = 'admin' and ba.parent = bb.id
	and bb.name = 'macs_group' and bb.parent = bb.id;
insert into macs_resource_flag (resource_id,
	group_id,
	is_disabled,
	is_owner,
	is_admin,
	is_read,
	is_write)
	select aa.id, ba.id, 0, 1, 0, 0, 0
	from macs_resource aa, macs_resource ba, macs_resource bb
	where aa.name = 'macsweb' and aa.parent = aa.id
	and ba.name = 'admin' and ba.parent = bb.id
	and bb.name = 'macs_group' and bb.parent = bb.id;
insert into macs_resource_flag (resource_id,
	group_id,
	is_disabled,
	is_owner,
	is_admin,
	is_read,
	is_write)
	select aa.id, ba.id, 0, 0, 0, 1, 0
	from macs_resource aa, macs_resource ba, macs_resource bb
	where aa.name = 'macsweb' and aa.parent = aa.id
	and ba.name = 'staff' and ba.parent = bb.id
	and bb.name = 'macs_group' and bb.parent = bb.id;
insert into macs_resource_flag (resource_id,
	group_id,
	is_disabled,
	is_owner,
	is_admin,
	is_read,
	is_write)
	select aa.id, ba.id, 0, 0, 0, 1, 0
	from macs_resource aa, macs_resource ba, macs_resource bb
	where aa.name = 'macsweb' and aa.parent = aa.id
	and ba.name = 'guest' and ba.parent = bb.id
	and bb.name = 'macs_group' and bb.parent = bb.id;
insert into macs_resource_flag (resource_id,
	group_id,
	is_disabled,
	is_owner,
	is_admin,
	is_read,
	is_write)
	select aa.id, ba.id, 0, 0, 0, 2, 0
	from macs_resource aa,  macs_resource ab,
	macs_resource ba, macs_resource bb
	where aa.name = 'secure' and aa.parent = ab.id
	and ab.name = 'macsweb' and ab.parent = ab.id
	and ba.name = 'guest' and ba.parent = bb.id
	and bb.name = 'macs_group' and bb.parent = bb.id;


create table macs_group_member (
	group_id int unsigned not null,
	member_id int unsigned not null,
	type tinyint unsigned not null,
	unique (group_id, member_id)
);

insert into macs_group_member(group_id, member_id, type)
	select aa.id, ba.id, 1
	from macs_resource aa,  macs_resource ab,
	macs_resource ba, macs_resource bb
	where aa.name = 'admin' and aa.parent = ab.id
	and ab.name = 'macs_group' and ab.parent = ab.id
	and ba.name = 'macs' and ba.parent = bb.id
	and bb.name = 'macs_user' and bb.parent = bb.id;
insert into macs_group_member(group_id, member_id, type)
	select aa.id, ba.id, 1
	from macs_resource aa,  macs_resource ab,
	macs_resource ba, macs_resource bb
	where aa.name = 'staff' and aa.parent = ab.id
	and ab.name = 'macs_group' and ab.parent = ab.id
	and ba.name = 'macs' and ba.parent = bb.id
	and bb.name = 'macs_user' and bb.parent = bb.id;
insert into macs_group_member(group_id, member_id, type)
	select aa.id, ba.id, 1
	from macs_resource aa,  macs_resource ab,
	macs_resource ba, macs_resource bb
	where aa.name = 'staff' and aa.parent = ab.id
	and ab.name = 'macs_group' and ab.parent = ab.id
	and ba.name = 'test' and ba.parent = bb.id
	and bb.name = 'macs_user' and bb.parent = bb.id;
insert into macs_group_member(group_id, member_id, type)
	select aa.id, ba.id, 1
	from macs_resource aa,  macs_resource ab,
	macs_resource ba, macs_resource bb
	where aa.name = 'guest' and aa.parent = ab.id
	and ab.name = 'macs_group' and ab.parent = ab.id
	and ba.name = 'guest' and ba.parent = bb.id
	and bb.name = 'macs_user' and bb.parent = bb.id;