Code On Time: Oracle

Tuesday, December 4, 2012Print Subscribe

Advanced Membership Provider for Oracle Server

Requirements

An advanced membership and role provider requires three tables.

One table keeps track of user information. This information includes the user_name, email, and comments. Additional columns allow for implementation of a password question and answer in order to recover a forgotten password. When users are created, they can not be approved by default. Additional information is captured about the most recent login, activity, and change of password. When a user inputs an incorrect password past the limit, the user will become locked out. The number of failed attempts and most recent failed attempt will be stored.

Two tables are required to keep track of roles and associations of users with roles.

These are the advanced 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),
    comments nclob,
    password_question varchar2(256),
    password_answer varchar2(128),
    is_approved number(1,0) check (is_approved in (1,0)) not null,
    last_activity_date date not null,
    last_login_date date not null,
    last_password_changed_date date not null,
    creation_date date not null,
    is_locked_out number(1,0) check(is_locked_out in (1,0)) not null,
    last_locked_out_date date not null,
    pwd_attempt_count int not null,
    pwd_attempt_window_start date not null,
    pwd_ans_attempt_count int not null,
    pwd_ans_attempt_window_start date not null
);

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 advanced 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),
    comments nclob,
    password_question varchar2(256),
    password_answer varchar2(128),
    is_approved number(1,0) default 1 check (is_approved in (1,0)) not null,
    last_activity_date timestamp not null,
    last_login_date timestamp not null,
    last_password_changed_date timestamp not null,
    creation_date timestamp not null,
    is_locked_out number(1,0) default 1 check(is_locked_out in (1,0)) not null,
    last_locked_out_date timestamp not null,
    pwd_attempt_count int not null,
    pwd_attempt_window_start timestamp not null,
    pwd_ans_attempt_count int not null,
    pwd_ans_attempt_window_start timestamp not null
);

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 membership and role provider 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
column [text] Comment = comments
column [text] PasswordQuestion = password_question
column [text] PasswordAnswer = password_answer
column [bool] IsApproved = is_approved
column [date] LastActivityDate = last_activity_date
column [date] LastLoginDate = last_login_date
column [date] LastPasswordChangedDate = last_password_changed_date
column [date] CreationDate = creation_date
column [bool] IsLockedOut = is_locked_out
column [date] LastLockedOutDate = last_locked_out_date
column [int] FailedPasswordAttemptCount = pwd_attempt_count
column [date] FailedPasswordAttemptWindowStart = pwd_attempt_window_start
column [int] FailedPasswordAnswerAttemptCount = pwd_ans_attempt_count
column [date] FailedPasswordAnswerAttemptWindowStart = pwd_ans_attempt_window_start

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.

Labels: Oracle, Security, Tutorials, Web Application Generator

Tuesday, December 4, 2012Print Subscribe

Basic Membership Provider for Oracle Server

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.

Labels: Oracle, Security, Tutorials, Web Application Generator

Saturday, December 1, 2012Print Subscribe

Minimal Membership Provider for Oracle

Requirements

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

This is a sample “Users” table with “identity” primary key. It is necessary to create a sequence and trigger to update the identity key every time a new user is created.

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
);

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;
/

Here is how the table may look if a “unique identifier” primary key is implemented. Oracle will automatically assign a unique identifier when a new user is created.

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
);

User roles are hardcoded in the minimal Role Provider implementation.

Configuration

Create a table in your database using one of the scripts specified above.

Select the project name on the start page of the application generator and choose Settings.

Proceed to 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

role Administrators = admin
role Users = admin, user
role Everybody = *

The configuration maps logical table Users required for membership provider implementation to the physical database table Users. It also defines three user roles – Administrators, Users, and Everybody.

Generate the project to see the membership provider in action.

Labels: Application Factory, Features, Oracle, Security, Tutorials, Web Application Generator