January 12, 2005 at 8:37 am
I'm working on some bank application, where in client not instrested even the DBA'S (with Sysadmin) to view some of his table data. i tried with different things but failed in front of SA (Sysadmin) >
Can any one had answer to this - (other then encrypting data) ??? Urgent.
thanks in advance,
venkat
January 12, 2005 at 12:33 pm
Any and every SQL Login that has been granted SysAdmin rights (through membership in the SysAdmin server role) has complete, total, and absolute rights, permissions, and access to everything within that instance of SQL Server. I'd be fascinated to hear how this might be blocked.
What your bank needs to do is lock down who has SysAdmin access to their servers. It sounds like they want no one no when no how to have SysAdmin access to the database--a debatable move at best, IF it's even possible. (As soon as something breaks--or even needs to be changed--that requires SysAdmin access, you're dead.)
What they may have to settle for is to identify people they trust--the CEO, the VPs, whoever--and empower them with SysAdmin rights. Then, either they--the trusted people--do all the work, or they delegate the rights on an "only when and only for as long as necessary" basis. Presumably they stand behind them to make sure they're not reading data while optimizing a query or configuring a new backup.
Mild sarcasm aside, there comes a point where People Who Make Decisions have to delegate responsibility and authority--and doing that wisely and competently is why they earn the big bucks. Or so's my take on things.
Philip
January 13, 2005 at 12:44 am
Yeah wht you said is 100% true.... when ur boss ask some thing to do, we need to find the way...
Let me put my question (more clearly) in this way:
I dont want to block Sysadmin user as with out tht we can't do many admin tasks like replication, profiling etc... So other then SA, we are in a plan to create other user with Sysadmin rights, he will be whole responsible for any DBA tasks. The thing is we want to just retrict him to access only some table data (technical words: DENY SELECT ON TABLES).
Can we DENY SELECT PERMISSION TO SYSADMIN (Role) User. ???
Thanks, - venkat
January 13, 2005 at 2:41 am
Hi,
If you don't want to pursue the separate server/instance approach, then the only other alternative I know of is to
a) create a custom DB role in the master database [see http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritythedb_executorrole.asp]
b) assign all the necessary permissions to the required database
Hope that helps,
Mauro
January 13, 2005 at 2:42 am
Please refrain from posting the same question multiple times! It's hard to follow for everyone and unnecessary, since most guys here follow all the fora.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 13, 2005 at 2:49 am
Hi Frank,
sorry but I don't understand your comment. Does it refer to the entire thread or just to my reply? If so, could you please specify the link of the other thread where the problem is dealt with?
Thanks
January 13, 2005 at 2:53 am
Mauro,
my post was aimed at the OP. But since the forum software does not support tree view that is a little bit difficult to visualize. Here's the other thread http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=4&messageid=155074
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 13, 2005 at 2:54 am
Though I admit, this question clearly belongs more to security than suggestions.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 13, 2005 at 3:27 am
Encrypt the data at the column level.
See http://www.activecrypt.com for details on how to do column level encrption.
January 13, 2005 at 3:35 am
I suspect that's not what he wanted to hear.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 13, 2005 at 4:22 am
Mauro,
Sorry, if its a user defined DB role, again we cannot perform all admin tasks.
Can any one - list all (Server role) combination = Sysadmin server role
i mean (serveradmin + setup admin + soooooo ) = Sysadim role.
thanks - venkat.
January 13, 2005 at 6:58 am
As a few people have said, in SQL Server it is not possible to restrict what anyone with Sysadmin authority can do. SQL Server simply does not check if a Sysadmin has or does not have rights to do something - it just allows access.
If you want to prevent someone with Sysadmin looking at your data, then you have to use a different DBMS (not SQL Server!!) that allows Sysadmin rights to be limited.
Alternatively in SQL Server you can prevent a Sysadmin from making any sense of your data by encrypting it. This does not stop a Sysadmin adding, changing or deleting the data, it just means they have no idea what the data represents.
Those are the choices. Do not expect SQL2005 to change them.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 13, 2005 at 7:22 am
Actually it's not so much sysadmin authority. The real level this hits home is at dbo. If a user is dbo within a database, no security applies. Since members of the sysadmin role map in as dbo, you get the same result. That seems like a nit-picky distinction but it's a crucial one. That means if the database is owned by someone NOT in the sysadmin role, they too avoid security. Hence the reason it's necessary to encrypt at the application level.
K. Brian Kelley
@kbriankelley
January 13, 2005 at 7:53 am
in addition to the other replies.
You can also try to disencourage native db-access by using your own naming-conventions.
I have one db that has tables named A0001,.. with columns C0001, C0002,...
(select C0452, C0825 from A0125 where C0864 = ...)
I can assure you that these type of tables/views are no fun at all to access natively.
Schema are being managed using ERD-tools.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 13, 2005 at 8:03 am
Now that reminds me of http://mindprod.com/unmain.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply