April 18, 2017 at 7:16 am
ranitb - Tuesday, April 18, 2017 6:23 AMGilaMonster - Tuesday, April 18, 2017 6:00 AMGRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON schema::Sales TO <database role>That's the basic principal of it. Don't grant permissions to the database, don't use the db_owner fixed role (or the sysadmin fixed server role). Grant permissions on schemas to database roles (your own ones). Add roles to database users
Thanks GilaMonster. I think, I have started getting this SS technique.
Let me tell you what I understood -
Each user (i.e. a person trying to access the database) will have their own credential (user_id and password). When the user logs in he will land on the configured default schema, and has access to only those schemas where he has been granted the privileges.
Ex. User "RB" logs into database and lands on schema "Sales", as configured by DBA. After that he will have access only to those schemas (i.e. objects present in it) which has been granted.Is this understanding correct? Please rectify if wrong.
Oops I missed Roles. Where do they fit in ?
For the basics, that's roughly it, yes. It gets very complex from there.
First, you don't have to have a login and password. In fact, it's better if you use Active Directory logins. That way passwords are not part of database management. Second, you don't have to, nor should you, grant individual AD logins to the database. Instead, have the AD team set up groups and you grant group access to the database. That way, an individual login can be added to or removed from the group, and no database or server changes are needed.
Also, you can hop across schemas by setting up procedure owned by an account that has access across schemas and then granting execute priveleges to that stored procedure (and only to the stored procedure) for an account that otherwise couldn't access the schema.
In short, this gets thick and complicated. Just take it a bite at a time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 18, 2017 at 7:47 am
ranitb - Tuesday, April 18, 2017 6:23 AMEx. User "RB" logs into database and lands on schema "Sales", as configured by DBA. After that he will have access only to those schemas (i.e. objects present in it) which has been granted.
Depends entirely on what permissions have been granted to that user. If the user has been granted permission on schema ABC, then he'll be able to access the objects in schema ABC, no matter what his default schema is. If he hasn't been granted permissions then he won't.
Granting permissions can be explicit (GRANT SELECT ON...) or derived from database roles like db_datareader or db_owner, or derived from server roles like sysadmin.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2017 at 8:08 am
ranitb - Tuesday, April 18, 2017 6:23 AMEx. User "RB" logs into database and lands on schema "Sales", as configured by DBA. After that he will have access only to those schemas (i.e. objects present in it) which has been granted.
Oops I missed Roles. Where do they fit in ?
The default schema is only used to determine the implicit schema if an explicit schema is not included in a query. If a users default schema were ABC, then Select * From Table would actually be implicitly understood as Select * From ABC.Table. The default schema has no affect on security. It is just for query context. However, it is considered best practice for querys to explicitly identify the schema for every object. It is possible to set a users default schema to a schema they don't have access to use. That would effectively require that user to always explicitly declare the object schema (I should of done that a while ago:) )
SQL Roles (Server & Database) are similar to AD groups. You create a SQL Role, grant/revoke permissions for the role and assign logins to the role. Those roles will have all of the permission defined by that role. A sql login can be part of multiple roles, in which case the permissions for the login are the sum of all the roles (Deny trumps Grant).
Note also that a SQL Login can be a AD group name, which is actually the preferred strategy. Any AD user in the AD group is treated as that SQL Login. Since an AD user can be in multiple AD groups, and an AD group can be directly mapped to a SQL Login (which can be assigned multiple SQL roles), a single user may be authenticating against multiple SQL logins. The permissions are still the sum of all of the Roles assigned to all of the SQL Logins of all of the AD groups the AD user belongs to. The security model is very powerful, but it can also be overwhelming if not thought out.
For my group, our security practice is that no AD user accounts get dedicated logins. We create logins for AD groups, AD service accounts (identical to a user but not belonging to anyone and generally can't logon to a workstation) and SQL logins for non-AD authentication. When staff comes/goes, we just need to change AD groups. No changes to SQL Server security are needed (with exceptions for admin level users).
Wes
(A solid design is always preferable to a creative workaround)
April 18, 2017 at 8:41 am
Concerning AD Groups/Users in the SQL Instance, we instead utilize an approach which leverages the use of service accounts, which turns out to be a single SQL Server login per application (where the user won't know the password). We chose that route, because with an AD account in the database, a user can access db objects assigned to their account via unauthorized means, such as MSAccess. Not a big deal if all they have is SEL access, but in our determination, unacceptable if they have INS, UPD or DEL access. It requires some build-out, focusing on a secure mechanism for storing/retrieving the service account password though, so it's not for every shop. We did the same thing for Oracle (meaning, no personal accounts), so it wasn't a SQL Server-based decision. But it's a security consideration that I think gets overlooked more with SQL Server because of the ease of setup you get with the marriage between AD and SQL Server logins.
--=Chuck
April 18, 2017 at 10:04 am
chuck.forbes - Tuesday, April 18, 2017 8:41 AMConcerning AD Groups/Users in the SQL Instance, we instead utilize an approach which leverages the use of service accounts, which turns out to be a single SQL Server login per application (where the user won't know the password). We chose that route, because with an AD account in the database, a user can access db objects assigned to their account via unauthorized means, such as MSAccess. Not a big deal if all they have is SEL access, but in our determination, unacceptable if they have INS, UPD or DEL access. It requires some build-out, focusing on a secure mechanism for storing/retrieving the service account password though, so it's not for every shop. We did the same thing for Oracle (meaning, no personal accounts), so it wasn't a SQL Server-based decision. But it's a security consideration that I think gets overlooked more with SQL Server because of the ease of setup you get with the marriage between AD and SQL Server logins.--=Chuck
Using service accounts is also common in the SQL Server world, especially for web applications, or 3-tier client server applications where all the database connectivity is done from an application server instead of from the client program itself. This lets people take advantage of techniques like connection pooling, and as you said prevents people from having direct access to the database tables. I could still see a use for roles though to ensure the least privileges are given to any specific database user/service account that is required for that user/service account to do the work required.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply