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:
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.