May 22, 2017 at 4:44 am
Hi all,
My first post here, so I would like to say hello everyone and thank for your support.
I've a question in which I'm working for days.
We've a new database with encrypted tables, and only 1 user should access to them. The tables will be visible through a web. They shouldn't be visible through SQL (only for sysadmin).
But I realized that if I'm in SSMS in another database and create a sp "with execute as" - user to use on the web, I'm able to query the tables. 🙁
I cannot set the other database as trustworthy off because other databases and server access to them.
I cannot do a server trigger to cut connections because it does not detect the connection to the database with the encrypted tables.
How can I deny the permission to "execute as" (we user or owner) to see the encripted tables?
Thanks in advance!
Regards
May 22, 2017 at 5:22 am
is it not true that the only person that can do a procedure featuring EXECUTE AS is going to be a sysadmin anyway?
a regular user cannot create a procedure featuring execute as for a user that has higher privileges that it's own....so if other users are not sysadmin, or dbo in the database in question,you should be fine i think.
it's easy to test.
EXECUTE AS LOGIN-'mydomain\SomeNonPrivilegedUser'
CREATE PROCEDURE...
WITH EXECUTE AS....
so you don't need to worry about whether ran execute as clase was used, just review typical security,a nd just make sure that no one, other than the DBA, has dbo privileges
Lowell
May 22, 2017 at 5:28 am
Lowell - Monday, May 22, 2017 5:22 AMis it not true that the only person that can do a procedure featuring EXECUTE AS is going to be a sysadmin anyway?
a regular user cannot create a procedure featuring execute as for a user that has higher privileges that it's own....so if other users are not sysadmin, or dbo in the database in question,you should be fine i think.
it's easy to test.
EXECUTE AS LOGIN-'mydomain\SomeNonPrivledgedUser'
CREATE PROCEDURE...
WITH EXECUTE AS....
Hi,
Thanks but in this company there are several users working with SQL Server and all of them need to be dbo (not sysadmin), so they could create a sp with execute as. They are dbo on another databases, not the one that contains the encripted tables.
I need to avoid that thay can view the encripted tables from the other databases...
Regards
May 22, 2017 at 8:05 am
I found a solution related to database trigger.
I created a trigger to avoid sp creation with execute as.
Regards
May 22, 2017 at 8:47 am
did you hard code it to a string 'EXECUTE AS', so 'EXECUTE{CrLf}
AS' would still be allowed?
Lowell
May 22, 2017 at 8:53 am
I set it as '%EXECUTE%AS%' so both ways are not allowed 🙂
Thanks you
May 22, 2017 at 9:25 am
awesome. that's the way to code defensively.
Lowell
May 22, 2017 at 9:35 am
Although I hate to put a rain on your parade, do you have anything from stopping a dbo doing:DROP TRIGGER [YourTrigger] ON DATABASE;
GO
EXECUTE AS [YourLogin];
GO
CREATE PROC....
....
REVERT;
Or disabling said trigger.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 22, 2017 at 9:40 am
Hi,
Not, but at least we've an audit to log objects modifications so we will know who and when this dbo did it.
But I'm opened to better solutions.
Thanks
May 22, 2017 at 9:43 am
aar - Monday, May 22, 2017 8:53 AMI set it as '%EXECUTE%AS%' so both ways are not allowed 🙂Thanks you
Mmmmm... What happens if someone tries to create a stored procedure with the line EXECUTE dbo.spGetAssets?
John
May 22, 2017 at 9:48 am
As we're always in contacts, they'll let the DBA knows and we'll find a temporary solution but this about the encryption is a priority
May 22, 2017 at 10:02 am
seems like the right thing to do is simply replace dbo permissions with something that is almost, but not quite the same. that would address the issue, i would think.
if they try to do anything more than that, like add users, use WITH EXECUTE, drop the database, they are prevented doing so via direct, built in permissions, instead of a tool they can disable and re-enable after doing bad things.CREATE ROLE [AlmostOwners]
--create stuff
EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'
--select stuff
EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'
--insert/update/delete stuff
EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'
--can the users EXECUTE procedures
GRANT EXECUTE TO [AlmostOwners]
--allow the users to see view proc and function definitions
Grant View Definition To [AlmostOwners]
--see sys partitions and the like
GRANT VIEW DATABASE STATE TO [AlmostOwners]
Lowell
May 22, 2017 at 2:43 pm
revoke impersonate on user::privileged_user to group_of_unprivileged_users;
May 23, 2017 at 1:54 am
Joe Torre - Monday, May 22, 2017 2:43 PM
revoke impersonate on user::privileged_user to group_of_unprivileged_users;
The problem here is the users are dbo's. They could just re-enable their permissions.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 23, 2017 at 2:18 am
aar - Monday, May 22, 2017 8:53 AMI set it as '%EXECUTE%AS%' so both ways are not allowed 🙂Thanks you
That's going to give you a lot of false alarms.
I would recommend tweak those users' permissions. See what they *actually* need to do, and then create a custom database role that lets them do that, but not stuff you don't want them to do. If you don't tell them, they probably won't complain, and unless they need to be playing with impersonation, users and such, doing so will keep them out of your encrypted tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply