July 25, 2001 at 12:43 am
Hi all
Im trying to deny the "alter table" command for a schema user. Basically
I dont use DBO, the applications in the database get their own userid and create object using it. Ive created the following role to deny schema access but can add to it the "alter table" command to stop developers from adding new columns as they feel like.
Here is the role:
EXEC sp_addrole N'deny_schema_dba_access'
deny create table, create view, create procedure, create function, backup database, backup log to deny_schema_dba_access
I get the error when trying to deny alter table access:
Server: Msg 165, Level 16, State 2, Line 1
Privilege ALTER TABLE may not be granted or revoked.
The User does NOT have the db_owner role allocated to them.
Any ideas?
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 25, 2001 at 10:47 pm
Hi all
use mydb
go
exec sp_droprolemember 'db_ddladmin', 'myuser'
go
Does the trick.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 26, 2001 at 5:03 am
Well Well.. more testing done.
Basically the
exec sp_droprolemember 'db_ddladmin', 'trs'
STILL allows the user to alter the name of the columns in a table, but they can alter the data types and other physical structures of the table. Altering the column name (i believe) is just as bad as changing its structure!
The user can also still create indexes unless you explicitly disable it.
So the documentation:
db_ddladmin - Adds, modifies, or drops objects in the database (runs all DDLs).
is not any sort of indication of what it actually does and does not protect.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 26, 2001 at 5:04 am
"STILL allows the user to alter the name of the columns in a table, but they can alter the data types and other physical structures of the table. Altering the column name (i believe) is just as bad as changing its structure!"
should be:
"STILL allows the user to alter the name of the columns in a table, but they CAN NOT alter the data types and other physical structures of the table. Altering the column name (i believe) is just as bad as changing its structure!"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 26, 2001 at 7:52 am
BOL says this about alter table:
ALTER TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
I'd say the best bet is to not let them own the objects. Maybe you could change the owner after they are created/satisfied with them.
Andy
July 26, 2001 at 9:05 am
I agree with Andy. I think the Create Table and Alter Table are pretty tightly linked. I think if you have Create Table, then Alter table is also there.
I'd go the other way. I'd either let the users create/alter table and deal with it administratively (terminate abusers) or implement a system to creates the tables for them. Submit a Create Table statement adn store it in a table. Then let SQLAgent or someo other process run the statement as dbo and create the table. Don't allow alters.
Steve Jones
July 26, 2001 at 10:34 am
Our change control allows developers to build and modify tables in development (if they have the expertise), but once we are ready to move to QA or production, the database is scripted and only a DBA builds the QA or production databases. This gives developers the flexibility to work with the tables in designing their apps, but ensures that for testing or production purposes, everything is locked down.
It should also be pointed out that this does cause some additional strain on the DBA because with developers having rights to change objects, it's harder to ensure that the developers are following best practices.
What can be done is give developers rights to alter and create tables in development, however, no developer has a dbo role. So as they are building their app they can test with their own created tables. At the time for a build, any tables and other objects ready to be made part of that build would be converted over to dbo ownership after a DBA review. Then the DBAs only have to worry about verifying the tables declared ready for a build.
K. Brian Kelley
K. Brian Kelley
@kbriankelley
July 26, 2001 at 11:47 pm
Hi all
I am the same in that no one geting dbowner (dbo) access. Even in dev, i try and run a very strict shop with NO application schema changes unless it goes through me via change control doco that signed by the business analyst and dev ops guide reviewed and updated accordingly. I take the approach that the schema is one of the key backbones of the application and changes can have significant impact to developers and other systems. Its not as strict as it all sounds and changes are implemented very quickly, but even so, change management is a must even for somewhat small apps.
For dev purposes, I create a user logins for the database, and via roles, give them CRUD access to the "app" schema. They can do whatever the like from there. Once ready, change control is completed and I will move the changes (whatever they may be) into the app schema.
Anyhow, just one of the many ways to manage change and lower the impact of change.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply