Spring Security JDBC 연동을 위한 Schema

[Spring Security JDBC 연동을 위한 Schema]

 CREATE TABLE users(

      username varchar_ignorecase(50) NOT NULL PRIMARY KEY,

      PASSWORD varchar_ignorecase(50) NOT NULL,

      enabled BOOLEAN NOT NULL);

 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));

      CREATE UNIQUE INDEX ix_auth_username ON authorities (username,authority);

      

CREATE TABLE groups (

  id BIGINT generated BY DEFAULT AS identity(START WITH 0) PRIMARY KEY,

  group_name varchar_ignorecase(50) NOT NULL);

CREATE TABLE group_authorities (

  group_id BIGINT NOT NULL,

  authority VARCHAR(50) NOT NULL,

  CONSTRAINT fk_group_authorities_group FOREIGN KEY(group_id) REFERENCES groups(id));

CREATE TABLE group_members (

  id BIGINT generated BY DEFAULT AS identity(START WITH 0) PRIMARY KEY,

  username VARCHAR(50) NOT NULL,

  group_id BIGINT NOT NULL,

  CONSTRAINT fk_group_members_group FOREIGN KEY(group_id) REFERENCES groups(id));      

  

CREATE TABLE persistent_logins (

  username VARCHAR(64) NOT NULL,

  series VARCHAR(64) PRIMARY KEY,

  token VARCHAR(64) NOT NULL,

  last_used TIMESTAMP NOT NULL);  

 
※ 참고링크

http://static.springsource.org/spring-security/site/docs/3.1.x/reference/springsecurity-single.html#appendix-schema

[Mysql 용으로 변형] 

 CREATE TABLE users(

      username VARCHAR(50) NOT NULL,

      PASSWORD VARCHAR(50) NOT NULL,

      enabled BOOLEAN NOT NULL,

      PRIMARY KEY (username));

 CREATE TABLE authorities (

      username VARCHAR(50) NOT NULL,

      authority VARCHAR(50) NOT NULL

 );

CREATE UNIQUE INDEX ix_auth_username ON authorities (username,authority);

INSERT INTO users(username, PASSWORD, enabled) VALUE('henry', MD5('1234'), TRUE);   

INSERT INTO users(username, PASSWORD, enabled) VALUE('guest', MD5('1234'), TRUE); 

      

CREATE TABLE groups (

  id BIGINT(11) NOT NULL AUTO_INCREMENT,

  group_name VARCHAR(50) NOT NULL,

  PRIMARY KEY (id));

CREATE TABLE group_authorities (

  group_id BIGINT NOT NULL,

  authority VARCHAR(50) NOT NULL

);

CREATE TABLE group_members (

  id BIGINT(11) NOT NULL AUTO_INCREMENT,

  username VARCHAR(50) NOT NULL,

  group_id BIGINT NOT NULL,

  PRIMARY KEY (id));

  

CREATE TABLE persistent_logins (

  username VARCHAR(64) NOT NULL,

  series VARCHAR(64) PRIMARY KEY,

  token VARCHAR(64) NOT NULL,

  last_used TIMESTAMP NOT NULL);

- 참고로 제가 설치해서 사용하고 있는 mysql server 는 5.5 입니다.
- 5.6 부터는 base64 관련 함수가 포함이 되어 있더군요.. 흠..