MySQL

Labels
AJAX(112) Apple(1) Application Builder(242) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) 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(11) 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) DotNetNuke(12) EASE(20) Email(6) Features(99) Firebird(1) Form Builder(14) Globalization and Localization(6) Hypermedia(2) Installation(4) 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(15) OAuth(5) OAuth Scopes(1) OAuth2(6) Offline(14) Oracle(10) PKCE(1) PostgreSQL(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(163) Reports(48) REST(26) RESTful(21) RESTful Workshop(13) RFID tags(1) SaaS(7) Security(75) SharePoint(12) SPA(5) SQL Anywhere(3) SQL Server(26) Stored Procedure(4) Teamwork(15) Tips and Tricks(81) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(331) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(39) Web.Config(9) Workflow(28)
Archive
Blog
MySQL
Friday, October 27, 2017PrintSubscribe
User Roles in SQL Business Rules

Code On Time release 8.6.8.0 introduces ability to test user roles in SQL Business Rules and fixes several remaining issues related to the recent change to the folder structure of projects in the preparation to the Native App generation.

Testing Roles in SQL Business Rules

Developers can now access parameter @BusinessRules_UserRoles in the code of SQL Business Rules and test user roles for conditional processing.

For example, create a Transact-SQL business rule in Products data controller may look as follows:

if @UnitPrice > 30 AND CHARINDEX('Administrators', @BusinessRules_UserRoles) = 0
    set @Result_Error = 'Only admin can set a high price!'

Set Command Name of the rule to Insert|Update and Phase to Before. Run the app, login with a non-administrative account, and try to update a product with a price higher than $30.

SQL business rules tests user Roles to prevent data input in an app created with Code On Time app generator.

Use the corresponding “contains” function and parameter marker in front of “BusinessRules_UserRoles” when working with the database engines other than Microsoft SQL Server.

Bug Fixes

The following fixes are also included in the project.

  • Project Builder removes resources under bin folder.
  • Web App Factory marks js, css files as content.
  • File  ~/js/_ignore.txt is now marked as embeddedResource to allow compiler to build Visual Basic projects.
  • Removed "ToUniversalTime" in value converter to fix datetime shifting issue in ASPX apps.
Tuesday, September 30, 2014PrintSubscribe
Fix for MySQL Connector/Net 6.9.3

The latest installer of MySQL Connector for .NET contains a bug that will cause an error in any Code On Time applications that attempt to use this provider. The bug report can be found here. The error message can be seen below:

MySQL connector for .NET server error.

In order to fix the error, please follow the steps outlined below:

1.    Press the Start button on the taskbar, and type in “Notepad”. Right-click on the Notepad program and press Run As Administrator.

Running the Notepad as an administrator.

2. On the toolbar, press File | Open, paste in the following path, and press Enter to open the file.

C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config

3. Press Ctrl+F keys on your keyboard, type in “MySql.Web.v20”. Remove the “.v20” part. Perform the operation on the MySqlMembershipProvider, MySqlProfileProvider, MySqlRoleProvider, and MySqlSiteMapProvider provider elements.

Finding the "MySql.Web.v20"lines to replace.

4. Save the file. On the toolbar, press File | Open. Paste in the following path and press Enter.

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config

5. Perform the same changes as described above to remove the “.v20” from “MySql.Web.v20”.

6. Save the file.

7. Restart the app generator and proceed to regenerate your web application.

Labels: ASP.NET, MySQL
Friday, November 9, 2012PrintSubscribe
Advanced Membership Provider for MySQL

Requirements

An advanced membership and role provider requires three tables.

One table keeps track of user information. This information includes the UserName, Email, and a Comment. 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.

Advanced membership provider for MySQL with integer primary keys.

SQL:

create table Users (
    UserID int not null AUTO_INCREMENT primary key,
    UserName varchar(128) not null,
    Password varchar(128) not null,
    Email varchar(256),
    `Comment` text,
    PasswordQuestion varchar(256),
    PasswordAnswer varchar(128),
    IsApproved bit not null,
    LastActivityDate datetime not null,
    LastLoginDate datetime not null,
    LastPasswordChangedDate datetime not null,
    CreationDate datetime not null,
    IsLockedOut bit not null,
    LastLockedOutDate datetime not null,
    FailedPasswordAttemptCount int not null,
    FailedPasswordAttemptWindowStart datetime not null,
    FailedPasswordAnswerAttemptCount int not null,
    FailedPasswordAnswerAttemptWindowStart datetime not null
    );
    
create table Roles (
    RoleID int not null AUTO_INCREMENT primary key,
    RoleName varchar(128) not null
    );
create table UserRoles (
    UserID int not null,
    RoleID int not null,
    primary key (UserID, RoleID),
    foreign key (UserID) references Users(UserID),
    foreign key (RoleID) references Roles(RoleID)
    );

These are the advanced membership and role provider tables with “unique identifier” primary keys.

Advanced membership provider for MySQL with unique identifier primary keys.

SQL:

create table Users (
    UserID varchar(36) not null primary key default '',
    UserName varchar(128) not null,
    Password varchar(128) not null,
    Email varchar(256),
    `Comment` text,
    PasswordQuestion varchar(256),
    PasswordAnswer varchar(128),
    IsApproved bit not null,
    LastActivityDate datetime not null,
    LastLoginDate datetime not null,
    LastPasswordChangedDate datetime not null,
    CreationDate datetime not null,
    IsLockedOut bit not null,
    LastLockedOutDate datetime not null,
    FailedPasswordAttemptCount int not null,
    FailedPasswordAttemptWindowStart datetime not null,
    FailedPasswordAnswerAttemptCount int not null,
    FailedPasswordAnswerAttemptWindowStart datetime not null
    );
    
create table Roles (
    RoleID varchar(36) not null primary key default '',
    RoleName varchar(128) not null
    );
    
create table UserRoles (
    UserID varchar(36) not null,
    RoleID varchar(36) not null,
    primary key (UserID, RoleID),
    foreign key (UserID) references Users(UserID),
    foreign key (RoleID) references Roles(RoleID)
    );
delimiter $$ create trigger userinsert before insert on Users for each row begin set New.UserID = UUID(); end $$ create trigger roleinsert before insert on Roles for each row begin set New.RoleID = UUID(); end $$

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 = UserID
column [text] UserName = UserName
column [text] Password = Password
column [text] Email = Email
column [text] Comment = Comment
column [text] PasswordQuestion = PasswordQuestion
column [text] PasswordAnswer = PasswordAnswer
column [bool] IsApproved = IsApproved
column [date] LastActivityDate = LastActivityDate
column [date] LastLoginDate = LastLoginDate
column [date] LastPasswordChangedDate = LastPasswordChangedDate
column [date] CreationDate = CreationDate
column [bool] IsLockedOut = IsLockedOut
column [date] LastLockedOutDate = LastLockedOutDate
column [int] FailedPasswordAttemptCount = FailedPasswordAttemptCount
column [date] FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart
column [int] FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount
column [date] FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart

table Roles = Roles
column [int|uiid] RoleID = RoleID
column [text] RoleName = RoleName

table UserRoles = UserRoles
column [int|uiid] UserID = UserID
column [int|uiid] RoleID = RoleID

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.