0

This just came to me when helping my friend with his homework. Is it possible to know our role (implicit, not know to us) role grants as full access to a full table or just to a view derived from the table? The table [Table] contained a field[Class] indexed alphabetically, i.e., A,B,C,...,Z . The view contained just a subset , something like [class] , with values A,B, E,G,J. Now, if I was given a role with access to the latter table with that indexing , missing letters between B,E , E and G , etc., I would suspect I have access to a view and not the whole table. Still, is there a way, maybe by joining with/to other tables of knowing that my access is restricted? Maybe joining to a table containing the same PK as [Table] , without the Class field, maybe getting some Nulls as outputs? Basically, are there ways of knowing if I am viewing a full table or just a view derived from it? Maybe by trying to do something "full tables" can do but views cannot?

EDIT: Thanks for the answers, I was thinking more of indirect ways of getting an answer, like querying other tables on joins on my present table's PK ( assuming, of course, I have SELECT access to these tables ) and getting nulls, or by doing a " Where PK is null" query and getting some answers.

  • If you could figure out that you've got limited access to a table based solely on data from other tables you can query, then there's a flaw in the DB security set-up somewhere. Not finding rows matching your primary keys in other tables can just mean that no such rows exist, so it doesn't really tell you anything directly. And, the nature of SQL Server primary keys means that the value of an actual primary key for an actual row cannot be NULL. LEFT JOINs that return "rows" with a NULL primary key are really rows that don't exist in the right-hand table, so everything's NULL. – RDFozz May 30 '17 at 19:34
0

May be this example can help you:

CREATE TABLE Test (ID int);

SELECT USER_ID() AS UserId, USER_NAME() AS UserName;

SELECT  TABLE_SCHEMA,
        TABLE_NAME,
        TABLE_TYPE 
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_NAME = 'Test';

GRANT SELECT ON Test TO public;
GRANT REFERENCES ON Test TO public;

SELECT USER_NAME(grantee_principal_id) AS [User/Role],
       permission_name AS [Permission],
       state_desc AS [State]
FROM   sys.database_permissions
WHERE  class = 1 -- table
AND    OBJECT_NAME(major_id) = 'Test'

DROP TABLE Test;
GO
UserId | UserName                               
:----- | :--------------------------------------
5      | fiddle_9735bb1e26b1434294e426481043754d

TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE
:----------- | :--------- | :---------
dbo          | Test       | BASE TABLE

User/Role | Permission | State
:-------- | :--------- | :----
public    | REFERENCES | GRANT
public    | SELECT     | GRANT

dbfiddle here

You can use INFORMATION_SCHEMA views to get metadata about database objects.

SELECT  TABLE_SCHEMA,
        TABLE_NAME,
        TABLE_TYPE 
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_NAME = 'Test';

TABLE_TYPE field it's a varchar(10) field that returns either VIEW or BASE TABLE depending on the type of the object.

To know if you have or not permission you can query sys.database_permissions of the Security catalog views. Of course you need read access to this catalog.

SELECT USER_NAME(grantee_principal_id) AS [User/Role],
       permission_name AS [Permission],
       state_desc AS [State]
FROM   sys.database_permissions
WHERE  class = 1 -- table
AND    OBJECT_NAME(major_id) = 'Test'
  • Thanks for the answer, I was hoping for more of an indirect way, like attempting a join with a table on the PK and getting a null , or using a " where PK is null" when joining, something of this sort, of course, assuming I do have select permissions on other tables containing the same PK field. – MSIS May 29 '17 at 17:30
0

Basically, are there ways of knowing if I am viewing a full table or just a view derived from it?

The only thing you can see if you have restricted permissions is the type of object:

select type_desc
from sys.objects
where name = 'yourObject'

If you have only permissions on a subset of columns, you'll never figure it out using metadata (system tables/views):

The visibility of metadata is limited to securables that a user either owns or on which the user has been granted some permission.

Related link: Metadata Visibility Configuration

  • @sepubic: thanks for the answer; I don't mean that I can now necessarily in a direct way. True, by definition, this is kept from those who have restricted access. Can I, e.g., do a join query on the PK column and get nulls, etc? – MSIS May 29 '17 at 16:44
  • You can't join on a column on which you don't have a permission. The query will not be even compiled – sepupic May 29 '17 at 17:43
  • Sorry to insist on this, but could one then use this fact, that the query was not compiled, to conclude they are using a view? It is just that in a reasonably-safe system, I doubt that someone with restricted access, i.e., access to a view, would not have access to security-related system views. Sorry, just saw your link on Metadata, will check it out now. – MSIS May 29 '17 at 18:35
  • I don't understand your question. If you have no access to other colums, how can you even write a query using the columns that you don't know if they exist o no? You cannot figured out their names, you cannot even know if they exist – sepupic May 29 '17 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.