So you want to do a Startup – the “E” projects

“E” stands for educational. The main purpose is to improve your education or knowledge, making money or moving up to a better job is a possible side-effect.

 

The keys for an “E” project is

• Early Adapter of a version 1.0 language, product etc.
• Ideally within 6 months of release
• “Academic Research Languages” and “Niche Languages” are an exception.
• Be prepare for 6 “wasted time” for every success
• Actually, the time is not wasted – you have increased your width of knowledge and patterns.
• Be product/offering directed.

A few examples from my own past.

• APL/360 (in 1968) – main benefit was seeing what was seeing what was possible beyond currently available languages like Assembly, Fortran, COBOL, EasyCoder.
• C did not come into existance until 1973
• B (that which C was based on) did not come into existence until 1970
• Most developers have zero experience with this, which is still a much higher language than C# or Java
• Purchased Borland TurboC 1.0 just after it was released.
• Ended up writing a collection of MSDOS Utilities in it and sold copies under ShareWare
• Talked the boss into getting Visual Basic 1.0 for a work project.
• 3 months after release,
• Lead to me getting on with Microsoft (after all, few people were programming in VB longer than I had been!!!)
• Purchased the original VBX Kit (VB Control SDK) and learn to writer UI Controls from scratch. (The docs were mimeographed pages in a binder)
• That skill has given me a leg-up through my career.
• Next time you open SSMS, the tree on the left was prototyped with a control that I wrote for another project and MSSQL group “borrowed”.
• Did a set of prototype dialogs for a startup in Android seeking Angel Funding.
• Getting regular pings on LinkedIn because it listed on my profile.

Other “E” projects from my past

• Turbo Prolog: A current project may need to use prolog as part of the solution, I already know the “zen” of this language… :-)
• FOCUS, Oracle 5.1, RBASE, DB-III, Clipper – all of that experience was recycled into my RDBMS (SQLServer) skill sets
• Simula67 – this is the father of C++ and object orientated language.

On my radar are the following languages:

• Alloy – see http://alloy.mit.edu/community/, it deals with program verifications. The ability to prove programs are correct is a raising trend.  If security is involved in your work place, you may wish to get familiar with it

One of my current recommendation for ‘solo garage startups’ is to create control-libraries for the hot mobile platforms:

• Android
• Windows Mobile 7

In addition to these libraries (which are marketable by themselves), look at the market places and find out the most popular downloads.  Create a monkey-see, monkey-do version of these applications that uses your libraries. Often this process will identify the controls that should go into the libraries.

 

Once you have the library done – you should create a page showing now simple it is to create these applications using your controls. You will likely see more copies of the library sold to  want-to-be developers, then your clone application (unless you do it very very well).

 

Bottom line is that you are educating yourself. The key is to go with the new-items (before the masses) so you are a big fish in a small tank, instead of a herring chasing the school.

 

Some of these items will be still born (Borland OWL, Apple’s LISA, etc) – that is to be expected.  You can still benefit from the approaches that they attempted.

Startups: The napkin business plan

Today I had two different conversations with folks interested in doing startups. Both were in that pre-business plan phrase. A business plan is not just something that you write for Angel Funding, it is something to precipitate vague ideas into something concrete.

 

Often in the early days, people think in stream-of-consciousness.  The logic and thinking is fuzzy. The purpose of a napkin business plan is to get a framework, a skeleton to add muscle, flesh, circulation system and fat too. Without a framework, you cannot walk,  you can only slither. Changing the stream into snow (but not ice – you want to keep it pliable).

 

To me, the first question is simple – what class of business are you thinking of?

 

Businesses can be divided to the following general classes:

• Producer: You build or create a sellable commodity. Your are the factory, examples:
• Selling Word Templates
• Selling Control Libraries
• Selling Accounting Systems
• Selling content (Pictures, articles) that you own.
• You are a middle man: You acquire something and pass it along. This may be for a mockup (i.e. Amazon.com) or for advertising revenue, or both. Example:
• Social networking (advertising revenue)
• Online stores (pass thru sales)
• Content consolidation (advertising revenus)
• You are a service agency: You perform work for someone or provide a service. Examples:
• Designing web sites
• Consulting Services

The second question is often not considered at all, or only in a child-like manner.  If you are successful, and end up with a 1000 employee company --- what do you want your role to be?

• None – Sell it and have a villa on the ocean in Goa, India
• Board of Director – your hand is still in the firm, but you are not in day to day management. You may go on to other things…
• CEO – You are running the firm… often software types that state this are out to lunch…  do you really want to handle all of the day to day business details, HR issues, etc? If you were good in management, you would have done a commerce degree!!! Don’t be a case study for the Peter Principle!
• CTO or Architect – in other words, you hope to keep having fun coding and developing.
• OR….

