scrBiQi4

1/*creates foo db*/
2
3delimiter ;
4
5drop database if exists foo_db;
6
7create database foo_db;
8
9show databases;
10
11/*
12create a user who will be the owner of the new database. This user will be used
13to create all the tables, views, stored procedures and other schema objects in 
14the foo_db database.
15
16grant all permissions to this user to enable them to do anything they want
17inside that database
18
19*/
20
21grant all on foo_db.* to 'foo_dbo'@'localhost' identified by 'basic';
22grant select on mysql.* to 'foo_dbo'@'localhost';
23
24/*
25
26now i have a web front end written in php but if i use the foo_dbo account
27to access the database any haxor who gets his hands on this user and pass could
28gain full control of my database - not good.
29
30so all i do is i create a new user called foo_usr who only has execute permissions
31on stored procs for the foo_db database. This means that if a haxor gets hold of
32this account the worst thing they can do is call some stored procs.
33
34Of course your front end code can also only call stored procs - THIS IS A GOOD THING, 
35much better than inline sql as your code will be much cleaner and perform better !!
36
37*/
38
39grant execute on foo_db.* TO 'foo_usr'@'localhost' identified by 'basic';
40
41
42flush privileges;
43
44select host, user from mysql.user;
45
46
47/************************************/
48/*Creates table users in foo db*/
49
50delimiter ;
51
52use foo_db;
53
54drop table if exists users;
55
56create table users
57(
58user_id int unsigned not null auto_increment primary key,
59username varchar(32) binary not null unique,
60created_date datetime not null
61)
62engine=InnoDB 
63default charset=latin1 collate latin1_swedish_ci;
64
65insert into users (username, created_date) values ('e0s',curdate());
66insert into users (username, created_date) values ('f00',curdate());
67insert into users (username, created_date) values ('Thomas_Jefferson',curdate());
68insert into users (username, created_date) values ('Benjamin_Franklin',curdate());
69
70select * from users;