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.

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:

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.