Knowing where you want to end up means that you can staff up early with folks that compliment YOUR end game.

 

The third question is easier to answer: What is the benefit that your customer will receive? The intent is to move your thinking from ME to the customer, “People will buy  foobars and make me rich!” is transformed to “People will buy foobars for their friends to show them they care about the environment and have more money than their friends”.

You need to think about the benefits to your customers. This leads to the sub-question, “Why will their buy foobars from me and not Amazon.com (or other competitor)?”

 

The fourth question is what are my options for opening my business? Use a food outlet as an analogy:

• Can I start with a “push cart” (are you too proud to push it?)
• Do I need to rent 5000 sq ft of waterfront space and redecorate it in Inuit Modern?

The sub-question is what is needed to attractive the desired customers? Buying a new push cart may not be need, a second hand push cart decorated appropriately may do better. What about customer expectations ---

• if you are sloppy and the cart reflects it after 1 hr – you will not have repeat business.
• A clean cart with latex gloves being used for food handling and signs indicating non-GMO organic gluten free products only will not only get repeat business but may result in referrals.

The key is to understand your customer motivations --- which is why in an earlier post I cited the importance of a “domain expert”, someone that can state and advocate what your (theoretical) customer wants.

• How do you get word out (“advertise” is not an acceptable answer – details please!)?
• What do you do for customer service? (I know that as a developer you don’t want to deal with customers – but without them, you have no sales!)
• A pure advertising revenue site still have to deal with customers because they may refer to your site on social media – and you cannot afford to ignore that.

Last words..

You are thinking of creating a start-up. Startups are typically corporations (LLC, S Corp etc) which have an independent legal existence from you. They are very much like having a kid or significant other – they are not extensions of yourself, but separate. You need to get enough separation between the startup and yourself for both of you to grow and prosper.

Startup: Garage or Angel Funded or Solo

There are three main models for startup as the title suggests. In the old days before Open-Source, there was Share-Ware. Often Share-ware allowed a single person to produce a viable startup with no external funding etc.  Support was via email, there was no need for a website, and social media did not exist.

 

Today, a solo startup could technically make it – but it has the following risks:

• You get swamped trying to do everything – in fact, you may suffer burn out.
• If anyone sets their business crosshairs on your business, they can jump ahead of you because they have more resources
• Moving from solo to team runs a very high risk. There is learning curve to working as a team. If you are running solo, you will likely defer this change until it is forced upon you…
• Odds are that this will be in the middle of taking off… and the extra load on the single engine will cause the engine to sputter and your firm may crash instead of cash.
• If you do not have significant management experience, you will mismanage people because you are likely under stress….
• If the business model is such that you will never need more than one person – you are good…  if not, things may pressurize and explode when you try moving to more staff.

At one time, garage start-ups were the expected startup mode. People invested their time in working after hours to create a project. They may actually be out-of-pocket for some costs, ideally never putting their personal budget into the hole (they may have to give up a few concerts, a new car etc to make money available – that is fine). The real advantage of the garage is that there is staying power --- if you are early to market, you just wait until the market grows…  There are no investors asking you to delay refactoring, get a new release out, etc. You can do it right.

 

Today, I have seen several startups that want to go from

• Idea –> Angel Funding –> Sell out.

This is the dotCom thinking. Another variation is

• Idea –> Patent Filling –> Angel Funding –> Sell out. In this case, they are trying to “game” the Angels because many Angels are looking for IP. Remember, a patent filled does not mean a patent granted.

The reason that you should go to Angels is when you need cash amounts that you cannot provide yourself. If the reason is to pay for LABOR, Angels should say “NO WAY!”. You should be investing your time (and your friends’ time as equity shares) in getting the startup going.

 

In today’s environment, you do not need money for most software, Microsoft Web Spark provides you with all that you need. For Hardware, you can bootstrap with a hosted site for $10/month  from folks like WebHost4Life (Asp.Net, SQL Databases etc).

 

If your site grows enough that you need something beyond this, then if you ping around, you will likely find a hosting service that is willing to provide one or more idle computers to you for an equity stake, or a firm with an awesome bandwidth that will allow you to drop a machine or two into their machine room for an equity stake (1%?).  They are giving you unused capacity in exchange for equity – a win-win scenario. The same applies to source control (I know of a local Team Foundation Server shared by several light load companies).

 

So when should you try for Angel Funding? IMHO, it should occur when:

• you need to spend cash that you cannot get out of your own budget
• without the cash, an opportunity will be lost
• Angel funding should  be biased for CAPITALIZATION and not Expenses

Examples: More hardware (for example, if you are offering backup services and cannot add customers without capitalization), advertising (to expand market quickly), etc.

 

