on 2011 Mar 02 2:18 PM
Taken from the newsgroup: Ingmar asked:
Hi,
I want to create a user who has almost no rights ... only "select" to 5 Views. I put the user to "select grant"and had produced per View a "grant select".
So far so good ... I can only see data from the 5 Views ... but I can see the entire database schema ... I really want to prevent this. Is that possible? The only thing that will make the user, the "select" on the 5 Views!
regards
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
We had a similiar situation where we needed to restrict acces to a user. Created 2 views and gave read/write priviledges and gave only 'select' permissions to the 2 views. Left all other user settings as is and the user remained in the Public group. If I understand Volker's answer then this user still had access to the system catalog?
Thank you.
User | Count |
---|---|
75 | |
9 | |
9 | |
8 | |
8 | |
7 | |
7 | |
6 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.