An rbac SQL script (2) for Derby

For more information, see a rbac SQL script (2) http://blog.csdn.net/enjoyo/archive/2007/07/21/1701641.aspx.

This is the script for Derby (it can be used as a reference for learning the Derby database)

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

-- Membership SQL Derby

--

-- INSTALLThe tables and initialize the membership data

-- Supporting the Java project and Hibernate

--

-- Author: Kevin Yin

Http://blog.csdn.net/enjoyo/

-- Welcome to the suggestions for this script, and welcome to use this script to build your system,

-- However, I do not provide any express or implied warranty for your system.

-- Reprinted please keep this reputation

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

 

-- Role table

Create Table mbs_roles_t (

Nroleid integer not null generated always

Identity (start with 1, increment by 1)

Primary Key, -- Role ID

Szrlename varchar (128) not null, -- role name

Szlorolename varchar (128) not null, -- role name (lower case)

Bissysdef integer not null default 0, -- is a system role (1)

-- Or the role added by the user (0)

Bislockedout integer not null default 0, -- whether it is locked 0 No 1 locked

Szdescription varchar (255) default null, -- Role description

Unique (szlorolename)

);

 

-- User table

Create Table mbs_users_t (

Nuserid integer not null generated always

Identity (start with 1, increment by 1)

Primary Key, -- User ID

Szusername varchar (128) not null, -- User Name

Szlousername varchar (128) not null, -- username (lower case)

Sznickname varchar (128) not null, -- user nickname

Bisanonymous integer not null, -- whether anonymous user 0 is not 1 is

Szpassword varchar (128) not null, -- password, plaintext, hashed,

-- Or encrypted; base-64-encoded

-- If hashed or encrypted

Szpasswordformat integer not null, -- password encryption method

-- 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, -- User email

Szloemail varchar (128) not null, -- User email (lower case)

Szmobilepin varchar (16) default null, -- user mobile phone number

Bisapproved integer not null, -- Audit 0 not approved, 1 approved

Bislockedout integer not null, -- whether to be locked 0 No 1 locked

Dtcreate timestamp not null, -- Account creation time

Dtlastlogin timestamp not null, -- Last Logon Time

Nfailedpwdcount integer not null default 0, -- number of times the password is incorrectly entered

Nfailedpqacount integer not null default 0, -- number of password errors

Nlogintimes integer not null default 0, -- number of logins

Unique (szlousername, szloemail)

);

 

-- User configuration table

Create Table mbs_userprofile_t (

Nuserid integer not null, -- User ID

Szprofilekey varchar (128) not null, -- User Configuration key value

Szprofilevalue varchar (255) not null, -- user configuration item Attribute Value

Primary Key (nuserid, szprofilekey)

);

 

 

-- Role and permission table

Create Table mbs_user2role_t (

Nroleid integer not null, -- Role ID

Nuserid integer not null, -- permission Value

Primary Key (nroleid, nuserid)

);

 

-- Insert the system pre-defined user role

Insert into mbs_roles_t (srolename, szlorolename, bissysdef, bislockedout,

Szdescription) Values

('Administrators ', 'administrators', 1, 0, 'administrator user group, the Administrator has unrestricted access to the system '),

('Registered users', 'registered users', 1, 0, 'Register a user group, you can execute permissions for members in the system '),

('Guests', 'guests', 1, 1, 'Guest user group, by default, the guest and user group members have the same access permissions, but the Guest account has more restrictions '),

('Anonus us', 'anonymous', 1, 0, 'Anonymous user group, more restrictive than guest users '),

('Denial users', 'denial users', 1, 0, 'blacklist user group, the system will reject any operation of the user in this user group, the highest priority of this user group ');

 

-- Insert a superuser

Insert into mbs_users_t (szusername, szlousername, sznickname, bisanonymous,

Szpassword, szpasswordformat, szpasswordsalt, szemail, szloemail, szmobilepin,

Bisapproved, bislockedout, dtcreate, dtlastlogin, nfailedpwdcount, nfailedpqacount,

Nlogintimes) values ('admin', 0, '000000', 0, '', 'admin @ localhost.com ',

'Admin @ localhost.com ', '', current_timestamp, current_timestamp, 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 ancer', '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 );