Permissions in sql server 2000 (URGENT)
-
Sheilish,
Here's what we do to get around this problem. This is tedious but easy to implement in an existing application. This is necessary because SQL Server does not "ALTER TABLE", "DROP TABLE", etc. (which Oracle does) which you would normally want to revoke from the developers. Most DBA's want to give all privs for SP's/UDF's/data to developers but do not want them to have privs to create/alter/drop dbo-owned db objects (tables, views, triggers, etc.) which DBA's prefer to maintain control over.
All SP's and UDF's in your user database should be owned by a role of your choosing other than dbo. Here, we use role "dco" (short for "database code owner") and all SP's and UDF's are owned by this dco role instead of dbo (e.g., CREATE PROCEDURE dco.<procedurename> instead of CREATE PROCEDURE dbo.<procedurename>
. Create this "dco" role, and then grant this "dco" role to all your developers along with datareader/datawriter (which will grant them read/write access to the data in dbo-owned tables/views/etc) and revoke the roles/privs that you don't want them to have (like db_owner, CREATE TABLE, etc.). By scripting all the SP's and UDF's in an existing db you can drop and recreate them under the new "dco" role ownership. Global search and replace of "EXECUTE dbo." to "EXECUTE dco." will also need to be done in the application. We have been using this security scheme for almost 2 yrs. The only gotcha I came across is that a "dco" owned sp run by a developer will not be able to turn on/off identity_insert on a table (since that requires a lofty DBA priv like ddl admin or db_owner).
Try it!
Jeff
-
Cunning
MVDBA
-
Hi Jeff,
You are really great!!!, Really Cunning. I am teting it , but i am pretty sure it will work. BTW what is your approch for Identity insert issue ?
Thanks,
Sheilesh
-
Hi Sheilish,
Only once did I need to get around the limitation of this "dco" security scheme regarding identity_insert where I required strict accountability of the sequential identity numbers (for 1 table in 1 application).
If you need to do this you can custom code around it as follows. Unfortunately I don't have a generic solution since I only needed this for 1 table. (Don't agree to do this for new app's unless it is absolutely needed. If a search of your app's does not find any manipulation of identity_insert good for you!)
Remove the identity property on the column. Create a unique index on the column. Create a usp (which may be run simultaneously by different users in your app) to handle INSERTs into the table like the s.p. below. (The s.p. below does inserts into table "PermitApplications" which has a unique index on columns APId/PAId, where column PAId was previously used as the identity column.)
CREATE PROCEDURE dbo.usp_insertOnePermitApplication
@PSGId int,
@APId int,
@PTId int,
@AGTId int,
@HeadEmployeeId int,
@VTId int,
@HeadOrgId int,
@SubmittedDate VARCHAR(10) = '?',
@NbrOfPermitsRequested INT = 1,
@NewPAID UniqueId OUTPUT -- Permit app Id of permit application inserted
AS
-- Local Variables
DECLARE
@CurrentMaxPAId UniqueId, -- Last permit app Id for app pd.
@InsertAttemptCount INT, -- Number of permit app insert attempts
@errorcode INT, -- Error Code returned by this proc (0=success; else error)
@SubmittedDateDT DATETIME,
@PKConstraintError INT -- Error with primary key constraint violation
-- Initialize variables
SET @CurrentMaxPAId = 0
SET @errorcode = 0
SET @InsertAttemptCount = 0
SET @PKConstraintError = 2627
-- If submitted date is invalid/missing set it to current date
IF ISDATE(@SubmittedDate)=0 OR RTRIM(@SubmittedDate)='?'
SET @SubmittedDateDT = GETDATE()
ELSE
SET @SubmittedDateDT = CAST(@SubmittedDate AS DATETIME)
----------
TryInsert:
----------
-- Get last permit app Id (PAId) inserted for the application period
-- (APId) to set it for the permit application to insert.
SELECT @CurrentMaxPAId = MAX(PAId)
FROM dbo.PermitApplications
WHERE APId = @APId
-- Quit if unable to select the current max permit Id for the app period and return
-- the error code.
SET @errorcode = @@ERROR
IF (@ErrorCode <> 0)
GOTO Exit_Proc
SET @NewPAId = ISNULL(@CurrentMaxPAId,0) + 1
SET @InsertAttemptCount = @InsertAttemptCount + 1
-- Check that a permit app with this permit app Id has not been inserted
-- since the max permit app Id was retrieved.
IF NOT EXISTS (SELECT * FROM dbo.PermitApplications WHERE APId=@APId
AND <A href="mailto
AId=@NewPAId">PAId=@NewPAId AND PAIdPart=0)BEGIN
INSERT INTO PermitApplications
(
PAId,
PSGId,
APId,
PTId,
AGTId,
HeadEmployeeId,
VTId,
HeadOrgId,
SubmittedDate,
NbrOfPermitsRequested
 
VALUES (
@NewPAId,
@PSGId,
@APId,
@PTId,
@AGTId,
@HeadEmployeeId,
@VTId,
@HeadOrgId,
@SubmittedDateDT,
@NbrOfPermitsRequested
 
SET @errorcode = @@ERROR
IF (@ErrorCode = @PKConstraintError)
-- Limit insert attempts to 100 after primary key constraint violation.
IF @InsertAttemptCount <= 100 GOTO TryInsert
END
ELSE
BEGIN
IF @InsertAttemptCount <= 100 GOTO TryInsert
END
----------
Exit_Proc:
----------
IF @errorcode <> 0 SET @NewPAId=0
RETURN @errorcode
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Good luck,
Jeff
-
Jeff,
Simply Great !!! It will help me a lot.
Thanks again,
Sheilesh
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply