SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Saturday, September 09, 2006

Passing parameter into SP for permissions?

SQL Apprentice Question
How can I pass a user name into a stored procedure I've created that
assigns certain table and SP permissions? The EXECUTE doesn't seem to
allow variables when permissions are involved. It wants literals, i.e.
'John' instead of @username.

Celko Answers
>> How can I pass a user name into a stored procedure I've created that assigns certain table and SP permissions? <<


You can probably do it with dynamic SQL, but why not use the DCL and
keep your system secure?

You do have a security officer who creates and monitors the user
accounts, don't you? Or do you really do it at the application level
on the fly? If so, have you told the security officer and the auditors
about this "feature" to subvert authority?

No comments: