Best Practice on Users/Roles on SQL Server for a Web Application

Best practice on users/roles on SQL Server for a web application

First, I tend to encapsulate permissions in database roles rather than attach them to single user principals. The big win here is roles are part of your database, so you can completely script security then tell the deployment types to "add a user and add him to this role" and they aren't fighting SQL permission boogeymen. Furthermore, this keeps things clean enough that you can avoid developing in db_owner mode and feel alot better about yourself--as well as practice like you play and generally avoid any issues.

Insofar as applying permissions for that role, I tend to cast the net wider these days, especially if one is using ORMs and handling security through the application. In T-SQL terms, it looks like this:

GRANT SELECT, UPDATE, INSERT, DELETE, EXECUTE on SCHEMA::DBO to [My DB Role]

This might seem a bit scary at first, but it really isn't -- that role can't do anything other than manipulate data. No access to extended procs or system procs or granting user access, etc. The other big advantage is that changing the schema--like adding a table or a procedure--requires no further security work so long as you remain within that schema.

Another thing to take into consideration for SQL 2005+ is to use database schemas to secure groups of objects. Now, the big trick here is that many ORMs and migration tools don't like them, but if you render the default schema [dbo] to the app, you can use alternative schemas for special secured stuff. Eg--create an ADMIN schema for special, brutal database cleanup procedures that should be manually run by admins. Or even a separate schema for a special, highly secured part of the application that needs more granular DB permissions.

Insofar as wiring in users where you have separate boxes, even without a domain you can use Windows authentication (in Sql Server terms integrated authentication). Just make a user with the same credentials (user/pass combo) on both boxes. Setup an app domain to run as that user on the web box and setup a Sql Server user backed by that principal on the sql box and profit. That said, using the database roles can pretty much divorce you from this decision as the deployment types should be able to handle creating sql users and modifying connection strings as required.

Using SQL Server Users and Roles as an authorization database for an intranet web application?

I recommend creating a sql login that the web application will use to connect to sql server. This way you are not impersonating any specific AD account which may get deleted, disabled in the future and can control the user strickly in SQL Server.

I would then recommend implementing roles based authentication in your application. This will enable you to create users and roles that are custom to your application and then assign users to them. This way if a user tries to access a resource that their role is not allowed it will not do any work. Here is a demo app based on this principle http://www.codeproject.com/KB/web-security/rolesbasedauthentication.aspx.

Best user role permissions database design practice?

As krokodilko wrote in his comment, it depends on the level of flexibility you need.

I have implemented role based permissions for one of my clients as follows:

1. User (user id (PK), user name (unique), password (salted and hashed!), first name, last name, phone etc')
2. Role (role id (PK), role name (unique), role description)
3. Permission (permission id (PK), permission name (unique)) - the tabs / screens / actions goes here
4. User To Role (user id, role id) - PK is both columns combined
5. Role to Permission (role id, permission id) - PK is both columns combined

But my requirement was to be as flexible as possible, and it is a system that is still growing (6 years and counting).

I guess a lot of applications can have the user to role as a one to many relationship, instead of a many to many like in my case, but I wouldn't go hard coding permissions or role to permissions in any application.

Further explanation: Role based security database design on What the # do I know?

What is the best practice for role security for an Intratnet ASP.NET/SQL2K5 environment?

ASP.NET 2.0's Membership, Roles, and Profile

What is the best practice for role security for an Intratnet ASP.NET/SQL2K5 environment?

ASP.NET 2.0's Membership, Roles, and Profile

best practices of accessing db from web application

normally you only have one mysql account which can access the database, more accounts for one application (to increase security) make no sense for me because if your systems gets compromised all logins are leaked because they need to defined in your application code.

on the other side you give attackers with many mysql accounts more possibilities to attack.

so what is left for using multiple accounts in one application, ahh access user actions against the database (limit user a with no edit rights etc.).

what a user should do against your database (delete things a, edit things b) should be handled by your application logic, for sure it is good to limit operation access per user (eg don't allow to write into outfile etc.) but not to handle such simple things. the cost (multiple data connection per user) is in my eyes much higher then a good programming logic.

so increase security wich multiple accounts is pointless, use one account with a strong password and let the simple actions edit, delete etc. handel by your programming logic.

Which is the best practice for allowing users to acces a database from database perspective? One db user vs a db user for every user

To me, it looks as one schema and many users.

Which XE is it? The most recent lets you store 12GB of user data which is quite a lot.

Creating a schema for every user is ... quite strange. Why would you do that? Users will access data you store, but they don't have to have their own schemas. That would, actually, make things way more complex as you'd have to grant access on objects & data (from user who owns everything) to each of those users (or use roles).

Additionally, you don't even have to create database users - you could have your own "users" table, develop authentication (and then authorization) so that they would be able to connect to your application (that's authentication) and do various things (read-only, insert new rows, manage current data ... - that's authorization).

As you use Oracle, I hope you consider using Oracle Apex as a tool to develop that application.

---

Related Topics