You should never approach Angel Funding to pay for writing code. As a software developer, you are expected to have pre-invested your own hours into that activity.

 

So think garage startup. If you are not going solo, then try getting an experience development manager involved to manage the people and the project. They could do some of the coding but their first priority is management. You may have the excellent idea, but you are too close to it. Financial success depends on making compromises and making rational business decisions.

So you want to do a Startup – the “A” projects

A is for “Academics”.  If you are a boot-strapped developer that learnt coding on the job, this is very likely not for you.

 

Academic literature is full of research that can be rolled into products or components. If you are a Computer Science Grad (or post grad) then a membership to IEEE and/or ACM is a must – solely to get access to their electronic libraries of journal and conference proceedings.

 

What you will discover are mountains of research which have not been developed into products or components. For example, one of my current project is to research and derive advance Role Base Access component that supports Policy, Distributed,  Temporal and Special.  I’m in the midst of reviewing the literature (for a bibliography of what I am reading is here).  This type of system is needed by most large organizations – for example: Hospitals (HIIPA), Banks and other financial organizations, large corporations. If you google, you will find that there are offerings in this area, for example Omada’s offerings.

 

You may ask, surely, their own IT group will write it themselves! Wrong, or should I say should be wrong – they may attempt to do it, but the project will likely end up being a kludge and will fail.

 

In my RBAC work above, you will see that most of the works is in the last 5 years and that there is a large volume….  in-house developers (even those with B.Sc. in Computer Science) will have trouble reading the literature.

 

If your academic training is in psychology, or HR – you will find similar bare fields waiting to be ploughed. Academics research, startup produces!

 

There is one important gotcha --- beware of stuff that is “owned” by someone.

For example, SCRUM is open but there are dominant groups, like the ScrumAlliance,  you need to join and play nicely with such groups.

 

The business model is more a garage-startup and not an angel-funded startup…  Personally, I think a lot of people are looking for Angel Funding that should be doing garage startups. It reminds me a bit of folks trying to cash in during the dotCom boom. Are you going to Angels to pay you to do a garage startup OR because you need the funding for items OTHER than labor. If the money is needed for labor ---  get some friends together and boot-strap yourself. If it is needed for equipment, paying for advertising, etc then you should see an Angel Group – but your garage startup should have delivered at least a CTP product…

So you want to do a Startup – the “D” Projects

“D” is for Developer. This means that the product is for developers! You are a domain expert! Examples of successful firms in this space are Telerik, DevExpress, etc. This is the type of project that you are unlikely to get angel funding for initially but it’s a viable business model with the exit strategy being selling off to one of the above.

 

The typical development system provides basic Lego blocks only. You provide custom blocks that deal with common specific needs (for example, doors and windows!). Below, I will first talk about Controls, the visual widgets.

 

Control Projects

The key for success is success is a new or highly popular development system. Today, that would boil down to:

• Android (Java core)
• Windows Phone 7 (CLR core)

You can actually jump start your development by looking for Open Source controls and examples of how to modify existing controls. For example, I found a nice color picker example for the Android.

On your first pass, you simply reap prior work and build out a set of controls that leverage this code. On the second pass, you look at existing control sets on related platforms (Telerik, DevExpress) and clone the control in this target development platform.

 

At this point, you have a viable product offering. You need to make sure you have good documentation (not just adequate) and examples. Make sure that your product is easy to learn because you want to offer 30 day trial versions and want the learning experience to be a success.

 

You should enlist a few fellow developer types to handle support and review (code, documentation, example) because if you are successful – you won’t have time to sleep! For purchasing developers, you have saved time and debugging. Set a good price point. Consider offering multiple type of licenses initially:

• Yearly
• Perpetual Upgrades (3-5x Yearly license)
• Individual
• Company

The purpose of these licensing options  is to get a critical mass of users. You withdraw the less profitable options as customer numbers build.

 

Another hot spot is Controls for SharePoint.

 

Utility Libraries

The simplest example is a library that has all of the functions available in Excel. There are two obvious targets:

• Creating SQLCLR function library that you can use in TSQL
• Creating CLR function library that you can use with LINQ

If you look at departmental computing, you will see a lot of Excel spreadsheets. When those projects are taken over by IT or consultants, you find that the absence of such libraries are a frustration – frustrations are a market opportunity.

 

There are other sets of utilities that are marketable – consider MS Project features for example.

Bottomline

“D” projects are a good “do it evenings and weekends” that do not require Angel Funding until you need to leave the garage.

So you want to do a startup – the “C” projects

C stands for Co-operative as in the co-operative game theory. Ideally this means striking a partnership with an existing business or software product firm. Your goal is to provide an add-on product offering for their product. The motivations of people are shown below:

 

You, the startup

• You have an existing market to target
• If your offering can be on THEIR site, you will get much higher sales at a lower cost
• Greatly reduced marketing cost
• Revenue sharing may be part of the agreement – view it as a “sales representative” commission.
• If you can get an exclusive agreement for 2+ years on your offering then you have that sweet spot called a protected market!
• Scope of project is usually reduced (you will be interacting, not building a complete system)

Them, the established business

• Allow richer offerings to customers without cash outlay or risk.
• Allow your core competency to stay focus (especially is a small development staff)
• Possible additional revenue with little if any risk

The Mechanism

The startup negotiate with the established firm an exclusive agreement for them to produce and offer for sale a complementary product to the firm’s business.  The firm gets a fee per unit sold. The firm provide some internal technical support (i.e. internal API access). The firm does not have to develop some new expertise (or find funding for it) but it’s competitive situation should be improved.

 

The firm may actually be willing to be an “Angel Investor” in the firm. For example, a startup that develops an android application in lieu of a Windows application may cost the firm $300K in weighted development costs. Investing $100K in a startup for 33% interest may be a better business investment – if it is successful, you save $200K and if the startup expands offerings, you may reap unexpected paybacks. If it is not, you lost is only $100K instead of $300K.

 

Given the exclusive agreement (“protected market”), finding additional angel investors will be much easier.

 

Some Local Examples

I will walk through some local Whatcom firms and describe some possible start-up opportunities (if you do them, remember my 1% equity in the startup for suggesting it! ;-) )

 

Logos Bible Software [Site]

Create Mobile Applications that provides the same (or subset) of features as on Windows/Mac products. The offerings would be:

• iProducts (iPhone, iPad etc)
• Android
• Linux also?
• Windows Phone 7
• Blackberry

These could be standalone and/or web service based (i.e. data is cached off web site).  Their advantage: more offerings without needing to find and hire developers.

 

Dealer Information Systems Corporation [Site] aka DIS

Very similar to the above, except you focus on the use of mobile devices for scanning bar code labels  etc and integrating the data in real time with their management systems. Additional features are mini-applications on mobile devices, like inventory lookup, dynamic rerouting of flatbeds for picking up equipment, documenting “as delivered” and “as returned” state of rental equipment.

 

Qualnetics [Site]

Checking their product offerings we see some mobile offerings. A startup could offer conversions or ports to Android and other platforms. Possible product offerings on other mobile platforms that does telematics.  This is an interesting case because they do some mobile offering – but they do not appear to cover the spectrum. Are they willing to spend money getting the entire spectrum of mobile developers, or would they prefer to cooperate with someone in a win-win scenario.

Haggens [Site]

