Code On Time: Security

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

Thursday, November 29, 2012Print Subscribe

Logging Data Access

Requirements for high-security web applications may require the logging of user credentials when data is accessed. Let’s create two fields, AccessedBy and AccessedOn, and use SQL Business Rules to automatically update these fields.

Adding AccessedBy and AccessedOn Columns

Start SQL Server Management Studio. Connect to your database. In the Object Explorer, right-click on Databases / Northwind / Tables / dbo.Customers node, and press Design.

Add two columns to the table:

Column Name	Data Type	Allow Nulls
AccessedOn	datetime	true
AccessedBy	nvarchar(50)	true

Save the table.

Switch back to Code On Time web application generator. Refresh the project.

Configuring the Fields

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on Customers / Fields / AccessedOn node.

Change the following properties:

Property	Value
Values of this field cannot be edited	true
Data Format String	g

Press OK to save the field. Double-click on Customers / Fields / AccessedBy node.

Mark the field as read-only:

Property	Value
Values of this field cannot be edited	true

Press OK to save the field. Right-click on Customers / Business Rules node, and press New Business Rule.

Give this rule the following properties:

Property	Value
Type	SQL
Command Name	Select
View	editForm1
Phase	Execute
Script	set @AccessedOn = getdate() set @AccessedBy = @BusinessRules_UserName

Seeing the Results

On the toolbar, press Browse. Navigate to the Customers page, and select a customer. The Accessed On and Accessed By fields will be appear on the form with updated values.

However, if you check the records in the database, the columns will not be updated.

This is due to the way that the client library works – when data is selected from the database, the business rule adds the values to AccessedOn and AccessedBy before they are displayed in the user interface. If the user tries to save the values, the client library will compare the data requested from the server (with appended values) and any new values. Because the user has not changed these fields, the client library assumes that the values are unchanged and does not update the record.

There are two methods of updating the field value:

Update the field value when the user selects the record.
Update the field value after the user updates the record.

Method 1: Update When User Selects

Switch back to the Project Designer. The business rule will still be open in the Browser window. Change the Script property as follows:

Property	New Value
Script	set @AccessedOn = getdate() set @AccessedBy = @BusinessRules_UserName update Customers set AccessedOn=@AccessedOn, AccessedBy=@AccessedBy where CustomerID=@CustomerID

Press OK to save the business rule. On the toolbar, press Browse.

Navigate to the Customers page, and select a record. The Accessed On and Accessed By fields will be populated in the user interface.

The database record will be updated as well.

Method 2: Update When User Updates

Make sure you are not using the business rule from Method 1. Switch back to the Project Designer. Right-click on Customers / Business Rules and press New Business Rule.

Give this business rule the following properties:

Property	Value
Type	SQL
Command Name	Update
Phase	Execute
Script	update Customers set AccessedOn=@AccessedOn, AccessedBy=@AccessedBy where CustomerID=@CustomerID

Press OK to save. On the toolbar, press Browse.

Navigate to the Customers page. Select a customer, and the fields will be populated in the user interface.

However, the database will not be updated.

Press Edit, change a field value, and press OK to save.

The column values will be updated only when the record is updated.

Labels: Business Rules/Logic, Security, Web Application Generator