Featured White Paper(s)
An Introduction to Workload Tuning
Workload Tuning is just what it sounds like: tuning the performance of all processes that comprise a database workload in one… (read more)
Featured Script
dba3_fn_TableIdentityColumns_WithIdentCurrent_Article
UDF, returns identity column names With Ident Current values for a designated table in the same database. Requires a paramete… (read more)
Authentication vs. Authorization
Authentication and Authorization are two completely separate things; even in SQL Server. Frequently they are confused or commingled because they are often implemented using the same tool. In SQL Server you can create a new user, and in the same dialog grant access and roles to databases, and SQL Server Roles.
So, let me clarify the obvious…
Authentication is the process of validating that you or some process is who it says it is. Authentication can be performed using a UserID and Password, or a valid token from some other authentication source such as active directory, or third party authentication process.
Authorization is the process of granting permission to data, application functions or resources. The capabilities I have when using a system are determined by the authorization, not by the authentication. Authorization in SQL Server is performed in a number of ways via Schema, SQL Server Roles, Grant/Revoke rights, and even in Active Directory.
Now you begin to see the confusion when both Authentication and Authorization may be implemented in the same dialogs, or something like Active Directory.
SharePoint is one application that more fully utilizes the capabilities of ActiveDirectory and it’s integration with SQL Server and Reporting Services. Your credentials determine what you are allowed to see and what you are allowed to do. Since Active Directory accounts can be integrated into SQL Server User Accounts, you can implement Horizontal and Vertical partitioning based on the user, resulting in different data presentation from one person to the next. There is a lot more to this topic of course.
Many applications are written for a large audience (such as web applications) that do not create credentials using the native aspects of Windows Active Directory or SQL Server User accounts. Many web sites require you to create a UserID and Password; but few would actually create a database user for that UserID. Instead they create and maintain their own system for authentication and application permissions.
Even in a design for a large audience you can create a data structure allowing you to partition data based on a users credentials. The difference here is that you have to create the filtering capability yourself and can’t use the features built into SQL Server natively. This isn’t necessarily a bad solution in that you have many options on how you implement those capabilities. In fact, it would be easier to integrate a solution like this with the authentication of another product.
At one client where I worked, we integrated the SUGAR CRM application into an in house application quite easily because we separated Authentication from Authorization. Sugar had it’s own authorization methodology. As a result, we were able to jump from the one web app to the other without requiring the user to sign on a second time…the Authentication was shared between both applications.
Another client provided a web application that was branded and embedded in customer web sites. The end user never knew that the data was implemented in a completely separate system. The Authentication credentials were shared from the customer application so that the user, when transfered to the clients web application they did not have to sign on a second time; the Customers web site was responsible for Authentication.
The next time I write an application from scratch I think I’ll first write an Authentication application that can be used as a centralized method for any new application, or find a canned product I can build on.
Well, enough for today. Drop me a note if you have ideas or comments from your experience you’d like to share with our readers. As always, send your email to btaylor@sswug.org.
Cheers,
Ben