How to get information about an SQL Select statement

I am wondering if it is possible to get the real name of the tables/fields from which each field in a select statement comes from.

Lets say you have two tables, Creditors and Debtors both have the fields Code, Name and Phone.

If a user enters the following sql statement:

SELECT Code AS CustomerCode, Name AS CustomerName, Phone AS ContactNumber FROM Debtors.

This will result in SQL server returning field names CustomerCode, CustomerName and ContactNumber.

Is it possible to get from the SQL server some sort of meta data that maps each field to its real name and the table it comes from?

Programmically, given an SQL select statement, I want to be able to determine the real name of each field and the real name of the tables they come from.

I don't want to parse the SQL myself so I thought that there might be away to send the statement to the sql server and get back this information without the row data.

What we are t开发者_StackOverflowrying to do is implement table/field level security. A user can enter an sql statement to select fields from a table or across multiple tables (using joins) and have the results displayed in a table. The fields get added dynamically to a grid control but only the ones that the user is allowed to see.

If the user joins multiple tables how do we know programmically which fields come from which table? the problem gets worse if they use aliases in the SQL.

Currently this is working on a legacy in house built sql engine (that uses a proprietary database) which can return all of the required table/field information without the row data so part of the applications security model is built around this. However moving this application to something like SQL server might prove difficult if we can't get this to work.

Besides Sql server, do any other sql databases support this type of functionality?

To the best of my knowledge, you cannot get that information.

You can probably handle your problem in many databases, however, by using GRANT / REVOKE security in the database itself. Assuming that users are logging into the database itself (not just your application), many DBMSes allow you to GRANT SELECT privileges on restricted columns from a table. Using that technique, it will not fool the server if the user specifies ALIASes for columns.

A quick google indicates that at least PostgreSQL, SQL Server, and Oracle offer column level GRANT SELECT protection based on userid.

Interesting question, by the way.

No. The whole point is you should only know the name presented to you and not where it came from.

The way to solve your problem is to remove all access from tables and only grant access through views with appropriate access permissions.