-- Once you create the database like mysql create database security, run this
-- script like : mysql security < security-mysql.sql from the command line
-- After the first run un comment the DROP table statements, so if you
-- run it again to clean-up your database, you wont get any
-- table already exists errors.
--
--
--DROP TABLE accounts, groups, actions, groupaccounts, groupactions, log, activity, configuration;
--DROP TABLE accountid_seq, groupid_seq, actionid_seq, activityid_seq;
CREATE TABLE accountid_seq (
id int
);
CREATE TABLE groupid_seq (
id int
);
CREATE TABLE actionid_seq (
id int
);
CREATE TABLE activityid_seq (
id int
);
CREATE TABLE accounts (
accountid int AUTO_INCREMENT NOT NULL,
firstname text NOT NULL,
lastname text NOT NULL,
initials text,
username text NOT NULL,
password text NOT NULL,
email text NOT NULL,
hintquestion text,
hintanswer text,
expired tinyint,
expireddate integer,
tries int,
lasttrieddate int,
PRIMARY KEY (accountid)
);
CREATE TABLE groups (
groupid integer NOT NULL AUTO_INCREMENT,
groupname text NOT NULL,
hierarchy integer NOT NULL,
PRIMARY KEY (groupid)
);
CREATE TABLE actions (
actionid integer NOT NULL AUTO_INCREMENT,
actionname text NOT NULL,
PRIMARY KEY (actionid)
);
CREATE TABLE activity (
activityid integer NOT NULL AUTO_INCREMENT,
description text NOT NULL,
PRIMARY KEY (activityid)
);
CREATE TABLE groupaccounts (
groupid integer NOT NULL,
accountid integer NOT NULL
);
CREATE TABLE groupactions (
groupid integer NOT NULL,
actionid integer NOT NULL
);
CREATE TABLE log (
timestamp integer,
ip varchar(16) NOT NULL DEFAULT '----------------',
accountid integer,
username text,
activityid int
);
CREATE TABLE configuration (
md5 tinyint DEFAULT 1 NOT NULL,
bad_attempts_max int DEFAULT 5 NOT NULL,
bad_attempts_wait int DEFAULT 300 NOT NULL,
log_activities tinyint DEFAULT 1 NOT NULL,
timeout int DEFAULT 900 NOT NULL,
error_reporting tinyint DEFAULT 1 NOT NULL,
stylesheet text NOT NULL
);
insert into configuration (md5,bad_attempts_max,bad_attempts_wait,log_activities,timeout,error_reporting,stylesheet) values(1,5,300,1,900,1,'Corona');
insert into accounts (accountid,firstname,lastname,initials,username,password,email,hintquestion,hintanswer,expired,expireddate,tries,lasttrieddate) values(1,'administrator','admin','aa','admin','5ebe2294ecd0e0f08eab7690d2a6ee69','admin@mydomain.com','how are you?','very well',0,null,0,null);
insert into accounts (accountid,firstname,lastname,initials,username,password,email,hintquestion,hintanswer,expired,expireddate,tries,lasttrieddate) values(NULL,'test user','brown','tb','test','5ebe229lmy60ekgferab7690d2a6ee69','test@mydomain.com','what is your pets name','black',0,null,0,null);
insert into accounts (accountid,firstname,lastname,initials,username,password,email,hintquestion,hintanswer,expired,expireddate,tries,lasttrieddate) values(NULL,'Joe','Blow','jb','joe','5144abbeb84239f038276f8e36b2251d','joe@mydomain.com','what is my last name','blow',0,null,0,null);
-- This is for advancing the AUTO_INCREMENT field on accountid
insert into accountid_seq (id) VALUES(4);
insert into groups (groupid,groupname,hierarchy) VALUES(NULL,'Admins',1);
insert into groups (groupid,groupname,hierarchy) VALUES(NULL,'Power Users',2);
insert into groups (groupid,groupname,hierarchy) VALUES(NULL,'Users',3);
insert into groups (groupid,groupname,hierarchy) VALUES(NULL,'Guests',4);
-- This is for advancing the AUTO_INCREMENT field on groupid
insert into groupid_seq (id) VALUES(4);
insert into groupaccounts (groupid,accountid) values(1,1);
insert into groupaccounts (groupid,accountid) values(2,2);
insert into actions (actionid,actionname) values(NULL,'Show Admin Menu');
insert into actions (actionid,actionname) values(NULL,'Add Action');
insert into actions (actionid,actionname) values(NULL,'Delete Action');
insert into actions (actionid,actionname) values(NULL,'Modify Action');
insert into actions (actionid,actionname) values(NULL,'View Action');
insert into actions (actionid,actionname) values(NULL,'Add Group');
insert into actions (actionid,actionname) values(NULL,'Delete Group');
insert into actions (actionid,actionname) values(NULL,'Modify Group');
insert into actions (actionid,actionname) values(NULL,'View Group');
insert into actions (actionid,actionname) values(NULL,'Add Account');
insert into actions (actionid,actionname) values(NULL,'Delete Account');
insert into actions (actionid,actionname) values(NULL,'Modify Account');
insert into actions (actionid,actionname) values(NULL,'View Account');
insert into actions (actionid,actionname) values(NULL,'Add Activity');
insert into actions (actionid,actionname) values(NULL,'Delete Activity');
insert into actions (actionid,actionname) values(NULL,'Modify Activity');
insert into actions (actionid,actionname) values(NULL,'View Activity');
insert into actions (actionid,actionname) values(NULL,'View Log');
insert into actions (actionid,actionname) values(NULL,'Modify Config');
insert into actions (actionid,actionname) values(NULL,'Insert Group Accounts');
insert into actions (actionid,actionname) values(NULL,'Delete Group Accounts');
insert into actions (actionid,actionname) values(NULL,'view MD5');
-- This is for advancing the AUTO_INCREMENT field on actionid
insert into actionid_seq (id) VALUES(22);
insert into activity (activityid,description) values(NULL,'Failed Login');
insert into activity (activityid,description) values(NULL,'Failed Admin Login');
insert into activity (activityid,description) values(NULL,'Successful Login');
-- This is for advancing the AUTO_INCREMENT field on activityid
insert into activityid_seq (id) VALUES(3);
insert into groupactions (groupid,actionid) values(1,1);
insert into groupactions (groupid,actionid) values(1,2);
insert into groupactions (groupid,actionid) values(1,3);
insert into groupactions (groupid,actionid) values(1,4);
insert into groupactions (groupid,actionid) values(1,5);
insert into groupactions (groupid,actionid) values(1,6);
insert into groupactions (groupid,actionid) values(1,7);
insert into groupactions (groupid,actionid) values(1,8);
insert into groupactions (groupid,actionid) values(1,9);
insert into groupactions (groupid,actionid) values(1,10);
insert into groupactions (groupid,actionid) values(1,11);
insert into groupactions (groupid,actionid) values(1,12);
insert into groupactions (groupid,actionid) values(1,13);
insert into groupactions (groupid,actionid) values(1,14);
insert into groupactions (groupid,actionid) values(1,15);
insert into groupactions (groupid,actionid) values(1,16);
insert into groupactions (groupid,actionid) values(1,17);
insert into groupactions (groupid,actionid) values(1,18);
insert into groupactions (groupid,actionid) values(1,19);
insert into groupactions (groupid,actionid) values(1,20);
insert into groupactions (groupid,actionid) values(1,21);
insert into groupactions (groupid,actionid) values(1,22);
insert into groupactions (groupid,actionid) values(2,1);
insert into groupactions (groupid,actionid) values(2,2);
insert into groupactions (groupid,actionid) values(2,3);
insert into groupactions (groupid,actionid) values(2,4);
insert into groupactions (groupid,actionid) values(2,5);
insert into groupactions (groupid,actionid) values(2,18);
insert into log (timestamp,ip,accountid,username,activityid) values(1022640120,'64.27.112.77',2,'',1);
insert into log (timestamp,ip,accountid,username,activityid) values(1022630120,'10.8.1.111',2,'test',2);
|
Stumble It!
Bookmark in del.icio.us
Download
