Blog: Posts from December, 2012

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Posts from December, 2012
Tuesday, December 4, 2012PrintSubscribe
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.

"Users", "Roles", and "User_Roles" table diagram for Advanced Membership Provider in Oracle.

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.

Tuesday, December 4, 2012PrintSubscribe
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.

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

Saturday, December 1, 2012PrintSubscribe
Minimal Membership Provider for Oracle

Requirements

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

'Users' table diagram.

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.