They do not appear to have a mobile friendly site available ( no http://mobile.haggen.com/ or http://m.haggen.com/ ) which presents some opportunities. Having a mobile site that lists competitive pricing specials (possibly with mobile device only coupons!). It could start with something as simple as a subset of their current pricings (assuming the login required a Haggen’s card) so someone shopping at Freddies or Safeway can do price checks before buying there. The price check would hopefully cause them to include Haggen’s in their shopping. With mobile-web only coupons (keyed to their Haggen’s card number only), they can do mini-hyper-focus-marketing and you get a few cents per coupon redeemed.

 

If GPS location is captured by using a mobile application, then Haggen’s would know the store they are in when they hit the site and intentionally “loss leader” a few items on that specific Haggen’s card to get the customer into a Haggen’s store instead. For example, if it is Safeway, lookup Safeway’s specials from their fliers and cut the price by 1 cent further for this customer. At checkout, the card number would trigger the extra mobile discount.  The discounts will be time limited (i.e. good for 4 hrs only).

 

Bottom Line

Almost every existing firm have items that they could (and perhaps should) do, but with the lack of $$$, they will defer the doing. For a start-up this is an opportunity. The firm gives up something that they are unlikely to do for at least two years because of budgets (so nothing lost), and you have the opportunity to make it work NOW with the thing called a protected market – i.e. exclusivity. A win-win situation for everyone. It takes a bit of co-operation (and an absence (or at least forcible restraining) of control freaks) and trust. – but this is Whatcom county… buy local, create jobs locally, support local startups!

So you want to do a startup–the “B” projects

B is for bureaucracy – that hated of all things!  The two biggest producers of bureaucratic requirements are governments and union (as in Union Contracts). On the plus side, it often compels companies to buy software. Healthcare is one of the biggest ones but it require domain expertise often. There are many more, for example staffing levels at nursing home [paper]. You look at the requirements and start building a sellable application around that.

 

The key to this is to be first or early to market with compliance to the regulations. There are two approaches –

• producing a replacement system for what they are using. Your key selling feature is compliance, but you should be competitive on features (see prior post)
• produce an add-on. This means researching what people are using and figuring out how to get the data out/in. For example, many businesses use Quick Books.

Many firms are customer request driven – they will not look at reporting requirements until the customers request it. The customer will not request it until they are reminded by government that they must provide the information. They are reactive. You get the advantage by being proactive. Your sales force can educate the customer and then sell the package.

 

The add-in approach is low investment with the added advantage that your customer may well ask for additional addins that the product provider have not produced.  At some point the combination of extra features and familiarity of the competitor product may enable you to offer a complete replacement to the same customers…

You need to learn about how regulations are issued – usually they are circulated in draft form. Look for the newest/latest requirements.

 

If you start at a state, city or county level you may even be successful in providing them software on a fee-per-transaction basis (paid by the customer) for capturing the regulation data on a web site. With budget crunches, it would be tempting…

 

“B” projects are not exciting but they tend to be an overlooked area.

So you’re a developer and want to do a start-up …

The first thing to remember is that there are a lot of people that have invented (and patented) better mouse traps that are flipping burgers at MacDonald… A successful startup need a balance between marketing and development. I have seen startups where sales and marketing generated orders that could not be fulfilled by development because they promised stuff that was not deliverable. I have also seen startups with excellent product that could not get enough sales to maintain the company.

 

Recently I attended an evening called “Speed Geek” – a cross between Speed Dating and Dragons’ Den.  Two of the sets of pitchers were folks that I know from various local groups – I got indirect feedback later that for both sets, I provided the best feedback of anyone taking part.

 

So, I thought a post describing what I consider to be due diligence before ‘investing time or money’ in a startup.

 

Best of Breed Analysis:

1. Have you Googled and identify all existing competitors?
2. Have you created a spread sheet for each competitors citing their feature sets (and short falls)?
3. Have you obtain information on their pricing or business model?
4. Have you obtain a list of existing customers?
5. Have you investigated the characteristics of their staff (LinkedIn.com is ideal for this!)
6. Have you obtain information about their sales/revenue?

All of the above “boring business stuff” is essential. If you have trouble doing this yourself, you may be able to find a seasoned new-product manager to do it for you for an equity stake (I would suggest 5% – after all, the odds are that the results will be a “no-go” so it must be worthwhile enough risk for the person to put their time into!).

 

So what does the above give you?

• Taking a UNION of all of the features should give you a rough specification of what should be in your product – you want to be able to compete on features!
• This specification can now be used to estimate time for development and man-hours required.
• Identify some candidate customers to approach (steal!) by offering the product for free as a beta to get essential feedback on what it would take to steal them to you!
• Viable pricing range – you may feel it’s worth $1000/seat but your competitors are licensing it at $200/site…
• Is this a saturated or underserved market? Do you make it by stealing customers or by making new customers (from people that are not using any existing products).
• Reverse engineer some competitor products --- often a sale depends on the ability to migrate their data (think MS Money –> Quicken, Microsoft Word –> OpenOffice)

Open Source Analysis

You need to review all open source projects in this area. The reasons are as follows:

• If any of them are technology-compatible with your skill sets, you may wish to get involved in the open source activity and shift to a Services Model. Your business model is customizing and extending the open-source product.
• They can often serve to jump-start development (many of the features may already be implemented) if the licensing is suitable for borrowing.
• The folks active on open-source equivalent are possible “remote developer candidates” if you go proprietary and need to grow staff.

Putting up strong fences

By this I mean, how do you keep your company alive once you get enough of a market that someone else wants it!

• Remember, the above analysis – you want their analysis to be unfavorable once you are established!
• Classic Fences:
• Intellectual Property (Patents)
• Better pricing
• Better features
• Lower learning curves
• High cost of moving data to a new system (the “LockIn”)
• You can be friendly for exporting slices of data while making a migration difficult.
• Better customer service/satisfaction

Business Expertise

One of the thing that dominated the Speed Geek was folk that had no business expertise or knowledge in the area they were pitching. Think of “back-seat driver” or the family health advisor (“Drink plenty of chicken soup”). Without an understanding of how the people who may be buying or using the system, you will likely design an awkward product and run out of funds before you can do a revision.

 

If you are a software nerd, enlist your significant other or friends to provide business expertise – often good products come out of user frustrations…

 

Bottom Line

A good startup team consists of a marketing type, a new-product manager, a domain expert  and development. Your odds of success increases with the amount of experience in each. In some cases one person can do all of the roles….  but that is rare and often the bandwidth to do it decreases over time if you are successful. If you do not have the money to hire, then consider equity stakes instead – don’t be cheap here or you will end up with 3rd string folks only. Don’t just fill positions with warm bodies – go for folks with the best track record/expertise and acquire one at a time. The current team should unanimously approve each new member. Often a veto will happen because someone sees issue of “philosophy conflict” or “personality conflict”.  Respect the veto – you cannot afford wasting energy dealing with these issues.

 

Have FUN!

A common error in implementing RBAC in SQL Server (and .Net)

Reading the academic literature on RBAC I noticed a qualification which is very significant but is rarely emphasized to get on the typical reader’s radar. The qualification is stated in the early works from 1996 and in the recent National Institute of Standards and Technology (NIST) RBAC documents. The qualification is that only one role may be active at a time.

 

It is not unusual to see all access rights being consolidated with something like this in TSQL:

 

SELECT DISTINCT AccessRight 
FROM [UserRole]
JOIN [RoleAccessRight]
ON [UserRole].RoleID=[RoleAccessRight].RoleID
WHERE UserID=@UserID

The problem is that this violated one role being active at a time!  This is not a problem if users are allowed to be assigned to only one role at time, unfortunately that is rarely seen.

 

Let me explain with a simple example. Prof Turning is an Instructor and assigned the Role AI_Instructor with rights (View Students taking AI in current year, Upsert Marks). In other words, he is able to give and change marks for his students.

 

Prof. Turning is also assigned to the Instructor_Performance_Review_Committee with rights (View Students in Faculty for all years).

 

If you do the above TSQL, Prof.Turning can view and change any grades for any student, current and past, in the faculty. This was not intended by any party.

 

While the above is contrived for a simple illustration, the same issue can arise in real corporate systems

 

There are two simple situations where the above may be valid:

• Where users can be assigned only one role (thus only one role can be active)
• Where rights are absolutely exclusive (which is difficult to determine).

There are some more complex situations where validity can be constructed  but they run a real risk of breaking down with business evolution.

 

If we view AccessRights above as equivalent to a permission (the right to do an operation on an object:  operation x object) then we can define the criteria that must be satisfied to be exclusive.

• {Objects in AccessRight A} Intersect {Objects in AccessRightB} = null

That is, there are no objects that are in common. Since RBAC typically includes object hierarchy, the complete hierarchy for A and B must be retrieved and intersected. Is this practical?

 

I suspect not:

• You cannot assign roles with overlap to a user
• You must check before every assignment of a role and deny if there are conflicts,
• On every object add or hierarchy change, every role must be rechecked, if there are any conflicts, deny the addition.

These behaviors are not administration friendly because the denials may appear random.

 

Bottom line is that maintaining a single active role is essential. This actually results in complexities when a UI element could be influenced by multiple roles, but that’s another post… Another key factor is that access rights are always in a role context and cannot be removed from the containing role.

 

Post Script: Above we did Insect of objects, an equivalent condition may be stated in terms of actions but since typical actions are far more complex to do analysis on, objects were used.

Implementing Organization Permissions in SQL Server – Basic Tables

The diagram below shows our starting tables (which will evolve).

 

 

Fine level Access Rights  are grouped into Roles through [RoleAccess]. A user is given a role on an organization unit in a specific hierarchical tree through [UserOrganizationRole].

 

The Organization Tree are kept correct by triggers that check any new insert against the allowed patterns stored in OrganizationTypeHierarchy. This will be our first piece of real TSQL

 

CREATE TRIGGER tr_OrganizationTree_Upsert
   ON  OrganizationTree
   AFTER INSERT,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    IF (Select Count(1) FROM OrganizationTree INSERTED)
    <>
    (SELECT Count(1)
    FROM OrganizationTree INSERTED
    JOIN Organization Parent
    ON ParentOrganizationID=Parent.OrganizationID
    JOIN Organization Child
    ON Inserted.OrganizationID=Child.OrganizationID
    JOIN OrganizationTypeHierarchy
    ON Parent.OrganizationTypeID=ParentOrganizationTypeID
    AND Child.OrganizationTypeID=OrganizationTypeHierarchy.OrganizationTypeID
    AND OrganizationTypeHierarchy.TreeID=Inserted.TreeID)
    BEGIN
        RAISERROR('Organization Tree upsert is invalid',16,1)
        ROLLBACK
    END
END
GO

What we are doing is taking the parent/child organizational units and looking up their types.  We then see if these types of organizations may be parent/child in the specific tree.

 

At the top of the trees, we have the ParentType and ChildTypes being the same which allows the top of the organization tree to be an organizational unit that is it’s own parent. This approach is actually helpful to locate hierarchical roots with OrganizationId=ParentOrganizationID.

 

Our first modification will be to RoleTypeDef.

• We will add a Bit indicating if the Role applies to:
• the Organizational Unit OR
• the Organizational Unit and all of it’s children in the specific tree.
• We will assume that we will have < 127 different Access Rights (usually enough for most practical applications) and create two BIT MASKED columns of 64 bits which will be done as a BIGINT (instead of VARBINARY etc) because it is easier to manipulate in TSQL.

So the revised table is:

What we need to do next is update the bitmasks whenever a change happens in RoleAccess table. to do this we need to use a little function to convert our AccessRightID to a bit mask.

CREATE FUNCTION GetBitMask( @bitposition SMALLINT)
RETURNS BIGINT
AS
BEGIN
DECLARE @results BIGINT
SET @results = 1
WHILE @bitposition > 0
BEGIN
  SET @results = @results * 2
  SET @bitposition = @bitposition -1
END
RETURN @results
END

We now add equivalent to AccessRightDef

and add a trigger to update these two new columns from the value in AccessRightID.

CREATE TRIGGER [TR_AccessRight_Insert]
   ON [AccessRightDef]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
UPDATE AccessRightDef
    SET BitMask= dbo.GetBitMask( (AccessRightID % 63)),
        MaskNo=AccessRightID/63
    WHERE AccessRightID IN (SELECT AccessRightID FROM INSERTED)        
END

We calculate the bitmask once per AccessRight for performance gain.

 

The next trigger recalculates all Roles Bit Masks whenever there is a change. Since Roles tend to change very slowly – there should be little performance impact.

 

CREATE TRIGGER [TR_RoleAccess_Upsert]
   ON [RoleAccess]
   AFTER INSERT,UPDATE,DELETE
AS 
BEGIN
    SET NOCOUNT ON;
UPDATE RoleTypeDef
    SET BitMask1=RevBitMask  
FROM   
(SELECT RoleTypeID AS RID, SUM(ARD.BitMask) AS RevBitMask
FROM AccessRightDef ARD
JOIN RoleAccess RA
ON ARD.AccessRightID = RA.AccessRightID
WHERE MaskNo=0
GROUP BY RoleTypeID) Revised
JOIN RoleTypeDef
ON RID = RoleTypeDef.RoleTypeID

UPDATE RoleTypeDef
    SET BitMask2=RevBitMask  
FROM   
(SELECT RoleTypeID AS RID, SUM(ARD.BitMask) AS RevBitMask
FROM AccessRightDef ARD
JOIN RoleAccess RA
ON ARD.AccessRightID = RA.AccessRightID
WHERE MaskNo=1
GROUP BY RoleTypeID) Revised
JOIN RoleTypeDef
ON RID = RoleTypeDef.RoleTypeID
END

That’s it for today. We now have Roles that have bit masks of access rights (we have two columns for bitmasks but can easily add more) that are automatically maintained by triggers.

Implementing Organization Permissions in SQL Server - Overview

Permissions are often a complex problem that depends very much on the context. The closest non-computer analogy that I can think of is that of human language grammars. Sentence structure is not always <Subject> <Verb> <Object> in English and definitely not so in many languages. My experience is that there is no universal “permissions structure”, just like there is no universal sentence structure.

 

In this series I’m going to look at a modest complexity implementation  with the following organization structure

• Organizations are in a strict reporting hierarchy (tree) with:
• Organization Units (OU) being of specific types
• The type of parents/child OU are defined and enforced in SQL Server
• Relationships are just parent/child
• Organization are in a “cross-reporting” hierarchy (tree) that is relative but simple.
• OU relationships are just parent/child.
• One OU may have multiple parents
• Roles are sets of fine grain Access Rights (AR) on an OU
• Examples are:
• View Budget
• Add Employee
• Add Document
• Users may be granted Roles on OU. Roles can be one of two types:
• OU scoped (AR applies to OU only, not to children)
• OU Tree (AR applies to OU and all children under a specific tree)

I will be developing the SQL Server 2008 implementation as well as a complementary C# implementation for a Web Site.  The code is not optimized for use in large/huge organizations but should be sufficient for small or medium size organizations . In general ID’s are done as INT Identity(1,1) for code simplicity and columns are minimal (no CreatedBy, CreatedDate, ModifiedBy, ModifiedDate etc etc) and creators do not become owners.

 

The purpose of this series is to illustrate a simple implementation for a complex problem that exploits TSQL Table and Scalar Functions in SQL Server.

 

Stay tune…

SQL Server 2008 R2 on Windows Server 2008 R2 Purgatory

I’ve spent a day trying to get SQL Server 2008 R2 Installed on Windows Server 2008 R2. Not just on one box, but on several (including Windows 7). What should be a trivial matter gets very obtuse.

 

The boxes have VS 2010 installed, and .Net 3.5 SP1.

When I run setup it, I get this lovely message:

 

Using the .NET Verification Utility confirms the installation

 

So I have to install that which is installed. Googling did not find a solution and a lot of folks suffering the same problem. I had encountered this issue before and put it on the back burner (not upgrading to R2) after spending a day or two trying to find a solution without success.

 

To add further mystery – I see that some R2 features are installed…

Looking at the Install Log (in the %tmp% folder), reveals:

09:16:23.067 Attempting to find media for .Net version 3.5
09:16:23.067 Error: Cannot determine file version of .Net redist: 0x80004005
09:16:25.984 .Net version 3.5 installation failed, so setup will close. Error code: 0x80004005

 

Currently still looking for a solution (tried most of those suggested on the web)

• Uninstalling all features and roles then do 3.51 only: failed.
• Uninstalling all prior versions of SQL Server: failed

1. Success finally by downloading the latest trial version and then copy all of the child folders to the old image. It was a data corruption issue.

Some observations on Startups / Angels Funded Company

In the last 2 years, I have been involved as a consultant with 4 startups – some for direct cash, some for equity. In the DotCom boom days, I interviewed around to literally dozens of startup, declined offers from several of them, and ended working for one Startup doing corporate software which eventually folded 18 months after 9/11 because corporate IT Budgets were still severely constrained. When it folded, I checked back on the status of all of the other start-ups that I interviewed with and all of them had already failed except for one. The reason that I declined offers from other startups were because I evaluated the odds of their success to be very low. The typical issues were:

• Service Company trying to become a product company: product development mentality and contracting/consulting mentality are opposites. What you seek in employees in one is what you do not want in the other.
• Yet Another Monkey-See Monkey-Do: The product they were trying to create was imitating other products. They had no IP (i.e. patents to protect their market impact). This is the “Starbucks, Seattle Best” imitation syndrome. 
• You can be successful with a coffee-shop chain – locally we have two thriving chains: The Woods Coffee (9 shops) and Cruisin Coffee (13 shops) with many outlets in two counties only.
• Imitation with variation can work if you have an appropriate niche strategy to separate you from the big ones:
• Cruisin Coffee has 24hr drive thrus
• Woods Coffee has atmosphere and targeted towards the local culture (University Students, Whatcomites – which has a very strong “buy local” movement).

The recent startups that I’ve been involved with had Patents and in some cases, needed several Ph.D.’s in numeric methods to write the code. They were viable which simply means that the odds of them succeeding was likely over 20%. The interesting thing that I observed was that the greatest threat was organization issues. Some examples:

• CEO and Sales disconnected from development reality. Some examples:
• Promising things that development was unable to deliver on schedule.
• A good dev shop should make deliverables 90% by the original date. Failure to deliver usually means a  breakdown in communications (or ability to understand the issues involved) somewhere between management levels.
• I still remember one case where the CEO was fired by the board and while the CEO was still on the floor, the entire staff broke out to cheers and pounding on the desk in joy.  Both sides were frustrated, the CEO because dev did not deliver; the devs because they were expected to do the impossible.
• Product was done with quick-to-market, no analysis under the disguise of AGILE or other in vogue terms. The result was that after 2 years of business they were struggling with inflexible code written in a needlessly complex manner. Effectively AGILE has become a spaghetti factory. Release cycles had become so short that no refactoring-as-needed happened (an essential for Agile to be successful), instead, a new layer of code was widget on top of the older code to make delivers.
• Often advocating analysis before development will be attacked as trying to force Waterfall on the project. This is both bogus and may reflect a preference to avoid accountability. A good analysis done by a product manager in not waterfall and essential for planning and creating a schedule that people can be held accountable to…
• Picking key players with disjoint technology skill stacks. This can arise especially when a non-technologist (or academic) is the founder. There are a few folks around that are respectful and plays well with others, but in general these are rare. If you hire two senior development types: one from the C#/SQL Server world and one from the PHP/MySQL world, you are setting up a situation of constant conflicts unless there are clear boundaries. For example, the following could be viable
• Database: SQL Server
• Web Site: PHP
• JSON, SOAP, WCF Interface: C#
• Android/Blackberry Development: Java
• Mobile Phone: C#
• BUT it may depend on whether PHP developers will freely accept SQL (instead of MySQL) as the back end; if not, you are creating an in-fighting and complaining scenario.

Often I’ve heard the excuse “we can’t afford to do A,B,C”. The reality is that it is actually cheaper to do A,B,C – the problem is usually “the discipline to do A,B,C. Doing this is either a ‘bummer’ or they do not know how to go about it (knowledge gap)”.

 

It’s interesting to note the recent Ig Nobel prizes which found “"the best ways for improving the efficiency of a given organization are either to promote each time a random agent, or to promote randomly the best and worst members" .. Speaking of which, Sales forces are often referred to a slime by some developers – we now know “slime moulds can find the fastest path through a maze to find food”, so there may be some truth in the use of the term ;-)