一个实现RBAC的SQL脚本(二) For Derby

原文请参见： 一个实现RBAC的SQL脚本(二)  http://blog.youkuaiyun.com/enjoyo/archive/2007/07/21/1701641.aspx

这是For Derby的脚本（可以当作学习Derby数据库的参考，呵呵）

--********************************************************************

-- Membership SQL For Derby                                           

--                                                                   

-- Installs the tables and initialize the membership data            

-- supporting the Java Project and Hibernate                         

--                                                                   

-- Author：Kevin Yin                                                      

-- http://blog.youkuaiyun.com/enjoyo/                                      

-- 欢迎对此脚本提出建议，也欢迎使用本脚本构建您的系统，              

-- 但本人不对你的系统进行任何明示的或暗示的担保                      

-- 转载请保留此声名                                                   

--********************************************************************

 

--角色表

CREATE TABLE mbs_roles_t (

       nRoleID         INTEGER NOT NULL GENERATED ALWAYS AS

                           IDENTITY (START WITH 1, INCREMENT BY 1)

                           PRIMARY KEY,                   --角色ID

       szRoleName          VARCHAR( 128 ) NOT NULL ,    --角色名

       szLoRoleName              VARCHAR( 128 ) NOT NULL ,    --角色名(小写)

       bIsSysDef            INTEGER NOT NULL DEFAULT 0,    --是系统角色(1)

                                                        --还是用户添加的角色(0)

       bIsLockedOut              INTEGER not null DEFAULT 0,   --是否被锁定 0否 1锁定

       szDescription               VARCHAR( 255 ) default NULL,   --角色描述

       UNIQUE ( szLoRoleName )

);

 

--用户表

CREATE TABLE mbs_users_t (

       nUserID               INTEGER NOT NULL GENERATED ALWAYS AS

                           IDENTITY (START WITH 1, INCREMENT BY 1)

                           PRIMARY KEY,                   --用户ID

       szUserName          VARCHAR( 128 ) NOT NULL ,    --用户名

       szLoUserName             VARCHAR( 128 ) NOT NULL ,    --用户名(小写)

       szNickName         VARCHAR( 128 ) NOT NULL ,    --用户昵称

       bIsAnonymous             INTEGER not null,        --是否是匿名用户 0 不是 1 是

       szPassword          varchar(128) not null,           --Password ,plaintext, hashed,

                                                        --or encrypted; base-64-encoded

                                                        --if hashed or encrypted

       szPasswordFormat       INTEGER not null,              --密码的加密方式

                                                        --0=Plaintext, 1=Hashed, 2=Encrypted

       szPasswordSalt            varchar(128) not null,          --Randomly generated 128-bit value

                                                        --used to salt password hashes; stored in

                                                        --base-64-encoded form

       szEMail         varchar(128) not null,           --用户EMail

       szLoEMail             varchar(128) not null,           --用户EMail(小写)

       szMobilePIN         varchar(16) default null, --用户手机号

       bIsApproved         INTEGER not null,        --审核 0 未审核通过，1审核通过

       bIsLockedOut              INTEGER not null,        --是否被锁定 0 否 1锁定

       dtCreate         TIMESTAMP not null,          --帐户创建时间

       dtLastLogin           TIMESTAMP not null,          --最后一次登录的时间

       nFailedPwdCount INTEGER not null default 0,  --输入密码错误的次数

       nFailedPQACount          INTEGER not null default 0,  --密码问题错误次数

       nLoginTimes         INTEGER not null default 0,  --登录次数                  

       UNIQUE ( szLoUserName, szLoEMail )

);

 

--用户配置表

CREATE TABLE mbs_userprofile_t (

       nUserID               INTEGER NOT NULL,        --用户ID

       szProfileKey          VARCHAR( 128 ) NOT NULL ,    --用户配置项键值

       szProfileValue               VARCHAR( 255 ) NOT NULL ,    --用户配置项属性值

       PRIMARY key( nUserID,szProfileKey )      

);

 

 

--角色与权限对应表

CREATE TABLE mbs_user2role_t (

       nRoleID         INTEGER NOT NULL ,        --角色ID

       nUserID               INTEGER NOT NULL ,        --权限值

       PRIMARY KEY ( nRoleID , nUserID )

);

 

--插入系统预定义用户角色

INSERT INTO mbs_roles_t (szRoleName, szLoRoleName, bIsSysDef, bIsLockedOut,

        szDescription) VALUES

('Administrators', 'administrators', 1, 0, '管理员用户组，管理员对系统有不受限制的完全访问权'),

('Registered Users', 'registered users', 1, 0, '注册用户组，可以执行系统中属于会员的权限'),

('Guests', 'guests', 1, 1, '来宾用户组，按默认值，来宾跟用户组的成员有同等访问权，但来宾帐户的限制更多'),

('Anonymous', 'anonymous', 1, 0, '匿名用户组，比来宾用户的限制更多'),

('Denial Users', 'denial users', 1, 0, '黑名单用户组，系统将拒绝此用户组中的用户的任何操作，此用户组的优先级最高');

 

--插入超级用户

INSERT INTO mbs_users_t ( szUserName, szLoUserName, szNickName, bIsAnonymous,

 szPassword, szPasswordFormat, szPasswordSalt, szEMail, szLoEMail, szMobilePIN,

 bIsApproved, bIsLockedOut,      dtCreate, dtLastLogin, nFailedPwdCount, nFailedPQACount,

 nLoginTimes ) VALUES('Admin', 'admin', 'admin', 0, '123456', 0, '', 'admin@localhost.com',

 'admin@localhost.com', '',1,0,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,0,0,0);

 

INSERT INTO mbs_userprofile_t (nUserID, szProfileKey, szProfileValue) VALUES

(1,'real name', 'admin'),

(1,'msn', 'admin@localhost.com'),

(1,'password question', 'admin'),

(1,'password answer', 'admin'),

(1,'last password change date', CHAR(CURRENT_TIMESTAMP)),

(1,'last lock out date', CHAR(CURRENT_TIMESTAMP)),

(1,'comment', '');

 

INSERT INTO mbs_user2role_t (nRoleID, nUserID) VALUES(1,1);