The table_privileges view is only preserved for compatibility with ancient versions of Oracle.
You should instead be using the user_tab_privs view, all_tab_privs view, or dba_tab_privs view (documentation links for each when you click).
There is no way to "hide" a linked server or prevent unauthorized users from trying to use it. All you can control is whether they actually have access on the other side. A linked server in and of itself is not an actual object; like a synonym, you don't grant access to the alias, just to the thing(s) it references.
Try it out, and you will see that some of your assumptions (e.g. that "a login ... can not see the linked server in object explorer") are false. I did this:
- Created a login, and granted no privileges whatsoever, just CONNECT / public role.
- Created a linked server, and did not add this login to the list of logins.
- Opened a new instance of Management Studio, connecting with this login.
- I was able to see all linked servers under Server Objects > Linked Servers.
- I was also able to query
sys.servers
to see the list of linked servers.
I was able to run the following query no problem:
SELECT name FROM [linked server].master.sys.objects;
However I was unable to run queries against non-system databases, since I had not granted explicit access to any non-system database or the objects inside. E.g.:
SELECT SalesOrderID FROM [linked server].AdventureWorks2012.Sales.SalesOrderHeader;
Yielded this error:
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI11" for linked server "linked server" does not contain the table ""AdventureWorks2012"."Sales"."SalesOrderHeader"". The table either does not exist or the current user does not have permissions on that table.
I expect that you can achieve more restrictive results by explicitly denying access to specific objects on the linked server (or by DENY CONNECT SQL
to that login altogether), but this does not limit its effect to the linked server; it also affects direct connections.
In any case, I fail to see the point of what you're trying to accomplish:
- If the user doesn't have access on the other side, what is the harm in letting them try? (You might have better luck telling them not to and auditing them than trying to prevent them from trying in the first place.)
- If the user does have access on the other side, why can't they use the linked server?
Best Answer
You want local Windows users to have the same permissions as the database owner of a specific database? How about:
EXEC
line when you think it looks right):