question

kumar4ashwani avatar image
kumar4ashwani asked

Can we given select permission only inside the procedure,direct select not allow...?

Alter Proc ExecQry (@Qry varchar(max)) as begin if(select CHARINDEX('*',@Qry))=0 begin exec (@Qry) End Print 'Char * is not allowed in selection list' End --ExecQry 'select Name,EmailAddress from Operator' Actuall i want to execute every query from this proc, and want to given only execute rights to users, and also want stop all direct select command. Is it possible, Please help me out this..... should be allowed like :---ExecQry 'select Name,EmailAddress from Operator' Should not be allowed for :- select * from Operator and Eror:-Char * is not allowed in selection list
selectpermissionsproc
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can i get a quick script for the same..??
0 Likes 0 ·
please help me still its not working....
0 Likes 0 ·
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
One of the advantages of stored procedures is that you can enable people to read tables without giving them direct access to those tables. But when you use dynamic SQL like this, the caller needs to have permission on the table. PS: This just seems such a bad idea. What do you hope to gain from it? Somebody will have to write the SQL to pass in anyway so you don't save coding time. And you'll have no protection from SQL injection unless you pass in strings that are parameterised queries, which are even harder to write.
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Well you could use the string functions to inspect the SQL they pass in but whether it's a "SELECT *" is the least of your worries. You will have to grant the login(s) read permissions on all tables as a minimum. Do any of these users have more than read permissions? You are giving them a tool where they can do anything they want if they can get it past your checks. How would you guard against something like this?: SELECT Name FROM dbo.Customers WHERE Name LIKE 'Micro%'; DROP TABLE dbo.Customers; (see https://www.xkcd.com/327/)
1 Like 1 ·
Actually i want to restrict the user to right query like "Select * from " means any query contains "*" should not be allowed. So to solve this purpose please help or share script to meet this requirement
0 Likes 0 ·
Hi David Actually i want to restrict the user to right query like "Select * from " means any query contains "*" should not be allowed. So to solve this purpose please help or share script to meet this requirement
0 Likes 0 ·
query contain *(start) should be restricted
0 Likes 0 ·
query contain *(star) should be restricted
0 Likes 0 ·
i want to stop only queries having * (star),by specifying the column name every one can fetch the data
0 Likes 0 ·
KutangPan avatar image
KutangPan answered
You can sign stored procedure with a certyficate and that should do the trick, using that i've created a role that can execute a stored procedure but has no visibility on the tables used and no way selecting data themselves using SELECT statements. You can also use the option to impersonate a login and execute stored procedure with specyfic premissions but is less secure that signing with a certyficate. find more here: [ http://sommarskog.se/grantperm.html][1] [1]: http://sommarskog.se/grantperm.html
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is what I would have recommended. It would help make the answer clearer if the 2nd paragraph ("You can also use...") stated that it was the `EXECUTE AS` option of `CREATE PROCEDURE`.
0 Likes 0 ·
If kurmar just want to prevent people writing SELECT *(star) there is another way, not the best and i wouldn't do it myself, but it would work, specyficly if you deny select on at least one table column for a user, that user won't be able to do a SELECT *(star) anymore on that specyfic table.
0 Likes 0 ·
Very interesting idea. I just tried the column-deny and it also prohibits doing `COUNT(*)` and even `COUNT(1)`. I wonder if it prohibits anything else.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.