Setting permissions and access levels

Databases
1

I have an application that has several modules (think of modules as different pages), each module has a set of permissions; view, add, edit, delete

I want each user role to have privileges for each module, for example

Role A Permissions

Module 1 -> view
Module 2 -> add, edit
Module 3 -> view, add, edit, delete
etc.

How can I design the database to support that and how would I go about implementing it . I am new to db design.

2

I have come up with something like this.But I need to add a user group too.please help me on that


SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Table structure for table `perm_data`
-- 

CREATE TABLE `perm_data` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `permKey` varchar(30) NOT NULL,
  `permName` varchar(30) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `permKey` (`permKey`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=39 ;

-- --------------------------------------------------------

--
-- Table structure for table `role_data`
-- 

CREATE TABLE `role_data` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `roleName` varchar(20) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `roleName` (`roleName`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

--
-- Table structure for table `role_perms`
-- 

CREATE TABLE `role_perms` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `roleID` bigint(20) NOT NULL,
  `permID` bigint(20) NOT NULL,
  `value` tinyint(1) NOT NULL default '0',
  `addDate` datetime NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `roleID_2` (`roleID`,`permID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `user_data`
-- 

CREATE TABLE `user_data` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(20) NOT NULL,
  `password` text NOT NULL,
  `name` tinytext NOT NULL,
  `address` tinytext,
  `address2` tinytext,
  `city` tinytext,
  `state` tinytext,
  `zip` tinytext,
  `cellphone` tinytext,
  `telephone` tinytext,
  `email` tinytext,
  `aim` tinytext,
  `yahoo` tinytext,
  `icq` tinytext,
  `other` tinytext,
  `dateAdded` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  KEY `Username` (`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `user_perms`
-- 

CREATE TABLE `user_perms` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `userID` bigint(20) NOT NULL,
  `permID` bigint(20) NOT NULL,
  `value` tinyint(1) NOT NULL default '0',
  `addDate` datetime NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `userID` (`userID`,`permID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `user_roles`
-- 

CREATE TABLE `user_roles` (
  `userID` bigint(20) NOT NULL,
  `roleID` bigint(20) NOT NULL,
  `addDate` datetime NOT NULL,
  UNIQUE KEY `userID` (`userID`,`roleID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;