TSQL stored procedure, User roles, best design approach ?
-
I wish to discuss TSQL stored procedure logic setup with different user roles.
I am a SQL server newbie getting to grips with user roles and writing TSQL for users and report tables.
In MS access I have all users setup as ADMIN and use passwords and form protection techniques to protect the code.
The ADMIN role allows all users to SELECT, INSERT, DELETE, UPDATE.
I have built many base tables to be used for reports or Excel data dumps with the following logic for all MS Access Users ( ie with the ADMIN Role)
0) DROP any tables created by previous report runs.
1) CREATE TABLE – Set up base table to get data
2) INSERT DATA – Insert data types for comparisons, ie Plans vs Actuals
3) DELETE DATA – Remove any unwanted data
4) CREATE TABLE – Set final reportable that stores all data above but GROUPED
5) INSERT DATA into table created in step4 from table created in step1.
6) SELECT data with joins for presentation to the USER based on USERS WHERE or HAVING instruction.
Lets Jump to SQL server..
The use of ROLES by user is most likely to be more restrictive. Say we have this setup.
Managers – SELECT only
Sales – SELECT only
Developers – SysAdmin privileges ( ALL => SELECT, INSERT, DELETE, UPDATE)
In USER setup shown the Managers and Sales User groups would NOT be allowed to run a TSQL procedure that had the same logic as MS Access as expressed above. These users would only be allowed to run Step 6 of the MS Access report build code.
( On the above the database owner for Step0 to Step5 is dbo, and for Step6 as there is no object creation, so no database owner issue)
So I am trying to understand the best common practice in running the logic for a report of this type.
If this report is required daily, do you set the TSQL logic up to handle step0 to Step5 overnight every evening getting the base table ready, and let the USER select on demand Step6.
Is this best common practise ?
Please advise what you would do ?
Thanks
-
If your first few steps only take a few seconds then I would use temporary tables or table variables and have a stored procedure return the records you want in a one big bang.
If the time to prepare all your logic takes time then i would have a scheduled job that runs a stored procedure to produce your results described in step 6.
I would add a database role for each security group in your databases and assign users to those groups. Never assign permissions to users directly.
If users do not need to access your base tables then consider querying your source database and having a specific target database for them to play with. That way your source is always sacrosanct and if they do cock up the target data you can always rebuild it safely.
-
Excellent response thanks, A few Questions
.."If your first few steps only take a few seconds then I would use temporary tables or table variables and have a stored procedure return the records you want in a one big bang."...
QUESTION : If user has only SELECT role ( ie Sales team), can he use #temp table or table variables, to do CREATE, INSERT, DELETE functions, if not what is best logic or approach?
The points on "Scheduled data runs", and "Group Roles", I understand all ok there.
The last point on setting up a dummy database and giving the Sales team wider roles of SELECT, INSERT, CREATE, DELETE is also a possible approach. I guess this would depend upon how much data one has.
-
TEMPDB is a special case that is used for a variety of cases so you don't need any specific permissions. You will notice that there is a guest user in TEMPDB.
If you do a big sort in your SELECT, or use certain types of cursor then SQL Server will use TEMPDB for its own internal uses anyway.
A point to note on this. When you install SQL TEMPDB defaults to 2Mb. You may want to have a look how big it is at present. When you restart the SQL Server service TEMPDB gets recreated at 2MB. If, after the restart you expand your TEMPDB manually then subsequent restarts it stays at your new size. The reason it is beneficial to have a larger TEMPDB is because it saves the performance hit of having to physically expand the database file during heavy operations.
-
Thanks for help David...Top man !
Is there any example code on re setting TempDB on matters of size ?
-
As we are talking about a database engine that is far and away more powerful than Access. Is there any reason to apply the same logic?
0) DROP any tables created by previous report runs.
1) CREATE TABLE – Set up base table to get data
2) INSERT DATA – Insert data types for comparisons, ie Plans vs Actuals
3) DELETE DATA – Remove any unwanted data
4) CREATE TABLE – Set final reportable that stores all data above but GROUPED
5) INSERT DATA into table created in step4 from table created in step1.
6) SELECT data with joins for presentation to the USER based on USERS WHERE or HAVING instruction.
#1 & #2 could easily be a select query that is used as a derived table
#3 - Inner & Outer joins could filter the data
#4, #5 & #6 are the main select query
In moving from MS Access it is always best to re-think your approach to solving process issues. If you don't quite often you will end up just having an Access database on SQL Server that is tied down by convoluted unesscessary logic purely because that's how it was done in Access.
--------------------
Colt 45 - the original point and click interface
-
No reason to follow same logic, but I am going to have to learn SQL server best practise.
Whats the code for changing temdb size to handle more data ?
-
The easiest way is to use Enterprise Manager.
right-click on TempDB and adjust the size under the Data Files and transaction log tabs.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply