June 14, 2016 at 7:55 am
A developer wants to create a front-end application that presents job scheduling information from the msdb (i.e., sysjobsteps and sysschedules tables). Is it a 'bad practice' to grant developers read access to system tables? Or, should the DBA create a process which loads data from system tables into user database tables? Thanks in advance.
June 14, 2016 at 8:05 am
What kind of authentication is the developer planning to use for that application?
June 14, 2016 at 8:06 am
I don't see what's the problem. The developer should be able to read, at least in non-production environments to create stored procedures which would be deployed to production to apply a more strict security.
Definitively, I wouldn't support duplicating data. The whole reason of not granting read access is to prevent people to read the data, it doesn't make sense to allow them to read a copy of the data.
June 14, 2016 at 8:10 am
It wouldn't hurt anything if they simply query specific system tables, so long as they don't do something stupid like set transaction isolation level to serializable, use a TABLOCK hint, or query the tables every second, which could block the SQL Server Agent from writing to the tables.
Another issue is that allowing users to view the T-SQL text of job steps or other configuration settings could potentially reveal things like embedded login credentials that should only be known to the DBA. It would probably be best if you create a view or stored procedure call that returns only the columns they need.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 14, 2016 at 8:22 am
Windows authentication.
June 14, 2016 at 8:30 am
Luis Cazares (6/14/2016)
The developer should be able to read, at least in non-production environments to create stored procedures which would be deployed to production to apply a more strict security.
Luis,
I am talking about production environment. Would you please clarify the above statement. Are you saying the developer should be granted read access to the system tables and allowed to create stored procedures in test? Then the stored procedures (which access the system tables) should be moved to production?
June 14, 2016 at 8:35 am
HookSqlDba7 (6/14/2016)
Luis Cazares (6/14/2016)
The developer should be able to read, at least in non-production environments to create stored procedures which would be deployed to production to apply a more strict security.Luis,
I am talking about production environment. Would you please clarify the above statement. Are you saying the developer should be granted read access to the system tables and allowed to create stored procedures in test? Then the stored procedures (which access the system tables) should be moved to production?
That's correct.
Most times, a developer shouldn't have any access to production at all. It depends on the kind of system that is being managed, but data shouldn't need to be fixed if the code works correctly.
June 14, 2016 at 8:43 am
Make sure you are backing up MSDB with new permissions, and you script out changes you make as these won't be in a new instance if you have a disaster.
I see no problem with read only access for a developer if the application is going to be surfacing information about SQL Agent.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply