October 31, 2011 at 12:45 pm
I have a client who wishes to give access to a database to one of his clients. This database contains complex stored procedures which represent intellectual property which is to be used but not to be modified or viewed.
Is it straightforward to give the secondary client execute access to everything with a particular userid, while making it impossible for that userid to have the ability to view or modify schema, stored procedures and the like?
Thanks, let me know if I have not explained myself.
Phil
October 31, 2011 at 12:48 pm
The only real way to do this is to host the applications on your servers and give ZERO access to the server itself. Only you application.
Then you need to make then sign a contract written by a lawyer that strictly forbids any access or reverse engineering of that application.
October 31, 2011 at 1:07 pm
PhilM99 (10/31/2011)
I have a client who wishes to give access to a database to one of his clients. This database contains complex stored procedures which represent intellectual property which is to be used but not to be modified or viewed.Is it straightforward to give the secondary client execute access to everything with a particular userid, while making it impossible for that userid to have the ability to view or modify schema, stored procedures and the like?
Thanks, let me know if I have not explained myself.
Phil
You've explained yourself fine, but you can't. Once you give away SA, you give away control. There's nothing you can do to protect those stored procedures from me.
Your options are slim. Self-hosting, as mentioned above. Pure dynamic SQL (which can still be traced and viewed, by the way, so it's not completely hidden) in a secured compile of the code.
Honestly, most of us DBAs out there only care about it to make sure it doesn't thrash around our servers like drunken eels. Otherwise, if we wanted to build the thing, we would have. 😀 I do realize the 1 unscrupulous person/company makes up for the thousand or so legal users if they are belligerant about distribution, however.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 31, 2011 at 1:18 pm
Encryption is also easy to undo if you were hoping to go down that road.
I've researched this in the past and aside from self hosting I couldn't find anything "easy".
October 31, 2011 at 3:09 pm
And in most cases SQLCLR is probably a bad path and even that could be decompiled. albeit with more work..
CEWII
October 31, 2011 at 3:16 pm
Elliott Whitlow (10/31/2011)
And in most cases SQLCLR is probably a bad path and even that could be decompiled. albeit with more work..CEWII
How can you hide the code so that even with a trace you can't see it?
October 31, 2011 at 3:21 pm
I have played (emphasize played) with this in the past but I seem to remember that I included some commands that were never executed in the code like ALTER LOGIN or some that change user information and the trace will NOT reveal the contents. But I admit I can't 100% remember, its been a couple years since I played with it.
CEWII
October 31, 2011 at 3:42 pm
Elliott Whitlow (10/31/2011)
I have played (emphasize played) with this in the past but I seem to remember that I included some commands that were never executed in the code like ALTER LOGIN or some that change user information and the trace will NOT reveal the contents. But I admit I can't 100% remember, its been a couple years since I played with it.CEWII
That info is blocked because it contains a password. The "error" message in the trace is clear about that.
I've never seen content obfuscated there outside sp_prepare and sp_execute. But then again the prepare shows the real command.
It makes it way harder to track if you don't have a test server to play with.
November 1, 2011 at 6:46 am
Thanks to all for the thoughts so far. It seems more complex than I thought. (I was just asking a how do I do this question..)
I wasn't clear that the database in question will indeed be hosted on my own server. But access to it will come from a database on the client's server(linked server) and from applications on the client's web site.
I have programming control over all of it, it's being separated for non-IT reasons. We're not so worried about them hacking or tracing or anything because it's a legal agreement, and both parties are merely trying to protect themselves.
I could write a middle tier app to control access, but it would only be doing something I thought SQL Server could easily do.
My vision was that there was a low-privilege userid I could give to them to use in the web app and in the other database(linked server) while NOT giving them a userid with privileges to alert schema and read and modify stored procs and views.
Seems it is not so??
Thanks
November 1, 2011 at 8:15 am
That could work.
As long as their user don't have view definition permission you should be fine.
November 1, 2011 at 8:25 am
It's a tad more complex. As soon as they have execute rights on the procedure they'll automatically have rights to view the procedure's definition. You can do things like impersonation, wrapper procs, login triggers, application roles, etc. Still not easy
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
November 1, 2011 at 2:55 pm
There was a clue in your answer, Gail so I pursued it. So with respect, can I ask you to revisit your reply?
Here's what I did as an experiment:
Using SSMS,
I connected to a SQL Express instance with the db in question using an admin login.
I created a login then user 'limited' but gave it no ownership or other database role membership.
Using the admin connection, I slected a particular stored proc as an example, and selected properties. Under 'permissions', I gave user 'limited' only 'execute' permission.
Then, using userid limited, I tested that I can execute the stored proc. (I can).
Then, here was my concern, I noted that 'Modify' was greyed out (Success!). To prove that userid limited cannot see the actual stored proc, I then tried to Script Stored Procedure/Create To/NewQuery Editor window... and it failed due to insufficient access rights. This seems to be exactly what I want.
So I conclude that giving a user Execute permissions does not allow them the right to view the stored proc. Am I missing something?
November 1, 2011 at 3:08 pm
PhilM99 (11/1/2011)
So I conclude that giving a user Execute permissions does not allow them the right to view the stored proc.
I'm sure I've seen execute permissions letting the user see (but not modify) the object. Maybe in an earlier version, or maybe I'm thinking of select permission and table definitions.
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
November 1, 2011 at 8:27 pm
Thanks. My experiment certainly isn't thorough. I think I'll try it on table defs and views and see what happens. I'm going to subscribe to the perspective that a dedicated hacker with mailicous or criminal intent is going to beat me anyway, but if I can set up at least a first level barrier then we have some footing on which to defend ourselves should our agreement be violated.
November 1, 2011 at 8:57 pm
PhilM99 (11/1/2011)
There was a clue in your answer, Gail so I pursued it. So with respect, can I ask you to revisit your reply?Here's what I did as an experiment:
Using SSMS,
I connected to a SQL Express instance with the db in question using an admin login.
I created a login then user 'limited' but gave it no ownership or other database role membership.
Using the admin connection, I slected a particular stored proc as an example, and selected properties. Under 'permissions', I gave user 'limited' only 'execute' permission.
Then, using userid limited, I tested that I can execute the stored proc. (I can).
Then, here was my concern, I noted that 'Modify' was greyed out (Success!). To prove that userid limited cannot see the actual stored proc, I then tried to Script Stored Procedure/Create To/NewQuery Editor window... and it failed due to insufficient access rights. This seems to be exactly what I want.
So I conclude that giving a user Execute permissions does not allow them the right to view the stored proc. Am I missing something?
After all is said and performed about permissions, etc., etc. As a deterrent to anyone who might view the procedure using any method, include in each procedure a copyright notification. This will at least deter some from using your work, and in other cases may be grounds for legal action by your company to recover $$ loss due to infringement actions by others. Discuss the pros, cons and the exact wording required to be effective with your firms lawyers.
Refer to:
http://library.findlaw.com/1999/Jan/1/241476.html
Which in part includes:
Original multimedia works are protected by copyright. The Copyright Act's exclusive rights provision gives developers and publishers the right to control unauthorized exploitation of their works..... software - that is protected under copyright law. Developers and publishers must avoid infringing copyrights owned by others.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply