Database is the most commonly used media to store authentication and authorization information for application.
This example is setting up a database for application authentication model which bases on security realms framework. The database can apply to your learning security realms and even a practical web application. It adopts following technics:
- Docker. Oracle database is running in a docker container
- Oracle XE 11g. It's sufficient dev and study
- WebLogic 12c. Java EE 7 container (later writing sample code will use it.)
1. Install Oracle XE 11g on Docker
2. Make your design satisfy security realms' thought
reference: Main thought of security realms
3. Tables relationship
4. Set up a new DB on Oracle XE
I will create a new database, called wbbank. Why it calls wbbank, because I plan on writing a sample project in the future, which is called WBBANK, an web application for a fabled bank. And the tables created today are wholly for illustrating security realms. So don't worry, it's just a name, a symbol, and will not impact eventual effect.
4.1 Create directory to store data file in docker oracle11g container
> ssh root@192.168.99.100 -p 2222
> su oracle
> mkdir -p /u01/app/oracle/oradata/XE/apps
4.2 Create exclusive tablespace and schema
sqlplus sys/oracle as sysdba
CREATE TABLESPACE WBBANK DATAFILE '/u01/app/oracle/oradata/XE/apps/wbbank.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
CREATE USER wbbank_dba IDENTIFIED BY oracle DEFAULT TABLESPACE WBBANK QUOTA UNLIMITED ON WBBANK;
GRANT ALL PRIVILEGES TO wbbank_dba;
4.3 Create tables and sequences
Now you can switch to wbbank_dba.
-- Create tables
CREATE SEQUENCE SEQ_Users;
CREATE SEQUENCE SEQ_Roles;
CREATE SEQUENCE SEQ_Groups;
CREATE TABLE Users
(
Id INTEGER NOT NULL ,
Name VARCHAR2 (50 CHAR) ,
Password VARCHAR2 (20 CHAR) ,
Description VARCHAR2 (200 CHAR)
) ;
ALTER TABLE Users ADD CONSTRAINT Users_PK PRIMARY KEY ( Id ) ;
ALTER TABLE Users ADD CONSTRAINT Users_UN_Name UNIQUE ( Name ) ;
CREATE TABLE Roles
(
Id INTEGER NOT NULL ,
Name VARCHAR2 (50 CHAR) ,
Description VARCHAR2 (200 CHAR)
) ;
ALTER TABLE Roles ADD CONSTRAINT Roles_PK PRIMARY KEY ( Id ) ;
ALTER TABLE Roles ADD CONSTRAINT Roles_UN_Name UNIQUE ( Name ) ;
CREATE TABLE Groups
(
Id INTEGER NOT NULL ,
Name VARCHAR2 (50 CHAR) ,
Description VARCHAR2 (200 CHAR)
) ;
ALTER TABLE Groups ADD CONSTRAINT Groups_PK PRIMARY KEY ( Id ) ;
ALTER TABLE Groups ADD CONSTRAINT Groups_UN_Name UNIQUE ( Name ) ;
CREATE TABLE User_Role
(
UserId INTEGER NOT NULL ,
RoleId INTEGER NOT NULL
) ;
ALTER TABLE User_Role ADD CONSTRAINT User_Role_PK PRIMARY KEY ( UserId, RoleId ) ;
ALTER TABLE User_Role ADD CONSTRAINT User_Role_Roles_FK FOREIGN KEY ( RoleId ) REFERENCES Roles ( Id ) ON DELETE CASCADE ;
ALTER TABLE User_Role ADD CONSTRAINT User_Role_Users_FK FOREIGN KEY ( UserId ) REFERENCES Users ( Id ) ON DELETE CASCADE ;
CREATE TABLE User_Group
(
UserId INTEGER ,
GroupId INTEGER
) ;
ALTER TABLE User_Group ADD CONSTRAINT User_Group_PK PRIMARY KEY ( GroupId, UserId ) ;
ALTER TABLE User_Group ADD CONSTRAINT User_Group_Groups_FK FOREIGN KEY ( GroupId ) REFERENCES Groups ( Id ) ON DELETE CASCADE ;
ALTER TABLE User_Group ADD CONSTRAINT User_Group_Users_FK FOREIGN KEY ( UserId ) REFERENCES Users ( Id ) ON DELETE CASCADE ;
CREATE TABLE Group_Role
(
GroupId INTEGER NOT NULL ,
RoleId INTEGER NOT NULL
) ;
ALTER TABLE Group_Role ADD CONSTRAINT Group_Role_PK PRIMARY KEY ( GroupId, RoleId ) ;
ALTER TABLE Group_Role ADD CONSTRAINT Group_Role_Roles_FK FOREIGN KEY ( RoleId ) REFERENCES Roles ( Id ) ON DELETE CASCADE ;
ALTER TABLE Group_Role ADD CONSTRAINT Group_Role_Groups_FK FOREIGN KEY ( GroupId ) REFERENCES Groups ( Id ) ON DELETE CASCADE ;
4.4 Add data
Please note, you might need to change some values at following script. Some ids may not be as same as mine script.
Login DB with wbbank_dba.
-- Add users
INSERT INTO Users ( ID, NAME, PASSWORD, DESCRIPTION) VALUES ( SEQ_USERS.NEXTVAL, 'bill', 'password', 'Bill.Gates');
INSERT INTO Users ( ID, NAME, PASSWORD, DESCRIPTION) VALUES ( SEQ_USERS.NEXTVAL, 'peter', 'password', 'Peter.Hamz');
INSERT INTO Users ( ID, NAME, PASSWORD, DESCRIPTION) VALUES ( SEQ_USERS.NEXTVAL, 'tom', 'password', 'Tom.Lee');
INSERT INTO Users ( ID, NAME, PASSWORD, DESCRIPTION) VALUES ( SEQ_USERS.NEXTVAL, 'neo', 'password', 'Neo.Wang');
-- Add roles
INSERT INTO Roles ( ID, NAME, DESCRIPTION) VALUES ( SEQ_ROLES.NEXTVAL, 'UserApplicant', 'Apply for WBBANK user');
INSERT INTO Roles ( ID, NAME, DESCRIPTION) VALUES ( SEQ_ROLES.NEXTVAL, 'UserManager', 'Manage WBBANK user application');
INSERT INTO Roles ( ID, NAME, DESCRIPTION) VALUES ( SEQ_ROLES.NEXTVAL, 'AccountApplicant', 'Apply for a new account, new creditcard, new saving account e.g.');
INSERT INTO Roles ( ID, NAME, DESCRIPTION) VALUES ( SEQ_ROLES.NEXTVAL, 'AccountManager', 'Manage account application');
INSERT INTO Roles ( ID, NAME, DESCRIPTION) VALUES ( SEQ_ROLES.NEXTVAL, 'TransactionApplicant', 'Start a transication, pay bill, transfer money e.g.');
INSERT INTO Roles ( ID, NAME, DESCRIPTION) VALUES ( SEQ_ROLES.NEXTVAL, 'TransactionManager', 'Manage transication, modify, rollback e.g.');
-- Add groups
INSERT INTO Groups ( ID, NAME, DESCRIPTION) VALUES ( SEQ_GROUPS.NEXTVAL, 'Users', 'User group of WBBANK.');
INSERT INTO Groups ( ID, NAME, DESCRIPTION) VALUES ( SEQ_GROUPS.NEXTVAL, 'Administrators', 'Staff group of WBBANK.');
-- user_role
-- 目前不使用这种角色映射方式。只采用User_Group方式。
-- group_role
INSERT INTO Group_Role ( GROUPID, ROLEID ) VALUES( 1, 1);
INSERT INTO Group_Role ( GROUPID, ROLEID ) VALUES( 1, 3);
INSERT INTO Group_Role ( GROUPID, ROLEID ) VALUES( 1, 5);
INSERT INTO Group_Role ( GROUPID, ROLEID ) VALUES( 2, 2);
INSERT INTO Group_Role ( GROUPID, ROLEID ) VALUES( 2, 4);
INSERT INTO Group_Role ( GROUPID, ROLEID ) VALUES( 2, 6);
-- user_group
INSERT INTO User_Group ( USERID, GROUPID) VALUES ( 1, 1);
INSERT INTO User_Group ( USERID, GROUPID) VALUES ( 2, 1);
INSERT INTO User_Group ( USERID, GROUPID) VALUES ( 3, 1);
INSERT INTO User_Group ( USERID, GROUPID) VALUES ( 4, 2);
Don'f forget to
COMMIT all your operations.