April 13, 2013 at 11:35 pm
My global office programmed all our stored procedures and it contains a lot of create/delete temp tables, truncate tables, update, insert etc.
When I grant a user the rights to execute stored procedures, the user cannot execute these stored procedures because of insufficient permissions. A lot of the stored procedures seem to require that a user executing them not only require Execute stored procedures permissions, but also ended up having db_owner rights.
Is there a way I can control this, please? I do not wish to grant these users the rights to UPDATE, INSERT, DELETE rows, create or delete tables, alter schema rights or even db_owner rights. But it seems to be hard, because they cannot execute the procedures without these.
Is there any suggestions on how I can do this, please? Even adding EXECUTE AS in the stored procedures don't work.
Is there other tools I can use that allows the users to execute stored procedures as an SA, but with logging so that I can review who ran all these stored procedures. Will Control-M or Dollar Universe able to do such things?
Thank you.
April 14, 2013 at 4:31 am
Without seeing the code, this is a guess....
If the user has exec rights on the procedure and the procedure is owned by the same principal as the tables, the user won't need update, insert or anything like that because of ownership chaining. Creating and dropping temp tables requires no permissions. Truncate table is likely the culprit (if done on a user table, not a temp table). Can you change that to delete instead?
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 14, 2013 at 9:31 pm
Hi,
What you mean by the the same principal? Is it dbo?
I cannot modify the stored procedures but I do see truncate table statements in there. When I grant the user db_datareader, db_datawriter and Alter Schema rights, users are creating tables and leaving the tables inside there even though they should drop it. This is a problem for us.
Is there another way I can grant execute permissions without all these rights, just execute and db_datareader?
April 15, 2013 at 3:16 am
Dree Vasquez (4/14/2013)
What you mean by the the same principal? Is it dbo?
Could be dbo, could be another database principal (owner, not schema)
I cannot modify the stored procedures but I do see truncate table statements in there. When I grant the user db_datareader, db_datawriter and Alter Schema rights, users are creating tables and leaving the tables inside there even though they should drop it. This is a problem for us.
Creating and dropping user tables inside a procedure? That will be a problem, not to mention being a rather bad practice.
Is there another way I can grant execute permissions without all these rights, just execute and db_datareader?
Again, without seeing the code, I'm guessing. Ownership chaining will mean that you don't need to grant data reader and data writer.
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 15, 2013 at 8:34 am
In the example below, I've created a stored procedure called [acct_staging_truncate] which truncates a table. The user 'acctmaint' is db_owner, and user 'JoeSchmo' is a public member with no permissions except execution on the procedure [acct_staging_truncate].
create procedure acct_staging_truncate
with execute as 'acctmaint'
as
truncate table acct_staging;
go
grant exec on acct_staging_truncate to JoeSchmo;
go
I've confirmed that JoeSchmo can execute the procedure successfully, but he can't truncate, select, or anything else directly on the table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 17, 2013 at 5:57 pm
WITH EXECUTE AS can fall down if there are cross-database calls.
To answer your question, yes, there is a way to setup your database security such that the Database User only has EXECUTE permissions on the stored procedure and no direct permissions to the underlying objects that procedure accesses. As for how to achieve that, it will depend on what the proc does. If you can post the code we can better assist. It could be as simple as changing TRUNCATE to DELETE if all objects are in the same database and owned by the same Database Principal, in which case Ownership Chaining will cover the necessary scenarios. If changing code is not an option and WITH EXECUTE AS will not work due to corss-database calls then you can look into impersonation or certificate signing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 18, 2013 at 8:34 am
Hi All
Thanks so much for all the suggestions. I really appreciate it.
I asked the Global developer and they said their procedures cannot be changed. And they also said that we should not limit the rights of the Developers/App Support Team to the databases. In my office, the developer and app support are the same team and I don't think it is correct to grant everyone db_owner privileges.
Do we really give developers the rights to execute stored procedures on the Test and the Production Systems. (In my office, Change Management is required on the Test and Production Systems) and granting such privileges will mean no one needs to raise an CR in the first place.
April 18, 2013 at 8:39 am
Dree Vasquez (4/18/2013)
I don't think it is correct to grant everyone db_owner privileges.
No, absolutely not. Principal of minimal privileged. Users don't need to be able to drop the database, delete all tables, change settings, backup, restore and hence they should not have enough permissions to do that.
Regarding your permissions problems, check the ownership of the tables and the procedures (not schema, owner), see if they are different. If they are, then the ownership chaining mentioned earlier won't work and hence the users will be having permission denied errors.
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, 2013 at 9:40 am
Dree Vasquez (4/18/2013)
Hi AllThanks so much for all the suggestions. I really appreciate it.
I asked the Global developer and they said their procedures cannot be changed. And they also said that we should not limit the rights of the Developers/App Support Team to the databases. In my office, the developer and app support are the same team and I don't think it is correct to grant everyone db_owner privileges.
Do we really give developers the rights to execute stored procedures on the Test and the Production Systems. (In my office, Change Management is required on the Test and Production Systems) and granting such privileges will mean no one needs to raise an CR in the first place.
If you're a member of sysadmin, then you can grant or deny whatever privillages you know are really required. You can even edit and recompile the stored procedures too. Don't let some 3rd party tell you how to manage your database.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 22, 2013 at 5:59 am
Eric M Russell (4/18/2013)
Dree Vasquez (4/18/2013)
Hi AllThanks so much for all the suggestions. I really appreciate it.
I asked the Global developer and they said their procedures cannot be changed. And they also said that we should not limit the rights of the Developers/App Support Team to the databases. In my office, the developer and app support are the same team and I don't think it is correct to grant everyone db_owner privileges.
Do we really give developers the rights to execute stored procedures on the Test and the Production Systems. (In my office, Change Management is required on the Test and Production Systems) and granting such privileges will mean no one needs to raise an CR in the first place.
If you're a member of sysadmin, then you can grant or deny whatever privillages you know are really required. You can even edit and recompile the stored procedures too. Don't let some 3rd party tell you how to manage your database.
As a counter to that you should remember that the code whether stored procedures or otherwise should have been tested and signed off before being put LIVE. This testing may and change control may be governed by statutary requirements and so altering it without a full retest may have legal implications. As such you should not be changing the stored procs but instead requiring the developers to do so.
On the other hand it is reasonable to require the developers to produce the code such that it will work correctly on a server with much more restrictive permissions.
As a developer I would expect very liberal rights on the develpment server - definitely DBO and probably SysAdmin. I would expect TEST to be more restricted - no SYSADMIN, maybe DBO but probably only DBREADER, DBWriter and appropriate EXECUTE permissions and minimal rights on the LIVE box.
April 22, 2013 at 6:10 am
crmitchell (4/22/2013)
Eric M Russell (4/18/2013)
Dree Vasquez (4/18/2013)
Hi AllThanks so much for all the suggestions. I really appreciate it.
I asked the Global developer and they said their procedures cannot be changed. And they also said that we should not limit the rights of the Developers/App Support Team to the databases. In my office, the developer and app support are the same team and I don't think it is correct to grant everyone db_owner privileges.
Do we really give developers the rights to execute stored procedures on the Test and the Production Systems. (In my office, Change Management is required on the Test and Production Systems) and granting such privileges will mean no one needs to raise an CR in the first place.
If you're a member of sysadmin, then you can grant or deny whatever privillages you know are really required. You can even edit and recompile the stored procedures too. Don't let some 3rd party tell you how to manage your database.
As a counter to that you should remember that the code whether stored procedures or otherwise should have been tested and signed off before being put LIVE. This testing may and change control may be governed by statutary requirements and so altering it without a full retest may have legal implications. As such you should not be changing the stored procs but instead requiring the developers to do so.
It depends on the shop. Some DBAs have carte blanche to change code when it makes sense, i.e. when the changes relate to database security or performance. Other times those tasks fall to a developer.
On the other hand it is reasonable to require the developers to produce the code such that it will work correctly on a server with much more restrictive permissions.
As a developer I would expect very liberal rights on the develpment server - definitely DBO and probably SysAdmin. I would expect TEST to be more restricted - no SYSADMIN, maybe DBO but probably only DBREADER, DBWriter and appropriate EXECUTE permissions and minimal rights on the LIVE box.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 22, 2013 at 6:33 am
opc.three (4/22/2013)
crmitchell (4/22/2013)
Eric M Russell (4/18/2013)
Dree Vasquez (4/18/2013)
Hi AllIf you're a member of sysadmin, then you can grant or deny whatever privillages you know are really required. You can even edit and recompile the stored procedures too. Don't let some 3rd party tell you how to manage your database.
As a counter to that you should remember that the code whether stored procedures or otherwise should have been tested and signed off before being put LIVE. This testing may and change control may be governed by statutary requirements and so altering it without a full retest may have legal implications. As such you should not be changing the stored procs but instead requiring the developers to do so.
It depends on the shop. Some DBAs have carte blanche to change code when it makes sense, i.e. when the changes relate to database security or performance. Other times those tasks fall to a developer.
On the other hand it is reasonable to require the developers to produce the code such that it will work correctly on a server with much more restrictive permissions.
As a developer I would expect very liberal rights on the develpment server - definitely DBO and probably SysAdmin. I would expect TEST to be more restricted - no SYSADMIN, maybe DBO but probably only DBREADER, DBWriter and appropriate EXECUTE permissions and minimal rights on the LIVE box.
Well I'm a developer but as I said I would expect that on DEV and would expect to need to justify it in TEST - it may be needed when promoting to TEST but normally I would not normally expect it after that stage is completed. Older code may still require it but it should be the exception rather than the rule. It should not be expected for LIVE.
The only thing I would want on LIVE over and above the basic access everyone would have would be the ability to view but not alter server settings and the database objects to aid with diagnosing problems. In some cases the sensitivity of LIVE data may mean that even that level of access would not be appropriate.
Ideally for LIVE the code should be set up to run with execute permissions on specific stored procs only and no general DB access.
April 22, 2013 at 7:41 am
crmitchell (4/22/2013)
opc.three (4/22/2013)
crmitchell (4/22/2013)
Eric M Russell (4/18/2013)
Dree Vasquez (4/18/2013)
Hi AllIf you're a member of sysadmin, then you can grant or deny whatever privillages you know are really required. You can even edit and recompile the stored procedures too. Don't let some 3rd party tell you how to manage your database.
As a counter to that you should remember that the code whether stored procedures or otherwise should have been tested and signed off before being put LIVE. This testing may and change control may be governed by statutary requirements and so altering it without a full retest may have legal implications. As such you should not be changing the stored procs but instead requiring the developers to do so.
It depends on the shop. Some DBAs have carte blanche to change code when it makes sense, i.e. when the changes relate to database security or performance. Other times those tasks fall to a developer.
On the other hand it is reasonable to require the developers to produce the code such that it will work correctly on a server with much more restrictive permissions.
As a developer I would expect very liberal rights on the develpment server - definitely DBO and probably SysAdmin. I would expect TEST to be more restricted - no SYSADMIN, maybe DBO but probably only DBREADER, DBWriter and appropriate EXECUTE permissions and minimal rights on the LIVE box.
Well I'm a developer but as I said I would expect that on DEV and would expect to need to justify it in TEST - it may be needed when promoting to TEST but normally I would not normally expect it after that stage is completed. Older code may still require it but it should be the exception rather than the rule. It should not be expected for LIVE.
The only thing I would want on LIVE over and above the basic access everyone would have would be the ability to view but not alter server settings and the database objects to aid with diagnosing problems. In some cases the sensitivity of LIVE data may mean that even that level of access would not be appropriate.
Ideally for LIVE the code should be set up to run with execute permissions on specific stored procs only and no general DB access.
Judging by your response I thought I would point out that You gave them dbo?[/url] was a link to a blog post, not really a question or comment directed at you. I consider myself a Developer first, and a DBA second. In some shops your expectations will not be met. You may be granted db_owner membership in DEV, read-only and VIEW DEFINITION in TEST, and nothing in LIVE, because sometimes you will not be allowed to see transactional data in LIVE and non-LIVE environments will be scrubbed of PII. At the end of the day if the code is untested in DEV it should not even make it to TEST, let alone LIVE. By the time it makes it to LIVE developers should not need to worry. Of course this is best case scenario, but that's how it goes in some shops. In some extreme cases access to LIVE can be granted temporarily to troubleshoot an issue that cannot be recreated in DEV or TEST, but that would require an approval for access to PII. You can see how this could motivate developers to produce fully-tested code that can be deployed in a repeatable way such that it runs in LIVE the same way it runs in DEV, a nice ancillary benefit to having a locked down LIVE environment.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply