January 20, 2012 at 5:01 pm
vikingDBA (1/20/2012)
You are wanting to do schema-wide permissions? If that is true, it is not to the best practice of "least-permissions", only giving them permissions on the objects they need. If you give schema-wide permissions, it is not granular, therefore not to the best practice. It is your choice, of course.Please clarify.
I have that database used by some people of the companie. The database should be accessible only by those people and nobody else.
Every one has select permission to every tables of the database.
Some people are responsible to enter data, others to update or delete but only to specific parts of the database. Also, some parts of the database are updated by every one and the lookup tables can be modified, but with no "Insert" permission and only by the person responsible of the parts the lookup table is part of.
So, because there where so much tables to managed, I created schemas to represent the parts of the database, and now I was created the roles to represent wich guy has which permission on which schema and some exceptional tables. Maybe there is a better way to design the database, but I did the best I could. Like I said, I have so much to learn!
What is a schema-wide permissions?
January 23, 2012 at 8:47 am
What is a schema-wide permissions?
GRANT UPDATE ON SCHEMA Production
This is a schema-wide permission. You have granted update on all tables in this schema to someone (need a "TO <whoever>" on the end), instead of granting SELECT on specific tables. This would imply that even if new tables were added to the schema, they would have select permissions on them as well, where granting to specific tables would not give them access to a new table unless SELECT permissions were specifically granted on that specific table.
If you look up "schema permissions" on books online, it shows that this practice can also have unintended consequences regarding object ownership chaining and access to the objects.
Just making sure you are aware of the issues with granting far-reaching permissions, and are ok with them.
I prefer to set up roles, and grant the specific permissions needed to them, and then assign whatever users to those roles. This is the "best practice" I referred to previously. If your specific needs and requirements dictate that this is the way to go, then that is fine, also. Unfortunately, I have been jaded by the less-than-stellar practices of third-party-software vendors, who give the application user db_owner access because they didn't want to deal with the issue of assigning only needed permissions.
January 23, 2012 at 5:34 pm
No, I am not doing shema wide permission, I am doing role that gives schema permission.
The command in my "QueryFile" would be something like
GRANT SELECT ON SCHEMA::HumanResources TO TilewRole
then I put people in different role.
If I add a table, it has to be in a schema and the access to the schema is predefined in eveny role so it is secure, but no, no schema wide permission.
Thanks!
January 23, 2012 at 5:52 pm
vikingDBA (1/23/2012)
What is a schema-wide permissions?GRANT UPDATE ON SCHEMA Production
...
I prefer to set up roles, and grant the specific permissions needed to them, and then assign whatever users to those roles. ...
When you say that you 'set up roles', do you mean that you put 'SELECT' permissin to all the tables (e.g. the 70 tables if we take the AdventureWorks2008 database) into a role if the role should permit a 'Read Only' on the database? Or you are doing something different?
January 26, 2012 at 1:05 pm
No, that is correct. I create an AppReader role, and grant SELECT to each table to that role.
For the AppUser role, I grant INSERT and UPDATE to each table that I want to give them those capabilities. I don't give the AppUser role the ability to Update or Insert to lookup tables.
For AppAdmin role, they get full access, even to insert and update on lookup tables.
We don't grant DELETE permissions, instead opting for a CurrRec column (bit) that they can turn on and off, essentially hiding the row of data. That way, it can be easily turned back on if needed.
If I add a new table to the database, I have to grant all the SELECT, INSERT, and UPDATE permissions to the appropriate roles, or they don't get access.
We use this scenario when we use Access as a front end. Then it just connects straight to the tables.
I have a separate set of roles that I assign permissions for regarding all the Stored Procedures that I create, in case it is a .net program.
January 26, 2012 at 5:21 pm
Neet! I like the idea about the CurrRec column
But your "scenario" would not work for us.
Different need, different solution!
That is what I like: nothing "repetitive", always have to work the mind to adapt to new requests
Thanks for the information!
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply