File: phpsecureweb/sql-scripts/security-mysql.sql

Recommend this page to a friend!
  Classes of Bulent Tezcan  >  phpsecureweb  >  phpsecureweb/sql-scripts/security-mysql.sql  >  Download  
File: phpsecureweb/sql-scripts/security-mysql.sql
Role: Configuration script
Content type: text/plain
Description: sql commands for MySQL database
Class: phpsecureweb
Allow users certain rights, login with password.
Author: By
Last change: wrong folder
Date: 17 years ago
Size: 7,757 bytes


Class file image Download
-- 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)

    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

    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','','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','','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','','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,'',2,'',1);
insert into log (timestamp,ip,accountid,username,activityid) values(1022630120,'',2,'test',2);

For more information send a message to info at phpclasses dot org.