Security / Membership & Role Providers / Oracle
Basic

Requirements

A basic membership provider requires a dedicated table to keep track of user names, passwords, and emails.

A role provider will require two tables to keep track of roles and associations of users with roles.

"Users", "Roles", and "User_Roles" table schema for basic membership provider.

These are the basic membership and role provider tables with “identity” primary keys.

SQL:

create sequence users_seq;

create table users
(
    user_id 
int not null primary key,
    user_name varchar2(128) not null,
    password varchar2(128) not null,
    email varchar2(128)
)
;

create or replace trigger users_trigger
    before 
insert
 on users
   
for each row
declare

    u_id users.user_id%type;
begin
  select users_id_seq.nextval into u_id from dual;
  :new.user_id := u_id;
end users_trigger;
/

create 
sequence roles_seq;

create table roles
(
    role_id 
int not null primary key,
    role_name varchar2(50)
)
;

create or replace trigger roles_trigger
    before 
insert
  on roles
   
for each row
declare

    r_id roles.role_id%type;
begin
  select roles_seq.nextval into r_id from dual;
  :new.role_id := r_id;
end roles_trigger;
/

create table 
user_roles
(
    user_id 
int not null,
    role_id int not null,
  constraint pk_user_roles primary key(user_id, role_id),
  constraint fk_users foreign key(user_id) references users(user_id),
  constraint fk_roles foreign key(role_id) references roles(role_id)
)
;

These are the basic membership and role provider tables with “unique identifier” primary keys.

SQL:

create table users
(
    user_id 
raw(16) default sys_guid() not null primary key,
    user_name varchar2(128) not null,
    password varchar2(128) not null,
    email varchar2(128)
);

create table 
roles
(
    role_id 
raw(16) default sys_guid() not null primary key,
    role_name varchar2(50
)
)
;

create table 
user_roles
(
    user_id 
raw(16) not null,
    role_id raw(16) not null,
    constraint pk_user_roles primary key(user_id, role_id),
    constraint fk_users foreign key(user_id) references users(user_id),
    constraint fk_roles foreign key(role_id) references 
roles(role_id)
)
;

Configuration

Use one of the scripts above to create the tables in your database.

Start Code On Time web application generator, select the project name on the start page, and choose Settings. Select Authentication and Membership.

Select “Enable custom membership and role providers” option and enter the following configuration settings.

table Users = users
column [int|uiid] UserID = user_id
column [text] UserName = user_name
column [text] Password = password
column [text] Email = email

table Roles = roles
column [int|uiid] RoleID = role_id
column [text] RoleName = role_name

table UserRoles = user_roles
column [int|uiid] UserID = user_id
column [int|uiid] RoleID = role_id

The configuration will guide the code generator in mapping the logical tables Users, Roles, and UserRoles to the physical tables in the database.

Generate the project to create the custom membership and role provider.