spring security例程搭建(Mysql)
在Spring security2.0.4发布的包中,提供了,相应的例程,可是由于它缺省使用的并非Mysql数据库的问题,导致我们无法直接搭建起例程。如果要搭建起例程就需要修改SQL语句,使其适用于Mysql。现对,修改内容说明如下:
一:SQL语句的修改,修改DataSourcePopulator.java文件。
template.execute( "CREATE TABLE ACL_SID(" + "ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 100) NOT NULL PRIMARY KEY," + "PRINCIPAL BOOLEAN NOT NULL," + "SID VARCHAR_IGNORECASE(100) NOT NULL," + "CONSTRAINT UNIQUE_UK_1 UNIQUE(SID,PRINCIPAL));"); template.execute( "CREATE TABLE ACL_CLASS(" + "ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 100) NOT NULL PRIMARY KEY," + "CLASS VARCHAR_IGNORECASE(100) NOT NULL," + "CONSTRAINT UNIQUE_UK_2 UNIQUE(CLASS));"); template.execute( "CREATE TABLE ACL_OBJECT_IDENTITY(" + "ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 100) NOT NULL PRIMARY KEY," + "OBJECT_ID_CLASS BIGINT NOT NULL," + "OBJECT_ID_IDENTITY BIGINT NOT NULL," + "PARENT_OBJECT BIGINT," + "OWNER_SID BIGINT," + "ENTRIES_INHERITING BOOLEAN NOT NULL," + "CONSTRAINT UNIQUE_UK_3 UNIQUE(OBJECT_ID_CLASS,OBJECT_ID_IDENTITY)," + "CONSTRAINT FOREIGN_FK_1 FOREIGN KEY(PARENT_OBJECT)REFERENCES ACL_OBJECT_IDENTITY(ID)," + "CONSTRAINT FOREIGN_FK_2 FOREIGN KEY(OBJECT_ID_CLASS)REFERENCES ACL_CLASS(ID)," + "CONSTRAINT FOREIGN_FK_3 FOREIGN KEY(OWNER_SID)REFERENCES ACL_SID(ID));"); template.execute( "CREATE TABLE ACL_ENTRY(" + "ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 100) NOT NULL PRIMARY KEY," + "ACL_OBJECT_IDENTITY BIGINT NOT NULL,ACE_ORDER INT NOT NULL,SID BIGINT NOT NULL," + "MASK INTEGER NOT NULL,GRANTING BOOLEAN NOT NULL,AUDIT_SUCCESS BOOLEAN NOT NULL," + "AUDIT_FAILURE BOOLEAN NOT NULL,CONSTRAINT UNIQUE_UK_4 UNIQUE(ACL_OBJECT_IDENTITY,ACE_ORDER)," + "CONSTRAINT FOREIGN_FK_4 FOREIGN KEY(ACL_OBJECT_IDENTITY) REFERENCES ACL_OBJECT_IDENTITY(ID)," + "CONSTRAINT FOREIGN_FK_5 FOREIGN KEY(SID) REFERENCES ACL_SID(ID));"); template.execute( "CREATE TABLE USERS(USERNAME VARCHAR_IGNORECASE(50) NOT NULL PRIMARY KEY,PASSWORD VARCHAR_IGNORECASE(50) NOT NULL,ENABLED BOOLEAN NOT NULL);"); template.execute( "CREATE TABLE AUTHORITIES(USERNAME VARCHAR_IGNORECASE(50) NOT NULL,AUTHORITY VARCHAR_IGNORECASE(50) NOT NULL,CONSTRAINT FK_AUTHORITIES_USERS FOREIGN KEY(USERNAME) REFERENCES USERS(USERNAME));"); template.execute("CREATE UNIQUE INDEX IX_AUTH_USERNAME ON AUTHORITIES(USERNAME,AUTHORITY);"); template.execute( "CREATE TABLE CONTACTS(ID BIGINT NOT NULL PRIMARY KEY, CONTACT_NAME VARCHAR_IGNORECASE(50) NOT NULL, EMAIL VARCHAR_IGNORECASE(50) NOT NULL)");
上面代码需要修改如下内容:
引用
GENERATED BY DEFAULT AS IDENTITY(START WITH 100) NOT NULL PRIMARY KEY
改成:
引用
BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
引用
VARCHAR_IGNORECASE
改成:
引用
VARCHAR
运行程序后仍然报错,是因为在spring-security-acl-2.0.4.jar包中有以下代码:
private String classIdentityQuery = "call identity()"; // should be overridden for postgres : sel