How to Know if I have A Restricted or Full Access to Table

Question

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.

sepupic · Answer

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

McNets · Answer

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

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'

How to Know if I have A Restricted or Full Access to Table

2 Answers

Add your own answers!

Ask a Question