August 19, 2015 at 3:44 am
Good morning,
We have a generic sql login "prduser". Applications use this login. We want the login NOT to have ALTER PROCEDURE and DROP PROCEDURE permissions only on the stored procedures(there are thousands of them). Please help on how to solve this.
August 19, 2015 at 6:23 am
i'd be interested to know why, of course; is it because the username and password are passed around to developers? are you having trouble with people changing procs? are you using anything with change control for your procs?
as far as i know, the only way to do this is with a Database Level Trigger that checks DDL actions, so you can rollback a a DDL command that was issued if it meets certain criteria(ie the specific user and the ALTER or DROP commands)
i posted a full fledged example where someone wanted a user to ONLY have the ability to create and alter views(but not table/proc/function).
you could use that as a model, if you really, really needed to prevent the activity.
http://www.sqlservercentral.com/Forums/FindPost1241331.aspx
Lowell
August 19, 2015 at 9:13 am
Dear Lowell sir,
The Applications use this login in their connection string and perform DML and other operations. Some of the business logic is in the stored procedures and hence the application code uses the stored procedures. However, the requirement is that the login must NOT alter procedure and must NOT drop procedure.It is allowed to do evrything else.
August 19, 2015 at 11:53 pm
The login had db_owner permission to the database. Will the trigger override the db_owner permission previlige?
August 20, 2015 at 3:42 am
Hi!
I maybe misunderstands your question, but you want your app login (prduser) to only have execute rights to procedures and functions?
You don’t add rights to a login object, you should use a role, which could be reused.
You could use a script that’s retrieves the objects you want to set some grant for the role, like:
declare @SchemaName as nvarchar(20) = 'dbo';
declare @MyAppLogin as nvarchar(128) = 'prduser';
declare @MyAppUser as nvarchar(128) = 'MyAppUser';
declare @MyAppRole as nvarchar(128) = 'MyAppRole';
declare @StoredProcedures as table (name varchar(180));
insert into @StoredProcedures select name from sys.sysobjects where type = 'P' and left(name, 3) not in ('sp_') and left(name, 5) not in ('Tool_') order by name asc;
declare @Functions as table (name varchar(180));
insert into @Functions select name from sys.sysobjects where type in ('FN', 'TF') and left(name, 3) not in ('fn_') order by name asc;
declare @TableTypes as table (name varchar(180));
insert into @TableTypes select name from sys.table_types order by name asc;
declare @statement nvarchar(180);
declare @statements as table (name varchar(180));
if not exists (select * from sys.server_principals where name = @MyAppUser)
begin
select @statement = N'CREATE USER [' + @MyAppUser + N'] ' + N'FOR LOGIN [' + @MyAppLogin + N']'
--print @statement
execute dbo.sp_executesql @statement;
end
if not exists (select * from sys.database_principals where name = @MyAppRole and type = 'A')
begin
select @statement = N'CREATE ROLE [' + @MyAppRole + N'] AUTHORIZATION [' + @MyAppUser + N']'
--print @statement
execute dbo.sp_executesql @statement;
end
execute sp_addrolemember @MyAppRole, @MyAppUser;
insert into @statements select N'GRANT EXECUTE ON OBJECT::[' + name + N'] TO [' + @MyAppRole + N']' from @StoredProcedures
insert into @statements select N'GRANT EXECUTE ON TYPE::[' + name + N'] TO [' + @MyAppRole + N']' from @TableTypes
while ((select count(*) from @statements) > 0)
begin
set @statement = (select top 1 name from @statements);
-- print @statement
execute dbo.sp_executesql @statement;
delete from @statements where name = @statement;
end
August 24, 2015 at 5:08 am
Dear Jonas
We want our app login(prduser) to have ONLY execute permission to procedures.It must NOT be able to ALTER or DROP stored procedure. It is allowed to do everything else. The login is a member of db_owner role.
August 24, 2015 at 5:43 am
coolchaitu (8/24/2015)
Dear JonasWe want our app login(prduser) to have ONLY execute permission to procedures.It must NOT be able to ALTER or DROP stored procedure. It is allowed to do everything else. The login is a member of db_owner role.
there's your answer then.
take away db owner, and grant execute, db_datareader and db_datawriter.
that role should not be creating objects or doing backups anyway, so take awy the overreaching rights.
Lowell
August 24, 2015 at 5:46 am
Dear Lowell,
Thats the catch. The login cannot be removed from db_owner. They have some reasons for it.
August 24, 2015 at 6:01 am
you'll probably need to investigate a bit more ; could their reasons for it be simply institutional inertial(we always needed db_owner just in case)
if an application is using this login, why would it issue ALTER PROC or DROP PROC commands? if it's due to developer misuse, you can fix that.
you can do the soft way, and add a login trigger that captures hostname +appname that uses that login, and ithe app name contains SQL server management studio, and the hostname to a developer machine, you can approach them individually and tell them don't connect with the app login.
if you create a database trigger, which would work, they developer would potentially just be slowed down...they could disable it, do their work, and re-enable your trigger.
i would consider declaring an emergency security fix, and change the password, and change it in the application as well.
Lowell
August 24, 2015 at 6:29 am
I'm interested in these reasons the application needs the db_owner database role.
I'm with Lowell on this one: revoke db_owner and grant execute, db_datareader and db_datawriter.
The login should have the least permissions it needs to do the work it needs to do. My guess is that creating new database users and granting them permissions is not among them. Also, revoking the permissions of others, dropping database users, disabling triggers and detaching the database should not be among them.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply