General discussion

Locked

Using SQL Server system tables...

By jbelina ·
Opinions Requested:

I have a situation at work where a programmer is wanting to use the following script to determine if a user is allowed to run a specific set of stored procedures or not and I'm concerned because he is using system tables to get this information and I've had it drilled into me not to use the system tables other than my own DBA scripts...

I was hoping to hear the different opinions on this so that I can give the programmer some intelligent feedback. I'll split the TechPoints with the best 2 replies I get within the next week or so...

Thanks in advance!
Jeff M. Belina

---- cut here ----


SELECT
ProcName =so.name,
userName = su.name

FROM

sysobjects so (NOLOCK),
sysprotects sp (NOLOCK),
sysusers su (NOLOCK)

WHERE
so.type = 'P'
and sp.action = 224
and sp.id = so.id
and sp.uid = su.gid
and su.name = USER_NAME()

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Using SQL Server system tables...

by pVp In reply to Using SQL Server system t ...

Since the user_name() function showed up in this query, obviously the end-user is logged in to the dataserver with their own id (as opposed to using a generic id for all programs).

The simple, hands-off solution is to make the attempt and intercept the error returned for unauthorized users. This also has the advantage of not doing this twice, since the dataserver is going to check permissions anyway.

OTOH, this query isn't going to hurt anything. It's only a select and has nolock for each table.

Collapse -

Using SQL Server system tables...

by jbelina In reply to Using SQL Server system t ...

Thanks, I was hoping for more information, check some of the other answers below...

Collapse -

Using SQL Server system tables...

by Cedric King In reply to Using SQL Server system t ...

The script won't hurt, so you can rest assured that the system database will not be hurt. Yes, it is best if the programmer tries not to touch system database but if the needs arise, as long as the scripts will not hurt the database, then it will befine.

Collapse -

Using SQL Server system tables...

by jbelina In reply to Using SQL Server system t ...

I'd rather not let anyone touch the system tables if possible, I believe having an SP to do this might work...

Collapse -

Using SQL Server system tables...

by Andrew Prosser In reply to Using SQL Server system t ...

If you check out the books online you will see the following;

In 6.x system tables were used internally by SQL Server for a wide range of uses, including maintaining the list of character sets that SQL Server could use and containing information about active locks.

System tables have changed significantly. Most SQL Server 6.x system tables will continue to work properly. Views provided allow applications referencing SQL Server 6.x system tables to continue functioning properly. However, some SQL Server 2000 data cannot be referenced through these views. Use the provided Information Schema Views or ODBC catalog system stored procedures to obtain system table information. Modify scripts as appropriate. Any scripts referencing SQL Server 6.x system tables will not be converted properly.

These are basically to make your scripts more table for any new versions of SQL Server released. The view you want to query will therefore be -

TABLE_PRIVILEGES
Contains one row for each table privilege granted to or by the current user in the current database. The INFORMATION_SCHEMA.TABLE_PRIVILEGES view is based on the sysprotects and sysobjects system tables.

To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA view_name.

There is nothing wrong with querying system tables (just don't update them except in dire emergencies - you cannot do this anyway without running sp_configure 'allow updates'). Most other database systems virtually demand that you dp this anyway, ie. Oracle V$ views, Sybase sys tables, etc.

Regards,

Andrew.

Collapse -

Using SQL Server system tables...

by jbelina In reply to Using SQL Server system t ...

Information_schema is great for SQL 7.x and higher but doesn't work for SQL 6.5.

Thanks anyway!
Jeff

Collapse -

Using SQL Server system tables...

by rwscc In reply to Using SQL Server system t ...

The programmer should not be allowed to run a script against the system tables. You have a responsibility for server integrity and server security. While this particular script may not hurt integrety your decision must not open the door to security problems.
If a programmer has a legitimate need for a function that returns information only available by a query of the system tables he should submit a request that defines exactly what information is needed, why it is needed, and where it is needed. If the request is justified, you should develop a stored procedure that includes bounds checking on all parameters, and then allow the programmer to use the stored procedure.
If however, your shop is as understaffed as most are and there areinsufficient resources to handle these requests with a formal procedure that gives you adequate time to analyze the request and implement it in a stored procedure, the default policy has to be NO ACCESS to the system tables.
When the database crashes do to a script typo, or a security compromise is discovered that relates back to access of the system tables, you will be held far more accountable than the programmer.

Collapse -

Using SQL Server system tables...

by jbelina In reply to Using SQL Server system t ...

Thanks, aren't all shops understaffed at this point? :)

Anyhow, I really appreciate your information as well as the practice approach suggested.

I don't want anyone crashing the system, not that I think this would, but you never know what's coming up next

Thanks!
Jeff

Collapse -

Using SQL Server system tables...

by rraheb In reply to Using SQL Server system t ...

I have a suggestion for you which can save you the dilemma of using the system databases to check whether the user can run the stored procedures or not.

What you can do is that you can create a table which has all the users and all the functions (stored procedures \ queries \ tables) in the system.

Each user will have a row in the table which determines the type of access he should be granted for each component.

For e.g. :

Table writes would be : Read \ Update \ No Access

Queries : Run \ No Access

and so on.... so the write will be controlled according to the username and the access type assigned in the table.

So in your application, whenever a user requests a certain function, you'd first check the table and then perform or discard the request.

Hope this helps you out,

Rafik El-Raheb.

Collapse -

Using SQL Server system tables...

by jbelina In reply to Using SQL Server system t ...

Thanks, I've seen a similar approach with some programs that were designed in house... I think the programmer was hoping to keep the extra level of administration out of it and have all the administration done at the SQL Server level...

Thanks!
Jeff

Back to Web Development Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums