Re: Planning security strategy in sql server

It would be something like "assigning a fixed database role to an
application role"...

You can add an application role to fixed database role. For example:

EXEC sp_addrolemember 'db_datareader', 'MyApplicationRole'

Also, if I'm accessing to a database object that references data from
another database trough views / stored procedures and so on by means of
ownership chaining, it would work?

Yes, but there is some additional configuration and considerations:

1) Once an application role is activated, all other databases can only be accessed only as the guest user so you'll need to enable the guest user in the referenced database. No permissions need to be granted, though.

2) You'll need to enable the 'db chaining' database option in both databases. You should do this only if you fully trust users that have CREATE permissions in those databases because ownership chaining will extend to both databases.

3) To maintain an unbroken chain for dbo-owned objects, both databases need to be owned by the same login. You can change with sp_changedbowner, if needed. Note that if the databases are owned by 'sa', ensure that only sysadmin role members can create objects in those databases because ownership chaining will extend to master as well.

I'm starting to think this is not the right way to manage security... maybe
it would be better to store a strong encoded HASH for the administrator's
password IN another table to retrieve it for accessing ALL THE DATABASES
without having to apply permissions to tables etc trough all our databases.

There are issues with application roles and connection pooling so using an application SQL login instead of an application role isn't a bad idea. However, I suggest minimal privileges rather than admin since that is the Best Practice. If all of your data access is via stored procedures, just grant execute permissions on the procs. Personally, I'd use separate logins per application to provide more granularity.

It's definitely a good idea to encrypt the password rather than storing in clear text. However, you can use a one-way hash for SQL passwords because you'll need the decrypted version to connect.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Roger Tranchez" <run1789@xxxxxxxxxxxxxxxx> wrote in message news:0CED4C32-E969-4DE1-834F-3B3FC5662E11@xxxxxxxxxxxxxxxx
Hello,

I'm planning (maybe) to use sql server 2000 application roles in order to
prevent unauthorized access to the database.

To achieve this, I want to login to sql server with authenticated mode using
a domain account that belongs to a group in the domain that has only public
access to the database.

Next I will activate the application role.

For the database users, you can assign the database role "db_datawriter" and
"db_datareader" to assign those permissions in a single step, to all the user
objects in the database BUT, I would like to do it in a single step for my
application role.

It would be something like "assigning a fixed database role to an
application role"...

Also, if I'm accessing to a database object that references data from
another database trough views / stored procedures and so on by means of
ownership chaining, it would work?

I'm starting to think this is not the right way to manage security... maybe
it would be better to store a strong encoded HASH for the administrator's
password IN another table to retrieve it for accessing ALL THE DATABASES
without having to apply permissions to tables etc trough all our databases.

As security is not a very fundamental issue in our company, maybe using this
HASH approach to store the database wood be enough secure... As after all,
the access to different parts of the databases will be controlled by the
windows or web applications through its own user/password table, this seems a
reasonable way to work...

I know everybody RECOMMENDS eeeverywhere not storing passwords, not to let
them travel trough the network and so on, but I would store the HASH... We
are not working at the CIA!

What do you think?

Thanks in advance

--
Roger Tranchez
MCTS
.NET 2005 and DB developer

.

Relevant Pages

  • Re: Viewing object owner in SQL 2005 - ownership chaining
    ... The server principal "HFDB_2_0_readonly" is not able to access the database "Hotfix_data" under the current security context. ... To add on to Erland's response, the user needs a security context in both databases, even if no permissions are granted. ... I'm trying to set up Ownership Chaining. ... When I set up both a table and view from one logon and granted permissions to the view it didn't work, evidently because "creating user" is not equivalent to OWNER. ...
    (microsoft.public.sqlserver.security)
  • Problem with connection
    ... I have website in asp.net and mysql database. ... pages on site that have connections to database created trough c# code ... (with connection strings, database adapters, datareaders..etc). ... Problem is strange: it works fine for a while, ...
    (microsoft.public.dotnet.framework.aspnet)
  • operator creation?
    ... Lets say i have an DB object, who implements the querys to the database ... trough a method called DBObject.doQuery. ... i have 50 sql functions stored in the database. ... If someone understand my problem (and my poor english), ...
    (comp.lang.python)
  • Re: List Users Permissions down to table.column action
    ... THIS STORED PROCEDURE GENERATES COMMANDS ... -- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING. ... -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE ... -- GRANT USER ACCESS TO SERVER ROLES ...
    (microsoft.public.sqlserver.security)
  • Re: User access on a company intranet
    ... Yes they need full permissions on the folder where the backend is. ... You wouldn't need to do this in your copy of the database. ... However you can toggle the shiftkey bypass from another mdb file. ... When you want to implement security, you create a new mdw file, ...
    (microsoft.public.access.security)