江湖十大残棋破解:这个权限模型数据库设计好吗？

seiecnu@gmail.com

自己写的，请给点意见~~谢谢

CREATE TABLE QPDB.LOGIN_GROUP (
LOGIN_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
, GROUP_NAME VARCHAR(50)
, GROUP_AUTH_DESCRIPTIOIN VARCHAR(100)
, ROLE_ID BIGINT UNSIGNED NOT NULL
, PRIMARY KEY (LOGIN_ID)
)TYPE=InnoDB;

CREATE TABLE QPDB.FUNCTION (
FUNCTION_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
, FUNCTION_NAME VARCHAR(50) NOT NULL
, DESCRIPTION VARCHAR(200)
, PRIMARY KEY (FUNCTION_ID)
)TYPE=InnoDB;

CREATE TABLE QPDB.POWER_ATTR (
POWER_ATTR_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
, POWER_NAME VARCHAR(50) NOT NULL
, POWER_DESCRIPTION VARCHAR(200)
, PRIMARY KEY (POWER_ATTR_ID)
)TYPE=InnoDB;

CREATE TABLE QPDB.OPER_LOG (
OPER_LOG_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
, USER_ID BIGINT UNSIGNED NOT NULL
, USER_NAME VARCHAR(50) NOT NULL
, OPER_TIME DATETIME NOT NULL
, MACHINE_IP VARCHAR(30)
, MACHINE_NAME VARCHAR(200)
, GROUP_ROLE_ID BIGINT UNSIGNED
, FUNCTION_ID BIGINT UNSIGNED
, ACTION_CONTENT VARCHAR(200)
, ACTION_DESCRIPTION VARCHAR(200)
, PRIMARY KEY (OPER_LOG_ID)
);

CREATE TABLE QPDB.DEPT (
DEPT_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
, DEPT_NAME VARCHAR(50)
, GROUP_ID BIGINT UNSIGNED
, PRIMARY KEY (DEPT_ID)
)TYPE=InnoDB;

CREATE TABLE QPDB.ROLE (
ROLE_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
, ROLE_NAME VARCHAR(50)
, DESCRIPTION VARCHAR(100)
, PRIORITY INT NOT NULL
, SORT_ORDER INT NOT NULL DEFAULT 1000
, PRIMARY KEY (ROLE_ID)
)TYPE=InnoDB;

CREATE TABLE QPDB.LOGIN (
LOGIN_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
, SCREEN_NAME VARCHAR(50) NOT NULL
, PASSWORD VARCHAR(100) NOT NULL
, PSW_QUESTION VARCHAR(50)
, PSW_ANSWER VARCHAR(50)
, AUTH_TYPE VARCHAR(10) NOT NULL DEFAULT 'role'
, LOGIN_TYPE VARCHAR(10) NOT NULL
, ROLE_ID BIGINT UNSIGNED
, GROUP_ID BIGINT UNSIGNED
, LOCATION VARCHAR(200)
, DISPLAY_STYLE VARCHAR(10)
, LOGIN_LOG_ID BIGINT UNSIGNED NOT NULL
, PRIMARY KEY (LOGIN_ID)
)TYPE=InnoDB;

CREATE TABLE QPDB.POWER (
ROLE_ID BIGINT UNSIGNED NOT NULL
, FUNCTION_ID BIGINT UNSIGNED NOT NULL
, POWER_ATTR_ID BIGINT UNSIGNED NOT NULL
, PRIMARY KEY (ROLE_ID, FUNCTION_ID, POWER_ATTR_ID)
)TYPE=InnoDB;