Restricting rights and deny of access based on access channel
-
I am looking for a way to restrict access to our SQL Servers based on what channel the user is using to access. Spesifically I need that a user logging in through Excel, Access, Microsoft Query or isql should only be able to read data no matter what privileges the user has on the server. Another needed function is that some users logging in from some spesific channels should have their sessions aboorted. We are able to do this on Sybase systems, but do not know of a way to accomplish this on SQL Server. Does anybody know the best way to accomplish this kind of functionality?
Regs,
Kjetil Dahl
-
I don't think that there is a way to do this in SQL Server. Look into application roles, though. If you can create an application role, then set up those applications that you mentioned above to use that role, you could then go and remove the specific users direct access to the databases.
-
I'm not sure these articles will help you; however, they may give you some ideas..
http://support.microsoft.com/default.aspx?scid=kb;en-us;288366
http://support.microsoft.com/default.aspx?scid=kb;en-us;257757
-
I also agree that this is not possible whether you use SQL Security or Integrated Security. Once your login is authenticated to the SQL Server the only thing prohibiting/governing your spid and it's ability to process is permissions/roles.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
It is only possible if you restrict access to your data through views or stored procedures.
With stored procedures you can begin by asking who the user is. The system function USER_ID() will do it. Then the user id should be match the uid column in the sysprocesses system table in master, which will give you the program name and the host name (the channel) the user is using.
Some further Transact-SQL with IF ELSE logic will do it.
select top 1 hostname, Program_name from sysprocesses
Where uid=USER_ID()
and ltrim(hostname)<>''
With Views, you add the above statement to the view and cross join with your query, giving a further constant condition on the program_name as it suits you. Mark that you have to use a different view for evry channel.
HABIB.
-
Thanks for the constructive feedback!
So far it looks like the application roles will be the way to go to achieve something like the solution we have in place for Sybase today, even though it would mean that the applications would have to know a set of role names and passwords to achieve this.
-
I agree. Roles are the best way to go. You can further look into using an application role.
define an application role for every application that will be accessing the database. The applicaiton role does not carry any users, only a password and a set of permissions on objects.
You activate the application role from your application. After establishing connection to the database, you issue the statement
sp_setapprole @role, @password
When the application role is active, every other permission attributed to the user outside this role is void.
I think this is the less costly way to go while using role based security.
HABIB.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply