Well, each user is member of the PUBLIC group by default, and as such each user can query the system catalog.
You can do the following to restrict that access (tested on SA 12 demo):
-- Create a user and grant only select (or insert,...) on one particular view
grant connect to MyUser identified by 'MyPwd';
grant select on GROUPO.ViewSalesOrders to MyUser;
-- Necessary to connect: select on dummy
grant select on sys.dummy to MyUser;
-- revoke the default PUBLIC membership -> prevents access to the system catalog
revoke membership in group public from MyUser;
-- show group membership for that user -> empty
select * from sysgroups where member_name = 'MyUser'
Now you can connect as that user "MyUser":
Note: You don't have access to the system catalog, so the following queries will fail with a "permission denied" error:
select * from sys.systable;
select * from GROUPO.Products;
call dbo.sa_conn_info();
nor can you access any other table/view/procedure, but you can do the following (with a necessary table owner qualification!):
select * from GROUPO.ViewSalesOrders
Some notes:
- Based on the client API you use, it might be necessary to add access to more system tables (as done for sys.dummy above).
- I have never used that in a production system...
answered
02 Mar '11, 14:36
Volker Barth
39.9k●360●547●816
accept rate:
34%