Third step : let's create some tables in our database

Okay, now we're going to create a few tables and sample rows for a user access management system. Here they are :

 

/*
 * Groups of users
 */
create table groups(
	gid int,
	group_name varchar(40),
	
	primary key (gid)
);
/*Create some groups...*/
insert into groups(gid, group_name) values (1, 'Administrators');
insert into groups(gid, group_name) values (2, 'Publishers');
insert into groups(gid, group_name) values (3, 'Visitors');

/*
 * Users
 */
create table users(
	uid int,
	user_login varchar(10),
	user_pass varchar(40),
	user_name  varchar(40),
	gid int,
	
	primary key (uid)
);
/*Create some users...*/
insert into users(uid, user_login, user_pass, user_name, gid) values (1, 'root', password('root'), 'The root man', 1);
insert into users(uid, user_login, user_pass, user_name, gid) values (2, 'nico', password('nico'), 'Nicolas', 2);
insert into users(uid, user_login, user_pass, user_name, gid) values (3, 'scott', password('tiger'), 'Scott', 2);
insert into users(uid, user_login, user_pass, user_name, gid) values (4, 'anonymous', password('anonymous'), 'Anonymous', 3);

/*
 * Different modules of our application
 */
create table modules(
	mid int,
	module_key varchar(10),
	module_name varchar(50),
	url varchar(255),

	primary key (mid)
);
/*Some functionalities of our application...*/
insert into modules(mid, module_key, module_name) values (1, 'UC', 'User creation');
insert into modules(mid, module_key, module_name) values (2, 'UAM', 'User access management');
insert into modules(mid, module_key, module_name) values (3, 'POST', 'Post messages');
insert into modules(mid, module_key, module_name) values (4, 'POST_ADM', 'Administer all posts');
insert into modules(mid, module_key, module_name) values (5, 'COM', 'Write comments');

/*
 * User individual access to the modules. 
 * If no row is found for a user and a module, then we check if the group has access. Else access is denied.
 */
create table useraccess(
	uid int,
	mid int,
	
	primary key (uid, mid)
);

/*
 * Groups access to the modules. 
 * If no row is found for a group and a module, then access is denied.
 */
create table groupaccess(
	gid int,
	mid int,
	
	primary key (gid, mid)
);

/* Administrators */
insert into groupaccess (gid, mid) values (1, 1);
insert into groupaccess (gid, mid) values (1, 2);
insert into groupaccess (gid, mid) values (1, 3);
insert into groupaccess (gid, mid) values (1, 4);
insert into groupaccess (gid, mid) values (1, 5);

/* Publishers */
insert into groupaccess (gid, mid) values (2, 3);
insert into groupaccess (gid, mid) values (2, 5);

/* Anonymous users */
insert into groupaccess (gid, mid) values (3, 5);

 

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <p> <pre>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.