Generating a Range of Dates in MySQL

Working on a report from a MySQL database, I needed a table of all dates for the next year. With SQL Server (2005 and later) there’s a CTE/recursive method to do this pretty elegantly, but I couldn’t find anything similar for MySQL. All the solutions I found involved temporary tables, loops, and/or stored procedures–none of these were viable options for me because it’s a production database and I can’t just go changing things. I needed a simple query, and since I couldn’t find one, I made one.

It’s ugly, but it did the job. Continue reading “Generating a Range of Dates in MySQL”

Active Directory Single Sign-On for Linux Intranet Servers

I mentioned a while ago that I have a Linux web server set up with Kerberos SSO in our AD domain. Setting it up was a lot more tedious than it seems like it should have been. I found bits and pieces of useful information here and there, and some step-by-step guides to help with specific sub-tasks, but I couldn’t find a good, intranet-specific guide to help me understand the big picture—what pieces I needed (and didn’t need) and how they fit together. So here’s part 1 of my attempt to rectify that situation (part 2 will be the WordPress integration—I’m still working on that part).
Continue reading “Active Directory Single Sign-On for Linux Intranet Servers”

Intranet Milestone: Transparent Authentication

I’ve started a project to move the front-end of our intranet from SharePoint to WordPress (SP is just too icky to do any serious front-end work with). The plan is for WordPress to become the front-end and CMS for news-type content, keep SharePoint for file library and calendar-type stuff (at least for now), and use the SP web services to integrate the SP content into WP. All of the various authentications involved must be transparent to the end-user.

Goal #1 was to get all the Kerberos stuff worked out so that Apache would transparently authenticate users against Active Directory (assuming they’re logged into a Windows client machine with their domain account—a reasonable assumption for an intranet, although a good experience logging on from an iPad or other non-domain client is also disirable). It took a bit of trial-and-error, but I got it working! WooHoo!!!

Goal #2 will be to fire up WordPress and get it to recognize that Apache already knows who the user is, create a new WordPress account if it doesn’t already exist, and log the user into WordPress.

This should be fun…   Winking smile

Internet Wiretap Bill Misses the Mark

Charlie Savage reported Monday in the New York Times that the Obama administration is seeking legislation that would require “back-doors” in all encryption products and services in the US. Of course, they cite terrorism as a primary motivation.

How best to balance the needs of law enforcement (and of government in general) with the privacy and liberty of the citizen is an age-old question. While I sympathize with the needs of law enforcement, the Internet wiretap plan simply will not accomplish its stated purpose.

When privacy advocates complain about video surveillance or airport screenings, the counter-argument has always been “If you’re not doing anything wrong, you don’t have anything to worry about.” (That argument assumes that law enforcement officers will use those systems only for their intended purposes, but we’ll leave that aside for now.) The point is that when you’re securing a place—a bank or airport, for example—the security measures apply equally to everyone who goes to that place.

But it’s different when you’re dealing with things. If you mandate that a certain type of thing T must have property P, and it’s illegal to make or possess a T without P, then law-abiding manufacturers will make their Ts with P, and law-abiding citizens will use Ts with P. But what’s to stop a criminal or terrorist from importing their Ts from a country without the stupid P-law? This turns the table to the bad guys’ advantage in two important ways.

First, the world already has robust, unbreakable, back-door-free encryption technology. The criminals will just use that. As with gun control legislation or nuclear non-proliferation treaties, if you outlaw strong encryption, only outlaws will have strong encryption.

Second, if a back door exists, the bad guys will figure out how to exploit it. History proves that. So not only will the bad guys have strong encryption that even the government can’t break, but the good guys will be forced to use encryption that the bad guys can break. It will be that much easier for them to steal money and identities. The law-abiding citizen and the government alike will be powerless to stop them.

So if this bill becomes law, it will accomplish precisely the opposite of its stated purpose. The government will still be powerless to eavesdrop on criminal and terrorist communications. Meanwhile, the good, honest citizen will be rendered powerless as well. That’s a situation truly to be terrified of.

When Low Tech Is the Best Tech

We’ve been thinking about developing a quick application to replace a paper HR process—should be a simple state machine with four possible states: Submitted, Accepted, Rejected, and Completed. But then we realized we would need email notifications and a coherent security model.

personnelchangerequestprocess

These requirements—workflow, notification, and security—happen reasonably well in the old paper model. Not perfectly, but well enough. These mechanisms are ingrained in the way people do their work, but to implement this in a computer application would require us to build it from scratch.

It quickly became more complicated than it was worth, a good reminder that sometimes low tech is the best tech.

The Enterprise Information Protection Paradigm

It used to be that network infrastructure was one of an organization’s most valuable assets and security was geared toward protecting the infrastructure; but costs are falling, and the network has become a commodity.

Meanwhile, the volume and value of information stored electronically are growing rapidly. For this reason, Dan Greer advocates a paradigm shift in information security, which he calls the Enterprise Information Protection Paradigm.

We suggest that this paradigm be called enterprise information protection (EIP). We say “enterprise,” in that, for most firms, data is literally who they are; “information,” …because this data has future value; and “protection” because protecting value is the first responsibility of boards and officers.

In practical terms, EIP means focusing our security efforts at the point of use—every point of use—“where data-at-rest becomes data-in-motion.” It means insisting on secure operating systems, applications, and procedures. And it means monitoring the use of information:

[EIP] is, to the firm, what a conscience is to an individual—that second brain that watches the first with the power to detect bad choices and to act on what it sees. We do not expect perfection in applying EIP any more than we expect perfection of the conscience, but … the goal is worth it.

Focusing security resources at the point of use is not a new concept—Bruce Schneier has advocated that as a technical security tactic for years. And it’s certainly not new to say information is an organization’s most valuable asset and that responsibility for information security goes all the way up to senior management. What I find compelling about this article is that it does a decent job of packaging these concepts together into a single, coherent paradigm.

Dan’s article is a bit long, and you have to slog through clichés like applying the theory of Evolution to information security (do they have Editors anymore?), but it’s worth a look.

The Spam That Got Through

All of my company’s inbound and outbound email goes through a security service that scans for spam and viruses. From time to time I get an email from someone saying that they got a message that they consider spam. I see that as a good sign. Here’s why:

Spam filters are machines, with some human input to fine-tune the filter criteria, doing the best job they can. The algorithms are ever-improving, but they’re still just computer programs.

Also, spam filters read mail, not minds—some of what they see looks enough like legitimate email that they are allowed to pass through. If I, a human, were reading our inbound email feed, I probably would allow many of the “spam” messages, too. It’s not possible for man or machine to know the mind of every recipient, how they would classify every message they receive.

And the humans that fine-tune the filter criteria tend to err on the side of caution: a false positive—deleting a sales lead, a message from an attorney, etc.—is far more costly an error than a false negative—the spam that got through.

According to the reports I get from our spam filtering service, 89% of our inbound email is deleted as spam, 1% is quarantined as likely spam, and the remaining 10% is delivered as normal email. That translates to about 2.7 million spam messages a year that never hit our inboxes. Under that kind of barrage, I’m surprised anyone finds it surprising when a single unwanted message sneaks through.

That’s what I consider a good sign: if end users are surprised when they get a single spam, it means our filters are doing a pretty darn good job.

I hope that puts things in perspective.