September 25, 2015 at 5:42 am
How can I allow a user to run a stored procedure but deny them the ability to see it in SSMS?
I don't mean 'view def' permissions, I mean the actual proc.
I read about encryption which mask the contents but they can still see the proc, any other ways I can accomplish this?
Thanks in advance.
September 25, 2015 at 6:06 am
smitty-1088185 (9/25/2015)
How can I allow a user to run a stored procedure but deny them the ability to see it in SSMS?I don't mean 'view def' permissions, I mean the actual proc.
I read about encryption which mask the contents but they can still see the proc, any other ways I can accomplish this?
Thanks in advance.
end users should not be using SSMS; Dev's sure, but a plain old end user? so what if a developer knows an object exists, he can't use it without permissions.
Anyway, you start with minimum permissions. don't grant anything to the users at the beginning.
if you create a user, and then the only permission is
GRANT EXECUTE ON MyProc TO ClarkKent
he can see the existence of the procedure in sys.objects. he can see what parameters are allowed, but he cannot see the text of it (ie with sp_helptext MyProc or select definition from sys.sql_modules where object_id = object_id('myProc')
granting roles db_owner or db_ddladmin allows the ability to see the text of it. take that permission away if that's your issue.
if you are asking how how to both grant someone access to an object but keep it a secret, by not let them see it exists, that's not possible.
you could create a wrapper procedure, which calls the real procedure via execute as owner, and grant the user access to the wrapper. then they can see the wrapper proc but not the "real" workhorse proc.
Lowell
September 25, 2015 at 7:28 am
As Lowell noted, the procs will be in sys.objects. They'll see them in SSMS. AFAIK, you can't hide that.
September 25, 2015 at 10:52 am
Thanks for the replies! I thought it wasn't possible but I wanted to ask just in case.
I will probably use the wrapper techinque.
We have a table with secure and unsecure data on the same column so we are planning on using
a lookup table to prevent vendors from seeing senitive data. We didn't want the vendor to see
the proc existed at all.
SQL 2016 has row level security but we are using 2012.
September 25, 2015 at 4:27 pm
USE tempdb
GO
-- create a test proc in tempdb
CREATE PROCEDURE usptest
AS
SELECT * FROM #temp
GO
-- following code will not remove proc from SSMS
-- but does prevent it showing in the "obvious" list of programmability>stored procedures folder
-- but can be readily found in programmability>SYSTEM stored procedures folder
-- and as other have said is not hidden when using
-- SELECT * FROM dbo.sysobjects WHERE (type = 'P') or similar
-- it might just obfuscate enough for you....but once the secret is out !
EXEC sp_addextendedproperty
@name = N'microsoft_database_tools_support',
@value = '<Hide? , sysname, 1>',
@level0type ='schema',
@level0name ='dbo',
@level1type = 'procedure',
@level1name = 'usptest'
--to revert
EXEC sp_dropextendedproperty
@name = N'microsoft_database_tools_support',
@level0type ='schema',
@level0name ='dbo',
@level1type = 'procedure',
@level1name = 'usptest'
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 26, 2015 at 7:37 am
J Livingston SQL (9/25/2015)
USE tempdbGO
-- create a test proc in tempdb
CREATE PROCEDURE usptest
AS
SELECT * FROM #temp
GO
-- following code will not remove proc from SSMS
-- but does prevent it showing in the "obvious" list of programmability>stored procedures folder
-- but can be readily found in programmability>SYSTEM stored procedures folder
-- and as other have said is not hidden when using
-- SELECT * FROM dbo.sysobjects WHERE (type = 'P') or similar
-- it might just obfuscate enough for you....but once the secret is out !
EXEC sp_addextendedproperty
@name = N'microsoft_database_tools_support',
@value = '<Hide? , sysname, 1>',
@level0type ='schema',
@level0name ='dbo',
@level1type = 'procedure',
@level1name = 'usptest'
--to revert
EXEC sp_dropextendedproperty
@name = N'microsoft_database_tools_support',
@level0type ='schema',
@level0name ='dbo',
@level1type = 'procedure',
@level1name = 'usptest'
Now that is just plain cool. I learned something new today, so thank you.
September 26, 2015 at 2:06 pm
This is one feature of System.Persistence, right out of the box.
September 27, 2015 at 11:33 am
SSCrazy that is absolutley perfect!!! Exactly what I needed. Thank you so much.
September 27, 2015 at 1:05 pm
smitty-1088185 (9/27/2015)
SSCrazy that is absolutley perfect!!! Exactly what I needed. Thank you so much.
I strongly suggest that you fully research this and understand what is and is not possible for this code, now and in the future...you (or your successors) need to be able to manage this within your business.
here is one link that you should read
https://msdn.microsoft.com/en-GB/library/ms180047.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 27, 2015 at 1:31 pm
This line of thinking is good. Table structure IS a security risk.
It is good to see some thinking back towards solid n-tier design principles:
Tables
Stored Procedures
Data Access
Business Logical Classes
Application
September 28, 2015 at 1:10 am
Create SQL-CLR procedure.
Encrypt your .net assembly.
Your procedure is sealed. No one can view contents of it.
September 28, 2015 at 5:18 am
savani.mahesh (9/28/2015)
Create SQL-CLR procedure.Encrypt your .net assembly.
Your procedure is sealed. No one can view contents of it.
actually, the dll is uploaded into the database, and can be extracted to disk, and then then decompiled.
A lot of the times, if you deploy from Visual Studio, the .cs or .vb classes are uploaded as well. if you didn't obfuscate the source code prior to compile time, So for a determined developer, it's going to be fairly easy to create your own version of the dll, read the source, etc.
that's an artifact of .NET being compiled to an intermediate level, so multiple programming languages can all write /.net code.
So at a certain level, it's just how difficult you want to make it for someone to extract and see your code.
Lowell
September 29, 2015 at 9:39 am
SQLCLR doesn't hide the procedure from someone, though it does make the code hard to get to.
However, what is the purpose here? At some point, much of your data manipulation is available from the results, or from Profiler. Perhaps even from the execution plan.
September 29, 2015 at 10:17 am
Even if a user has execute permission on a stored procedure, I'm thinking that if the user's account can't at least see the meta-data signature for the procedure name and parameter list, then that could interfere with the ability of the client tools or applications to bind to the procedure or call it. If the user's login account can't see it, then neither can the connection used by ADO.NET, SSRS, or whatever. My concern would be that attempting to hide the procedures could break something in the application.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 5, 2015 at 10:22 am
If you think that hiding objects will make your system more secure then you are wrong. At best it will give you a warm fuzzy feeling of security, but anyone with the right skills can find what they want. They can even use your attempts at 'security' to target their attack.
If you want to implement row-level security then Google can find you methods of doing this that can work on SQL 2000 onwards. I had row-level security working back in 1995 on a project I designed. Some of the roll-your-own techniques may not perform as well as the built-in stuff, but you choose your SQL version and make the most of it.
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply