Stephen Smith's Blog

Musings on Machine Learning…

Archive for April 2013

User Roles and Security in Sage 300 ERP

with 5 comments

Introduction

Role based security and user roles are terms that are in vogue right now in many ERP systems. Although Sage 300 ERP doesn’t use this terminology, it is essentially giving you the same thing. This blog looks a bit at how you setup Sage 300 ERP application security and how it matches role based security.

Users

First you create your Sage 300 ERP users. This is a fairly straight forward process using the Administrative Services Users function.

user1

Here you create your users, set their language, initial password and a few other security related items.

Security Groups

Security Groups are your roles. For each application you define one of these for each role. For instance below we show a security group for the A/R Invoice Entry Clerk role. In this definition we define exactly which functions are required for this role.

secgrp

Some roles might involve functions from several applications in this case you would need a security group for each application, but they can all be assigned together for the role.

User Authorizations

User Authorizations is where you assign the various roles to your users. Below I’ve assigned myself to the A/R Clerk role.

userauth

If multiple applications are involved then you would need to add a group id for each application that makes up the role.

Thus we can create our users. We can create our roles which are security groups in Sage 300 ERP terminology and then assign them to users in User Authorizations. As you can see below signing on as STEVE now results in a much more uncluttered desktop with just the appropriate tasks for my role.

desksec

Further Security

As you can see above in the Users screen there are quite a few security options to choose from depending on your needs. One thing not to forget is that there are a number of system wide security options that are configured from the Security… button in Database Setup.

dbsec

Also remember to enable application security for the system database for you companies. For many small customers, perhaps application security isn’t an issue. I’ve also seen sites where everyone just logs in as ADMIN. But if you have several users and separation of duties is important then you should be running with security turned on.

dbsec2

Where is Security Implemented?

In the example above we see how security has affected what the user sees on their desktop. Generally from a visual point of view we hide anything a user does not have access to. This means setting up security is a great way of uncluttering people’s workspaces. However this is a visual usability issue, we don’t want people clicking on things and getting errors that they aren’t allowed. Much better to just provide a cleaner slate.

But this isn’t really security, perhaps at most it’s a thin first layer.  The real security is in the business logic layers. All access to Sage 300 functions go through the business logic layer and this is where security is enforced. This way even if you run macros, run UIs from outside the desktop, find a way to run an import to something you don’t have access to, it will all fail if you don’t have permission.

Summary

Sage 300 ERP security is a good mechanism to assign users to their appropriate roles and as a result simplify their workspace. This is important in accounting where separation of duties is an important necessity to prevent fraud.

Google Forks WebKit

with one comment

Introduction

WebKit is the underlying HTML rendering library used primarily by the Apple Safari and Google Chrome browsers. It is used in a lot of other projects like the Blackberry Browser, Opera, Tizen, Kindle and even some Microsoft e-mail clients. Even Nokia was a big WebKit user before switching to Windows Phone. Generally it’s been considered a great success, rallying the web around standards and making life easier for web developers.

webkit

WebKit is a solid open source project with lots of support. This is one reason it’s so successful. Currently in Internet browsers there are three main HTML rendering engines: the Internet Explorer Trident engine, the Mozilla Firefox Gecko engine and then WebKit.

The big news around the Internet on this front recently is that Google is forking WebKit (meaning starting a new open source project based on WebKit) and then taking it in its own direction with a project called Blink. This raises all sorts of questions: like what it means to web developers? What is Google’s real agenda? Will this damage web standards? Will this slow WebKit development? In this blog posting I want to give my perspective on a few of these questions.

History

Actually WebKit was started out of a similar controversy. Back in 2001, Apple forked the KHTML/KJS HTML rendering engine used by the browser that is part of the KDE Linux User Interface system. Basically Apple wanted something better and more tuned for its OS X project. The result was the Safari browser built on the first WebKit HTML engine. At the time no one in the Linux community was happy about this, but in the end looking back, success makes everything all right.

So now that Google is forking WebKit, claiming that it’s for the same reasons that Apple forked KHTML, will history repeat itself and a much better HTML rendering engine will emerge? Or will this just fragment the market into more slightly different HTML rendering engines making life more difficult for web developers?

WebKit’s Mobile Success

In recent years, now that Android and the iPhone have completely taken over the mobile phone market, developing web sites with HTML5 and JavaScript has become much easier. This is because WebKit is used in both of these families of devices. This means to cover 95% of the mobile market you just need to target WebKit. This greatly simplifies development and testing. Further WebKit follows web standards diligently, it keeps up with evolving standards, has great performance and great quality.

I think that WebKit has been a major contributor to the combined success of both Android and the iPhone. You can easily browse most websites from these devices. Plus when Apps incorporate browser controls they are using WebKit.

Further both Apple and Google are contributing actively to WebKit. It’s been an interesting combination of co-operation and competition. When new hardware devices come out, initially it tends only be accessible for Apps. But Google tends to very quickly add JavaScript APIs for the device to WebKit. Then Apple tends to follow suite quite quickly. Further each is driven to keep incorporating the latest version into their devices since they don’t want to let the other get ahead of them.

One of the worries of Google forking WebKit and going its own way is that we will lose the competitive nature of Apple versus Google that has been driving WebKit forwards.

Why Fork?

So why is Google forking WebKit? A lot of opinion on the Internet is that this is a strategy to sabotage Apple. I guess Google could be egomaniacal enough to think that WebKit will fail without them participating. But Apple is such a big company with so much money and talent, I think they can do just fine with WebKit, after all they did start it without Google’s help. Further I suspect the army of independent open source programmers that contribute to WebKit will continue to do so and won’t switch to Blink.

Google’s official reason is that the code in WebKit is getting too burdened with supporting code for Safari, Chrome and all the other various things it does. That if they take WebKit and remove anything that Chrome doesn’t use then they will have a smaller, faster and easier to develop code base. Basically they claim they want to move the HTML engine forwards more tightly coupled with their multi-processor architecture to improve security and performance. That doing this while supporting competing architectures within the same code base is getting harder and harder.

When Apple started WebKit and later when Google joined it, both Google and Apple were primarily worried about Microsoft and wanted to have Browser technology clearly superior to Internet Explorer. Now with their success, Microsoft is now pretty well non-existent in the mobile world. I think as a result Google isn’t feeling threatened by Microsoft anymore and is turning its attention to Apple. Generally relations between the two companies have been getting colder and colder in recent years.

Actually Google currently only uses the HTML and CSS rendering part of WebKit called WebKitCore. They stopped using the JavaScript component JavaScriptCore in favor of their own V8 JavaScript engine. The V8 JavaScript engine has been blowing away the competition in JavaScript benchmarks for some time now. In fact the V8 JavaScript engine is also the heart of Node.js the highly successful JavaScript server side processing framework. I think Google is looking to get the same sort of success out of Blink that they got from V8.

What’s the Problem?

The problem is for developers. Right now developing good web pages that run nicely anywhere means targeting IE, FireFox and WebKit which then covers the main HTML/CSS rendering engines. Unfortunately HTML and CSS are very complicated and quite subtle. Although all adhere to the published web standards, there are differences in interpretation. Also there are emergent properties that get exploited as features, things that aren’t really in the standard but have appeared in an implementation.

In the mobile world right now, developers have it easier since they can target Android, iOS, Blackberry, Tizen and Symbian by just targeting WebKit. This makes life much easier since you really can develop once and deploy pretty much anywhere. It will be a pity to lose this, and potentially quite expensive for smaller development organizations.

I imagine that many source code files will continue to be shared by WebKit and Blink. But for how long? When will we have to pay attention between differences between Blink based browsers and WebKit based browsers?

Summary

Although I find it appealing that Google is hoping to do for HTML/CSS rendering speed what it did for JavaScript execution speed with V8, I’m really worried that this is going to fragment HTML5 development for mobile devices. I tend to think this will cause more web developers to decide that if I need to develop Android and iOS separately then I may as well do both natively in Apps. To me this will be a sad further fragmentation and polarization of mobile developer communities.

Written by smist08

April 13, 2013 at 3:40 pm

Sage 300 ERP – Data Integrity

with 4 comments

Introduction

Modern ERP systems maintain a company’s full financial history for many years. People want to be confident that all that data is correct and makes sense. So how can you be confident that your database has full referential integrity? Especially after years and years of operation. The Sage 300 ERP Data Integrity function is a way to validate the integrity of a database. Modern computers are much more reliable these days than when our Data Integrity function was originally written, but it still serves a good purpose. In this article we will explore some of the protections to protect data integrity in Sage 300 along with some of the possible causes of corruption.

Database Transactioning

The number one protection of data integrity in Sage 300 is database transactioning. Data is always written to the database in a database transaction. Database transactions always take the database from one state with full data integrity to the next state with data integrity. A database transaction is guaranteed by the database server to either be all written to the physical database or none of it is written, the database server guarantees that you will never see part of a transaction.

For instance as we post a G/L batch, we post each entry as a database transaction and since each entry in a G/L batch must be balanced we guarantee via database transactioning that the G/L is always in balance and hence data integrity is maintained.

Where Do Integrity Errors Come From?

Database transactioning sounds great, and in fact with database transactioning we see very few database problems in Sage 300 ERP. But when we do get integrity problems where do data integrity errors come from?

Below is a list of some of the main causes of data integrity problems. I’m sure there are more. I’m not looking to blame anyone (including myself), just to point out the main causes I’ve seen:

  • Bugs in the program. If Sage 300 asks SQL Server to store incorrect data, it will do so in a completely reliable transactional manner. Hopefully our QA processes catch most of these and this doesn’t happen often; but, Sage 300 is a large complicated program and mistakes happen.
  • People editing database tables directly in SQL Server Enterprise Manager. For various reasons people might try to put something in the database that the program doesn’t allow, and often this leads to database corruption.
  • Third party programs that write to the Sage 300 database directly. We do a lot of data validation checking in our business logic before allowing data to be written to the database, but if this is bypassed then corruption occurs. A common one in this case is not handling currency decimal places correctly.
  • Data validation needs to be tightened. Now and again, someone has written data that we accepted as valid that wasn’t. Then we had to tighten our data validation routines. The good news here is that we’ve been doing this for a long time now.
  • Bug in the Database Server. We’ve seen database indexes get corrupted which can lead to further problems either after the indexes are fixed (because of other data written as a result).
  • Partial backups or restores. We’ve seen people back up the tables for each application independently and then restore them. Perhaps to try to put A/R back to yesterday. But this corrupts the database since there is often matching data that needs to be in sync in Bank, Taxes or perhaps Order Entry. Make sure you always backup and restore the database as a whole.
  • Hardware glitches. Even with CRC checking and such, strange errors can start to appear from hard disk or memory hardware failures in computers.

The Data Integrity Checker

To find these sort of problems Sage 300 ERP has a data integrity checker in its Administrative Services. The main screen looks like:

dataint1

You select the applications you want to check and whether you want to fix any minor errors. Since this can be a long process, for several applications you can also configure which parts within the application to check by selecting the application and choosing the application options in a screen like:

dataint2

The end result is a report is run that lists all the errors found.

What Does the Integrity Checker Look For?

So what does the Integrity Checker do? Below is a list of some of the checks that are typically made:

  • Check the integrity of each file reading each record and calling the View Verify API which will call the business logic to validate the record. This includes things like checking the decimals of a money amount are correct, that the data is correct for the data type, that foreign keys are valid.
  • For Header/Detail type relationships there are often total or summary fields in the header like the total amount of an order or the number of detail lines. The integrity checker will read through the details and add up any of these numbers to ensure they match the header.
  • Check the database for any detail records that don’t have a matching header record (orphans).
  • Each application then knows about all sorts of cross file relationships that must be maintained and the Integrity Checker for that application will validate all of these relationships.

What Does Fix Minor Errors Do?

There is the check box to fix minor errors, but what does it do? Mostly it fixes up header/detail relationships by fixing any total or summary fields in header records. It can also delete orphan detail records. But generally it doesn’t attempt much because we don’t want to risk making things worse.

But it’s Slow

The big complaint about the Data Integrity checker is that it’s slow. This is because it does go through every record in the database as well as checking all the cross dependencies. These days we see company databases that are hundreds of gigabytes in size. Generally the complaint is that you can’t just run it as routine maintenance overnight. That you tend to have to configure what you want to run and do that selectively. It’s also best to run it when people aren’t in the system since it does put a fair bit of load on the system.

But this does open up an opportunity for third party developers. Companies like Tairox and Orchid offer solutions to automate data integrity or to run it as a service.

Summary

Even with super reliable modern databases and hardware, data integrity errors can still creep in and need to be dealt with. Just being aware they exist is half the battle. Also remember that it is extremely important to have regular full backups of your data in case of a really catastrophic failure.