Select permission only through a procedure

[rt63]

Hi folks,

I wanted to implement a policy at our workplace where users cannot submit a select query directly but should execute a procedure with the query text as parameter and the procedure in turn will execute the query. The users should be denied all rights except to execute this procedure.

The basic code of the proc is like this

declare sp_queryproc @sql_str varchar(8000)
as
execute (@sql_str)

The procedure will contain some more code to set transaction parameters and some other checks to validate whether to allow the query or not. However, it seems that it will not work - whenever execute is used in the procedure, it checks for the permissions of user invoking the procedure and not the user who has created the procedure. If instead of execute, I give a hardcoded select statement (say select * from sometable), it works fine.

Can someone suggest how we can overcome this. We are on SQL 7.0 and expect users to submit any kind of query through query analyzer.

Thanks
RT

RT

 

[sguslan]

When you execute a stored procedure with static sql that has been compiled at the procedure creation time, it assumes the the procedure owner privileges. Whereas, when you pass an sql string, it is executed dynamically and it assumes the permissions given to the user invoking the procedures.
This is by design and it is extremely important, because usually the SPs are created by admins/DBO who have full authority on the database, and , hence a dynamic sql can pass back any data from the database which is basically giving the user select capability on any table in the database , and that is exactly what you are trying to avoid ....

 

[rt63]

I think I have not explained the issue properly. We are not trying to hide any data - only trying to ensure that users do not fire queries which will not choke up the processor.

There are some tables which contain millions of records. To begin with, we want to make sure that someone does not give a query like

select * from big_table

Moreover, all these queries need not lock any records. So to begin with what we want to do is to get this query from user and make some changes like

@sql = 'set rowcount 100 set transaction isolation level read uncommitted ' + @sql

At a later stage, we will also scan the query for proper joins etc. So, the issue is not data security but processor performance. We have 200+ users who may want to view data in whatever manner they like and have enough knowledge of database design. We only want to avoid runaway queries.

RT
RT

 

[sguslan]

I C ..

Have you tried using sp_executesql. I have to test it but now i ma out of the office, so may be later.

But it may behave differently in regard to session and user attributes..
MS MSDN library states that "sp_executesql stmt parameter is not compiled until the sp_executesql statement is executed. The contents of stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. "

Give it a try ...

 

[sguslan]

Another thought ...
Try using Application Roles as they are protected by password, the user can not use them directly. Giv the role all select permissions needed and call the sp_setapprole within the SP to enable access to the tables..

 

[rt63]

I tried the sp_executesql. It works the same way as execute. Also, sp_setapprole cannot be invoked within a stored procedure. It has to be invoked directly from T-SQL. Any more ideas? RT

 

[SQLSister]

Well best I can suggest is you give an application role select privleges on the tables and require the use of your user application. Users, even sophisticated ones, maybe even especially sophisticated ones, should never be able to access a SQL Server database through anything except a user interface. Query Analyzer and Enterprise manager should only be on developer and dba computers. But if they do get a hold of Query Analyzer, their individual logion won't have the permissions to select anything.

From book online on when to use application roles:

"Additionally, you may want users to be restricted to accessing data only through a specific application (for example using SQL Query Analyzer or Microsoft Excel) or to be prevented from accessing data directly. Restricting user access in this way prohibits users from connecting to an instance of SQL Server using an application such as SQL Query Analyzer and executing a poorly written query, which can negatively affect the performance of the whole server.

SQL Server accommodates these needs through the use of application roles."

 

[rt63]

Hi SQLSister,

I fully agree with you that no one except developers/DBA should have such rights but the problem is our users. We are an IT training group and these users teach IT subjects (SQL included) and obviously they think no end of their capabilities. Most of the times they are right but once in a while some bad queries do slip in.

Moreover, they are very used to the Query Analyzer interface which lets them do various things (like color codes on key words, query syntax checking, execution plan check, saving query output, getting T-SQL help online etc.) and they do not want to lose out on any of it. Developing an equivalent application will be some task - so I was looking out for easier solution. For us (the IS division), they are the customers and as they say customer is always right. Hence this post...

Thanks
RT

 

[tlbroadbent]

Have you considered setting the Query Governor Cost Limit option. SQL BOL contains details for setting this option and the effect it has. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[rt63]

Hi Terry,

Yes, I did think about that also. There were some issues with that
1. It has to be at the server level. So even queries which may genuinely need extra time will get affected (there are some as part of application design).
2. This can be taken care of by setting the cost limit in the respective query code but the same can be done by users submitting query thru QA.

Is there some way where such settings can be implemented at a user level? So far, this looks a good option. We will need to change some code containing heavy queries (to set the cost limit in the code). Users coming thru QA will have the server level setting unless they override it in the query in which case it will probably be by design rather than by mistake.

Thanks
RT

 

[sguslan]

From my reading, Query Governor is golobal and affects every login ..
The SQL Yukon 64-bit version will have better Query Governor options. Still have to test it as we will get an HP 64bit machine to test the new platform. If i get more interesting information on the Governor, i'll post it. Though this may take a month or so. ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[tlbroadbent]

Unfortunately, the governor can only be set at the Server or current connection level. Setting at the connection level would require each person to set "SET QUERY_GOVERNOR_COST_LIMIT" before running any queries each time a connection is opened. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SQLSister]

Given your users, have you considered giving them a test database to play in and test out new ideas etc. They would have full rights to this and it would be on another server so that if they perform an inefficient query the real system isn't clogged. Then make them access the production database only through a user interface with an application role and do not give them any direct permissions to the production database. They wil whine about this, but who cares. Ain't your job to be popular, it's your job to protect the database. They'll get over it in a few weeks; my developers whined when I took away their rights to production too, but now they don't even notice. Of course to sell it to management, you;l probably have to be able to back up your assertion that they take down the system with inefficient queries, so start documenting. And while i believe that everyone should be customer-centric, that does not mean they are always right, sometimes it is your job to show them why what they want is not a good idea or practical.