October 17, 2012 at 11:20 pm
I have a question about setting up permissions on a sql server 2008 r2 datbase.
I basically wrote and enhanced some C# 2008 and C# 2010 console applications that connected to my test sql server 2008 r2 database. On my test database, I must all full right my default.
My 3 applications were deployed to a user acceptance testing environment that includes a user acceptance sql server 2008 r2 standard database.
The user accpetance database was set up by the network administrator at my small company. He is the only one at my small company that knows a little bit about the dba roles and has locked down permissions.
Due to the facts above, I would like to know what should I be aware that may need to have permissions setup for. My questions includes the followinng:
1. When to decide if role(s) need to be setup and how to setup the roles. When I ran my applications on my test database, I had the integrated security set to true. I did not need to supply the user name and password in the connection strings to the database. My user account was setup to have a role in the database.
Due to what I just said, will I need to have setup roles for the console applications to run on their own? If so, What kind of roles need to be setup and how do you setup these roles?
2. I setup 3 new tables that are under the dbo schema. Do I need to have permissions setup so people and/or roles can have read, write, update and/or execute permissions? If so, how do you setup these permissions?
3. I have also created 2 stored procedures that are used to access the 3 new tables that I setup. Thus do these stored procedures need to have read, write, update and/or execute permissions on them setup?
4. Do statistics (explain plans) need to be run on this database that has hardly evern been used before? If so, how do you accomplisth this goal?
5. Are there other items I need to consider? If so, what are the items and what do I do to resolve those issues?
If you can any part of my questions above, I would appreciate hearing what your answer is also.
October 30, 2012 at 4:56 am
1. When to decide if role(s) need to be setup and how to setup the roles. When I ran my applications on my test database, I had the integrated security set to true. I did not need to supply the user name and password in the connection strings to the database. My user account was setup to have a role in the database.
Due to what I just said, will I need to have setup roles for the console applications to run on their own? If so, What kind of roles need to be setup and how do you setup these roles?
Though there are default DB roles in SQL Server like db_datawriter, db_datareader etc, I rather prefer to have user defined roles created and mapped to these default DB roles to define various level of mappings.
-- This query creates role in the present DB
CREATE ROLE [Role_Name] AUTHORIZATION [dbo]
-- This query maps default reader and writer role to the user defined Role Name
sp_addrolemember 'db_datareader', 'Role_Name'
GO
sp_addrolemember 'db_datawriter', 'Role_Name'
GO
Now the user defined role "Role_Name" is ready to be mapped to either user or application account. You might want to create different roles for users and applications specifically and map these roles later to them.
2. I setup 3 new tables that are under the dbo schema. Do I need to have permissions setup so people and/or roles can have read, write, update and/or execute permissions? If so, how do you setup these permissions?
Once you have created role like above and mapped them to either user accounts or application accounts, the accounts will get the underlying permissions on the tables under dbo schema.
3. I have also created 2 stored procedures that are used to access the 3 new tables that I setup. Thus do these stored procedures need to have read, write, update and/or execute permissions on them setup?
You may like to create separate role to grant execute permissions on procedures to all or selected the users.
-- This query creates role in the present DB
CREATE ROLE [Role_Name] AUTHORIZATION [dbo]
GO
-- This query grants execution permissions on all procedures to the role Role_Name
GRANT EXEC TO [Role_Name]
GO
-- This query gives permission on specific procedure to role Role_Name
GRANT EXECUTE ON OBJECT::[Procedure_Name] TO [Role_Name]
GO
Now the user defined role "Role_Name" is ready to be mapped to either user or application account to execute procedure.
4. Do statistics (explain plans) need to be run on this database that has hardly evern been used before? If so, how do you accomplisth this goal?
You might like to check execution plans at times to optimize a query.
-- This query grants viewing execution plan for a query/procedure to the role Role_Name
GRANT SHOWPLAN TO [Role_Name]
GO
5. Are there other items I need to consider? If so, what are the items and what do I do to resolve those issues?
As the requirement comes on, don't hesitate to go through Books Online about the requirement and trying doing some R&d on you test/dev environment. It helps clearing lots of questions in mind.
Hope this helps atleast for the initial steps.
November 1, 2012 at 9:35 am
First, see the following url for an Instance Security Audit script I wrote. It lists out all the users, roles, and object permissions for every database in an instance.
http://www.sqlservercentral.com/Forums/Topic1251262-146-1.aspx?Update=1
Second, when I create databases, I usually create three roles: a reader, user, and admin. The reader is a read only role that has read-only access to all appropriate tables and read-only oriented stored procs. The User has insert and update capabilities on tables and execute on insert and update oriented stored procs (except for lookup tables). The Admin role is same as User, but also has access to insert and update any lookup table information, because I only want certain people to be able to add any new lookup values to the tables. Note that I use a CurrRec column in each table to "delete" a record. That way, it isn't actually deleted, but the SELECT statements just weed those records out that are set to 0 (false). That way, they can easily be "restored" by setting CurrRec back to a 1 (true).
I also create the CRUD roles for these three role types.
Here is an example:
CREATE ROLE MyDatabaseUser
CREATE ROLE MyDatabaseAdmin
CREATE ROLE MyDatabaseReader
CREATE ROLE MyDatabaseUserCRUD
CREATE ROLE MyDatabaseAdminCRUD
CREATE ROLE MyDatabaseReaderCRUD
Then, I just create a matrix of what tables and SPROCs each gets, and set the appropriate permissions on the tables and SPROCs.
Example:
GRANT SELECT ON dbo.FormData TO MyDatabaseReaderCRUD, MyDatabaseUserCRUD, MyDatabaseAdminCRUD
GRANT UPDATE,INSERT ON dbo.FormData TO MyDatabaseUserCRUD, MyDatabaseAdminCRUD
For the SPROCs, here are some example GRANT statements for the appropriate roles:
GRANT EXECUTE ON dbo.usp_Read_FormData_Rec TO MyDatabaseUser, MyDatabaseAdmin, MyDatabaseReader
GRANT EXECUTE ON dbo.usp_Read_All_FormData_Rec TO MyDatabaseUser, MyDatabaseAdmin, MyDatabaseReader
GRANT EXECUTE ON dbo.usp_Create_FormData_Rec TO MyDatabaseUser, MyDatabaseAdmin
GRANT EXECUTE ON dbo.usp_Update_FormData_Rec TO MyDatabaseUser, MyDatabaseAdmin
GRANT EXECUTE ON dbo.usp_Delete_FormData_Rec TO MyDatabaseAdmin
To add people to roles, use the following example script, changing the loginname to their Windows name (ex: MyDomain\Username):
/* Set the statements to add people to the roles */
EXEC (sp_addrolemember) 'MyDatabaseUserCRUD','loginname'
EXEC (sp_addrolemember) 'MyDatabaseAdminCRUD','loginname'
EXEC (sp_addrolemember) 'MyDatabaseReaderCRUD','loginname'
-- Role, User
For SPROCs, there is something called Object Ownership Chaining. This says that you only have to give them access to execute the SPROC if the SPROC itself and the underlying tables are owned by the same user, preferably, dbo. If that is the case, the user just needs to be given access to execute the SPROC, and the users won't need access to the underlying tables. This is great for creating all the SPROCs that are needed, and just giving access to execute the appropriate ones. Then you don't have to give anyone access to the underlying tables, which makes your design much more secure They can only run the SPROCs you give access to, and can't do a wholesale "SELECT * FROM something" to get all the data from a table. They can only get what the SPROC lets them have. Note that if the owner of the SPROC and the underlying table are different, then the users will also need access to the underlying tables that are in the SPROC.
Please research this concept thoroughly in case I have made a mistake in any of this.
Anyway, hopefully this and the previous post will give you a good idea of where to start and some things to research.
November 1, 2012 at 9:40 am
I tend to do what VikingDBA has done. I don't always have 3 roles, but I do always have the user role, and an admin role. Note the latter isn't db_owner or sysadmin, but may have permissions that allow administration of the tables in some way that the user role does not.
Create the role, always do this. Always. Assign permissions as needed to objects with the GRANT statement.
If you find a user that needs different rights to different tables, just create a new role. It's not hard or time consuming.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply