May 21, 2009 at 7:49 am
I'm running few scans against my sql 2005 database using third party tools. I'm using a user which is not SQL Server user rather it is custom application user which uses a common SQL Login to perform database activities.
This application user should never be given access to update its own password but this user will never update other application users password in the table. So I want to lock just the password column i.e.> just the password cell for this row alone.
whether it is possible to lock a row or a cell in sql server without using a isolation level locks....
Thanks.
May 21, 2009 at 12:08 pm
May 21, 2009 at 1:35 pm
I'm confused. What do you mean by "lock"
Prevent changes?
You can remove update permissions from the table, or the column, for a user/role.
May 21, 2009 at 1:57 pm
If this is a table with login in side of it and not the SQL Logins
i.e mytablelogins then you can create a role which is under database, security, users,
add a new role, select securables and specific objects select, then objects and select tables,
browse to mytablelogins - once it returns, in permissions then do update, hit column permissions and
select the column you want to allow update to.
If it is sql logins - you have to use certificates and create a stored procedure that the user can call and only have a filter inside of the stored procedure before it calls alter password to prevent certain sql logins being changed. This prevents you giving sa rights to the user...
--Here how to do sql logins passwords with certificates
SQL Server 2005: procedure signing demo
The ability to sign procedures is my favorite cryptographic feature in SQL Server 2005.
If we want to write a procedure that requires permission P, and we want Alice to be able to execute the procedure but we do not want to grant her the permission P, we can sign the procedure with a certificate and:
a) if P is a database level permission, then we can create a certificate in the database, create a user mapped to it, and grant P to that user.
b) if P is a server level permission, then we can create a certificate in the master database, create a login mapped to it, and grant P to that login.
By doing this, because of its signature, the procedure will gain P for the duration of its execution and we can just grant Alice permission to execute the procedure.
If we need to grant both server and database level permissions to a certificate, then we will have to create both a user and a login. The steps are as follows:
1) create the certificate in the database
2) create a user mapped to the certificate
3) grant db-level permissions to the user
4) backup certificate to a file
5) create the certificate in the master database from the file
6) create a login mapped to the certificate
7) grant server-level permissions to login
We can also first create the certificate in the master database and then recreate it in the user database where alice will work. The order in which we create the certificates does not matter, what matters is that the certificates are the same in both master and the user database.
And here is a demo:
-- Goals of this demo:
-- Show how a procedure can be signed by a certificate
-- and how to grant permissions to that certificate
create database demo;
use demo;
-- create a procedure that creates a new principal (login and user)
-- This requires ALTER ANY LOGIN at server level
-- and ALTER ANY USER at database level
create procedure sp_CreatePrincipal
@name varchar(256),
@password varchar(128)
as
declare @sqlcmd varchar(2000);
begin tran;
-- create login
set @sqlcmd = 'create login ' + quotename(@name) + ' with password = ' + quotename(@password, '''');
exec (@sqlcmd);
if @@error 0
begin
rollback tran;
print 'Cannot create login'
return;
end
-- create user
set @sqlcmd = 'create user ' + quotename(@name);
exec (@sqlcmd);
if @@error 0
begin
rollback tran;
print 'Cannot create user'
return;
end
commit tran;
go
-- now use this newly added procedure
-- to create a low privileged principal
sp_CreatePrincipal 'alice', 'Apufe@))%';
-- we'll now want alice to be able to use the procedure and create new principals
-- but without granting her directly the permissions
grant execute on sp_CreatePrincipal to alice;
-- verify that alice cannot create principals
execute as login = 'alice';
sp_CreatePrincipal 'bob', 'Apufe@))%';
revert;
-- create a certificate to sign the procedure
-- first, we'll need to create a database master key
create master key encryption by password = 'Apufe@))%';
create certificate certSignCreatePrincipal with subject = 'for signing procedure sp_CreatePrincipal';
-- sign procedure sp_CreatePrincipal
add signature to sp_CreatePrincipal by certificate certSignCreatePrincipal;
-- now that we signed the procedure, we can drop the private key
alter certificate certSignCreatePrincipal remove private key;
-- backup certificate to file; it will be used later to put the certificate in master
backup certificate certSignCreatePrincipal to file = 'certSignCreatePrincipal.cer';
-- create and map a user to the certificate
create user u_certSignCreatePrincipal from certificate certSignCreatePrincipal;
-- grant ALTER ANY USER to the certificate by granting it to the mapped user
grant alter any user to u_certSignCreatePrincipal;
-- create the same certificate in master now
use master;
create certificate certSignCreatePrincipal from file = 'certSignCreatePrincipal.cer';
-- create and map a login to the certificate
create login l_certSignCreatePrincipal from certificate certSignCreatePrincipal;
-- grant ALTER ANY LOGIN to the certificate by granting it to the mapped login
grant alter any login to l_certSignCreatePrincipal;
-- we're done!
use demo;
-- check that the certificate in demo matches the one in master
select c.name from sys.certificates c, master.sys.certificates mc where c.thumbprint = mc.thumbprint;
-- verify that alice can now create principals
execute as login = 'alice';
sp_CreatePrincipal 'bob', 'Apufe@))%';
revert;
-- cleanup
drop user u_certSignCreatePrincipal;
drop login l_certSignCreatePrincipal;
drop procedure sp_CreatePrincipal;
drop certificate certSignCreatePrincipal;
drop user alice;
drop login alice;
drop user bob;
drop login bob;
use master;
drop certificate certSignCreatePrincipal;
drop database demo;
-- EOD
---inside of the demo you can do a check if this is abc login get out of procedure.
June 1, 2009 at 7:52 am
I'm confused. What do you mean by "lock"
Prevent changes?
You can remove update permissions from the table, or the column, for a user/role.
We have a tool that will scan through our application & report us of any security loop holes. We have a custom users table created in our data model where each user activities are controlled i.e. like data entry, generate reports & so on. We use a single SQL Login for these activites.
In order to run our scan one of the user in our custom table should be used. When we use this "test" user it updates its own password field & our scan fails.
I want to make "test" user unable to update its own password but it should able to update other users password or other user can update their password.
In order to perform these activities I want to lock the password field or Password cell for "test" user alone.
NOTE: The "test" user uses the common SQL Login. We cannot use any other SQL Login for scan purpose.
June 1, 2009 at 9:32 am
This isn't "locking" a row or cell. a lock is preventing an update from colliding with another, not preventing someone from being able to update. That's a permissions issue.
What you can do is set a trigger to check for the "test" user and then rolling back a password change if that user is updating their own password.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply