Community   SpringSource   Projects    Downloads    Documentation    Forums    Training   Exchange   Blogs

Go Back   Spring Community Forums > Core Spring Projects > Spring Security
Reload this Page MySQL DDL
Register FAQ Calendar Search Today's Posts Mark Forums Read

Closed Thread
 
Thread Tools Display Modes
  #1  
Old Sep 29th, 2004, 02:47 PM
analogueboy analogueboy is offline
Member
  
Join Date: Sep 2004
Posts: 52
Default MySQL DDL
Has anybody create the MySQL DDL? If I create it, would anybody else be interested in a copy?
  #2  
Old Sep 29th, 2004, 04:48 PM
analogueboy analogueboy is offline
Member
  
Join Date: Sep 2004
Posts: 52
Default Done
I've created the DDL and DML if anyone wants a copy. Not too many changes, tested using the quick start app and everything is running fine.
  #3  
Old Sep 30th, 2004, 06:19 AM
Ben Alex Ben Alex is offline
Senior Member
Spring Team
  
Join Date: Aug 2004
Location: Sydney, Australia
Posts: 2,423
Default
Feel free to post them to this thread. That way people can search and find them. It's something I don't think belongs in CVS, as it would mean I need to keep them current. Besides, I use Postgres :wink:
  #4  
Old Nov 23rd, 2004, 10:10 PM
aaron8tang aaron8tang is offline
Junior Member
  
Join Date: Nov 2004
Location: Beijing,China
Posts: 18
Default
MySQL script embedded in contact example snapshot code check out from cvs, sorry for the little bit messy post :wink:

Code:
        template.execute(
        "DROP TABLE IF EXISTS USERS;");
    template.execute(
        "DROP TABLE IF EXISTS AUTHORITIES;");            
    template.execute(
        "DROP TABLE IF EXISTS ACL_OBJECT_IDENTITY;");            
    template.execute(
        "DROP TABLE IF EXISTS ACL_PERMISSION;");            
    template.execute(
        "DROP TABLE IF EXISTS CONTACTS;");          
        template.execute(
        "CREATE TABLE CONTACTS(ID INT NOT NULL PRIMARY KEY, CONTACT_NAME VARCHAR(50) NOT NULL, EMAIL VARCHAR(50) NOT NULL)");
    template.execute(
        "INSERT INTO contacts VALUES (1, 'John Smith', 'john@somewhere.com');"); // marissa
    template.execute(
        "INSERT INTO contacts VALUES (2, 'Michael Citizen', 'michael@xyz.com');"); // marissa
    template.execute(
        "INSERT INTO contacts VALUES (3, 'Joe Bloggs', 'joe@demo.com');"); // marissa
    template.execute(
        "INSERT INTO contacts VALUES (4, 'Karen Sutherland', 'karen@sutherland.com');"); // marissa + dianne + scott
    template.execute(
        "INSERT INTO contacts VALUES (5, 'Mitchell Howard', 'mitchell@abcdef.com');"); // dianne
    template.execute(
        "INSERT INTO contacts VALUES (6, 'Rose Costas', 'rose@xyz.com');"); // dianne + scott
    template.execute(
        "INSERT INTO contacts VALUES (7, 'Amanda Smith', 'amanda@abcdef.com');"); // scott
    template.execute(
        "INSERT INTO contacts VALUES (8, 'Cindy Smith', 'cindy@smith.com');"); // dianne + scott
    template.execute(
        "INSERT INTO contacts VALUES (9, 'Jonathan Citizen', 'jonathan@xyz.com');"); // scott
    template.execute(
        "CREATE TABLE ACL_OBJECT_IDENTITY(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, OBJECT_IDENTITY VARCHAR(250) NOT NULL UNIQUE,PARENT_OBJECT INT,ACL_CLASS VARCHAR(250) NOT NULL,FOREIGN KEY(PARENT_OBJECT) REFERENCES ACL_OBJECT_IDENTITY(ID));");
	template.execute(
		"ALTER TABLE ACL_OBJECT_IDENTITY AUTO_INCREMENT = 100;");
    template.execute(
        "INSERT INTO acl_object_identity VALUES (1, 'sample.contact.Contact:1', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
    template.execute(
        "INSERT INTO acl_object_identity VALUES (2, 'sample.contact.Contact:2', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
    template.execute(
        "INSERT INTO acl_object_identity VALUES (3, 'sample.contact.Contact:3', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
    template.execute(
        "INSERT INTO acl_object_identity VALUES (4, 'sample.contact.Contact:4', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
    template.execute(
        "INSERT INTO acl_object_identity VALUES (5, 'sample.contact.Contact:5', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
    template.execute(
        "INSERT INTO acl_object_identity VALUES (6, 'sample.contact.Contact:6', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
    template.execute(
        "INSERT INTO acl_object_identity VALUES (7, 'sample.contact.Contact:7', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
    template.execute(
        "INSERT INTO acl_object_identity VALUES (8, 'sample.contact.Contact:8', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
    template.execute(
        "INSERT INTO acl_object_identity VALUES (9, 'sample.contact.Contact:9', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
    template.execute(
        "CREATE TABLE ACL_PERMISSION(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,ACL_OBJECT_IDENTITY INT NOT NULL,RECIPIENT VARCHAR(100) NOT NULL,MASK INT NOT NULL,FOREIGN KEY(ACL_OBJECT_IDENTITY) REFERENCES ACL_OBJECT_IDENTITY(ID));");
	template.execute(
		"ALTER TABLE ACL_PERMISSION AUTO_INCREMENT = 100;");
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 1, 'marissa', 1);"); // administer
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 2, 'marissa', 2);"); // read
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 3, 'marissa', 22);"); // read+write+delete 
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 4, 'marissa', 1);"); // administer
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 4, 'dianne', 1);"); // administer
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 4, 'scott', 2);"); // read 
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 5, 'dianne', 2);"); // read
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 6, 'dianne', 22);"); // read+write+delete
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 6, 'scott', 2);"); // read
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 7, 'scott', 1);"); // administer
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 8, 'dianne', 2);"); // read
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 8, 'scott', 2);"); // read
    template.execute(
        "INSERT INTO acl_permission VALUES (null, 9, 'scott', 22);"); // read+write+delete
    template.execute(
        "CREATE TABLE USERS(USERNAME VARCHAR(50) NOT NULL PRIMARY KEY,PASSWORD VARCHAR(50) NOT NULL,ENABLED BOOL NOT NULL);");
    template.execute(
        "CREATE TABLE AUTHORITIES(USERNAME VARCHAR(50) NOT NULL,AUTHORITY VARCHAR(50) NOT NULL,FOREIGN KEY (USERNAME) REFERENCES USERS(USERNAME));");
    template.execute(
        "CREATE UNIQUE INDEX IX_AUTH_USERNAME ON AUTHORITIES(USERNAME,AUTHORITY);");

    /*
               Passwords encoded using MD5, NOT in Base64 format, with null as salt
               Encoded password for marissa is "koala"
               Encoded password for dianne is "emu"
               Encoded password for scott is "wombat"
               Encoded password for peter is "opal" (but user is disabled)
    
     */
    template.execute(
        "INSERT INTO USERS VALUES('marissa','a564de63c2d0da68cf47586ee05984d7',1);");
    template.execute(
        "INSERT INTO USERS VALUES('dianne','65d15fe9156f9c4bbffd98085992a44e',1);");
    template.execute(
        "INSERT INTO USERS VALUES('scott','2b58af6dddbd072ed27ffc86725d7d3a',1);");
    template.execute(
        "INSERT INTO USERS VALUES('peter','22b5c9accc6e1ba628cedc63a72d57f8',0);");
    template.execute(
        "INSERT INTO AUTHORITIES VALUES('marissa','ROLE_USER');");
    template.execute(
        "INSERT INTO AUTHORITIES VALUES('marissa','ROLE_SUPERVISOR');");
    template.execute(
        "INSERT INTO AUTHORITIES VALUES('dianne','ROLE_USER');");
    template.execute("INSERT INTO AUTHORITIES VALUES('scott','ROLE_USER');");
    template.execute("INSERT INTO AUTHORITIES VALUES('peter','ROLE_USER');");
  #5  
Old Mar 10th, 2005, 01:10 PM
jdoklovic jdoklovic is offline
Junior Member
  
Join Date: Feb 2005
Posts: 24
Default
here's a pretty version you can just copy and save to a file.....

Code:
DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS authorities;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS acl_permission;
DROP TABLE IF EXISTS acl_object_identity;

CREATE TABLE users (
	username varchar(50) NOT NULL,
	password varchar(50) NOT NULL,
	enabled bool NOT NULL,
	
	constraint PK_Username primary key (username)
) TYPE=InnoDB;


CREATE TABLE authorities (
	username varchar(50) NOT NULL,
	authority varchar(50) NOT NULL,
	
	constraint FK_Username foreign key (username) references users(username),
	constraint IX_AuthUsername unique key (username, authority)
) TYPE=InnoDB;
        

CREATE TABLE acl_object_identity (
  	id int NOT NULL auto_increment,
  	object_identity varchar(250) NOT NULL,
  	parent_object int,
  	acl_class varchar(250) NOT NULL,
  	
  	index (parent_object),
  	
  	constraint PK_Id primary key (id),
	constraint FK_ParentObject foreign key (parent_object) references acl_object_identity(id)

) TYPE=InnoDB;


CREATE TABLE acl_permission (
  	id int NOT NULL auto_increment,
  	acl_object_identity int NOT NULL,
  	recipient varchar(100) NOT NULL,
  	mask int NOT NULL,
  	
  	constraint PK_Id primary key (id),
	constraint FK_ACLIdentity foreign key (acl_object_identity) references acl_object_identity(id),
	constraint IX_URecipient unique key (acl_object_identity, recipient)

) TYPE=InnoDB;

CREATE TABLE contacts (
	id int NOT NULL auto_increment,
	contact_name varchar(50) NOT NULL,
	email varchar(50) NOT NULL,
	
	constraint PK_Id primary key (id)
) TYPE=InnoDB;

INSERT INTO users VALUES('marissa','a564de63c2d0da68cf47586ee05984d7',1); /* Encoded password for marissa is "koala" */
INSERT INTO users VALUES('dianne','65d15fe9156f9c4bbffd98085992a44e',1); /* Encoded password for dianne is "emu" */
INSERT INTO users VALUES('scott','2b58af6dddbd072ed27ffc86725d7d3a',1); /* Encoded password for scott is "wombat" */
INSERT INTO users VALUES('peter','22b5c9accc6e1ba628cedc63a72d57f8',0); /* Encoded password for peter is "opal" (but user is disabled) */

INSERT INTO contacts VALUES (1, 'John Smith', 'john@somewhere.com'); /* marissa */
INSERT INTO contacts VALUES (2, 'Michael Citizen', 'michael@xyz.com'); /* marissa */
INSERT INTO contacts VALUES (3, 'Joe Bloggs', 'joe@demo.com'); /* marissa */
INSERT INTO contacts VALUES (4, 'Karen Sutherland', 'karen@sutherland.com'); /* marissa + dianne + scott */
INSERT INTO contacts VALUES (5, 'Mitchell Howard', 'mitchell@abcdef.com'); /* dianne */
INSERT INTO contacts VALUES (6, 'Rose Costas', 'rose@xyz.com'); /* dianne + scott */
INSERT INTO contacts VALUES (7, 'Amanda Smith', 'amanda@abcdef.com'); /* scott */
INSERT INTO contacts VALUES (8, 'Cindy Smith', 'cindy@smith.com'); /* dianne + scott */
INSERT INTO contacts VALUES (9, 'Jonathan Citizen', 'jonathan@xyz.com'); /* scott */
        
INSERT INTO authorities VALUES('marissa','ROLE_USER');
INSERT INTO authorities VALUES('marissa','ROLE_SUPERVISOR');
INSERT INTO authorities VALUES('dianne','ROLE_USER');
INSERT INTO authorities VALUES('scott','ROLE_USER');
INSERT INTO authorities VALUES('peter','ROLE_USER');

INSERT INTO acl_object_identity VALUES (1, 'sample.contact.Contact:1', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (2, 'sample.contact.Contact:2', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (3, 'sample.contact.Contact:3', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (4, 'sample.contact.Contact:4', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (5, 'sample.contact.Contact:5', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (6, 'sample.contact.Contact:6', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (7, 'sample.contact.Contact:7', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (8, 'sample.contact.Contact:8', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (9, 'sample.contact.Contact:9', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');

INSERT INTO acl_permission VALUES (null, 1, 'marissa', 1); /* administer */
INSERT INTO acl_permission VALUES (null, 2, 'marissa', 2); /* read */
INSERT INTO acl_permission VALUES (null, 3, 'marissa', 22); /* read+write+delete */
INSERT INTO acl_permission VALUES (null, 4, 'marissa', 1); /* administer */
INSERT INTO acl_permission VALUES (null, 4, 'dianne', 1); /* administer */
INSERT INTO acl_permission VALUES (null, 4, 'scott', 2); /* read */
INSERT INTO acl_permission VALUES (null, 5, 'dianne', 2); /* read */
INSERT INTO acl_permission VALUES (null, 6, 'dianne', 22); /* read+write+delete */
INSERT INTO acl_permission VALUES (null, 6, 'scott', 2); /* read */
INSERT INTO acl_permission VALUES (null, 7, 'scott', 1); /* administer */
INSERT INTO acl_permission VALUES (null, 8, 'dianne', 2); /* read */
INSERT INTO acl_permission VALUES (null, 8, 'scott', 2); /* read */
INSERT INTO acl_permission VALUES (null, 9, 'scott', 22); /* read+write+delete */
  #6  
Old Aug 17th, 2007, 04:48 PM
filot filot is offline
Junior Member
  
Join Date: Aug 2007
Posts: 23
Thumbs up
Hey, thanks a lot man! I was exactly looking for this!
Closed Thread

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MYSql Autocommi reddeagle Data Access 6 Jun 1st, 2007 01:31 PM
Spring, JDBC and MySQL rlynn Data Access 4 Aug 19th, 2005 08:42 AM
MySQL java.sql.SQLException: Unknown Types value jrichnow Data Access 0 Jun 26th, 2005 08:34 AM
Problem with JDBC DAO and MySQL johnny2005 Data Access 7 Jun 21st, 2005 05:17 PM
ACEGI + CAS SERVER + MYSQL ???? lazzha Spring Security 2 Jun 21st, 2005 05:09 PM


All times are GMT -5. The time now is 08:53 AM.

Contact Us - Spring Framework - Archive - Top

Contegix provides first-class managed hosting and partial sponsorship of these forums.

Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2010, Jelsoft Enterprises Ltd.