Role Base Security Data Base Design (MYSQL Script)

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 25, 2011 at 07:58 PM
-- Server version: 5.1.36
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `security`
--

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

--
-- Table structure for table `roles`
--

CREATE TABLE IF NOT EXISTS `roles` (
`pk_role_id` varchar(50) NOT NULL,
`role_name` varchar(50) NOT NULL,
`created_by` varchar(50) NOT NULL,
`created_datettime` datetime NOT NULL,
`modified_by` varchar(50) NOT NULL,
`modified_datetime` datetime NOT NULL,
PRIMARY KEY (`pk_role_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`pk_role_id`, `role_name`, `created_by`, `created_datettime`, `modified_by`, `modified_datetime`) VALUES
('psu1001', 'prod_support', 'andrew', '2011-08-20 11:51:58', 'andrew', '2011-08-20 11:52:02'),
('ops1001', 'order pro specialist', 'andrew', '2011-08-20 11:52:51', 'vdsi', '2011-08-20 11:52:55');

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

--
-- Table structure for table `role_screen`
--

CREATE TABLE IF NOT EXISTS `role_screen` (
`pk_role_screen_id` varchar(50) NOT NULL,
`fk_role_id` varchar(50) NOT NULL,
`fk_screen_id` varchar(50) NOT NULL,
`created_by` varchar(50) NOT NULL,
`created_datettime` datetime NOT NULL,
`modified_by` varchar(50) NOT NULL,
`modified_datetime` datetime NOT NULL,
PRIMARY KEY (`pk_role_screen_id`),
KEY `fk_role_id` (`fk_role_id`),
KEY `fk_screen_id` (`fk_screen_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `role_screen`
--

INSERT INTO `role_screen` (`pk_role_screen_id`, `fk_role_id`, `fk_screen_id`, `created_by`, `created_datettime`, `modified_by`, `modified_datetime`) VALUES
('1', 'psu1001', 'oes1001', 'andrew', '2011-08-20 12:01:35', 'andrew', '2011-08-20 12:01:40'),
('2', 'psu1001', 'psu1001', 'andrew', '2011-08-20 12:02:11', 'andrew', '2011-08-20 12:02:17'),
('3', 'oes1001', 'oes1001', 'andrew', '2011-08-20 12:03:46', 'andrew', '2011-08-20 12:03:50');

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

--
-- Table structure for table `screens`
--

CREATE TABLE IF NOT EXISTS `screens` (
`screen_id` varchar(50) NOT NULL,
`screen_name` varchar(50) NOT NULL,
`created_by` varchar(50) NOT NULL,
`created_datettime` datetime NOT NULL,
`modified_by` varchar(50) NOT NULL,
`modified_datetime` datetime NOT NULL,
PRIMARY KEY (`screen_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `screens`
--

INSERT INTO `screens` (`screen_id`, `screen_name`, `created_by`, `created_datettime`, `modified_by`, `modified_datetime`) VALUES
('oes1001', 'order_entry', 'andew', '2011-08-20 11:56:47', 'andrew', '2011-08-20 11:56:57'),
('pss1001', 'production support', 'andrewq', '2011-08-20 11:58:01', 'andrew', '2011-08-20 11:58:08'),
('1111', 'scree', 'andrew', '2011-08-20 11:56:47', 'andrew', '2011-08-20 11:56:47');

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

--
-- Table structure for table `userinfo`
--

CREATE TABLE IF NOT EXISTS `userinfo` (
`pk_user_id` varchar(50) NOT NULL,
`user_name` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`email_id` varchar(50) NOT NULL,
`current_designation` varchar(50) NOT NULL,
`current_role` varchar(50) NOT NULL,
`created_by` varchar(50) NOT NULL,
`created_datettime` datetime NOT NULL,
`modified_by` varchar(50) NOT NULL,
`modified_datetime` datetime NOT NULL,
PRIMARY KEY (`pk_user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `userinfo`
--

INSERT INTO `userinfo` (`pk_user_id`, `user_name`, `password`, `email_id`, `current_designation`, `current_role`, `created_by`, `created_datettime`, `modified_by`, `modified_datetime`) VALUES
('z381466', 'anton', 'anton', 'anton@gmail.com', 'developer', 'developer', 'andrew ', '2011-08-20 11:50:46', 'andrew', '2011-08-20 11:50:46'),
('z381455', 'andrews ', 'andrvdsiews', 'andrew_india@yahoo.com', 'analyst', 'developer', 'vdsi', '2011-08-20 11:49:22', 'andrew', '2011-08-20 11:49:22'),
('oiu', 'oiuo', 'oiu', 'ioou', '98', '9809', '098', '2011-11-11 00:00:00', '890', '2011-11-11 00:00:00');

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

--
-- Table structure for table `user_role`
--

CREATE TABLE IF NOT EXISTS `user_role` (
`pk_user_role_id` varchar(50) NOT NULL,
`fk_role_id` varchar(50) NOT NULL,
`fk_user_id` varchar(50) NOT NULL,
`created_by` varchar(50) NOT NULL,
`created_datettime` datetime NOT NULL,
`modified_by` varchar(50) NOT NULL,
`modified_datetime` datetime NOT NULL,
PRIMARY KEY (`pk_user_role_id`),
KEY `fk_role_id` (`fk_role_id`),
KEY `fk_user_id` (`fk_user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_role`
--

INSERT INTO `user_role` (`pk_user_role_id`, `fk_role_id`, `fk_user_id`, `created_by`, `created_datettime`, `modified_by`, `modified_datetime`) VALUES
('1', 'psu1001', 'z381455', 'vdsi', '2011-08-20 11:55:23', 'andrew', '2011-08-20 11:55:33'),
('2', 'ops1001', 'z381455', 'andewq', '2011-08-20 11:55:54', 'vdsi', '2011-08-20 11:55:58');