cancel
Showing results for 
Search instead for 
Did you mean: 

Restricted Rights for user

VolkerBarth
Contributor
2,677

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

Accepted Solutions (1)

Accepted Solutions (1)

VolkerBarth
Contributor

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:

  1. Based on the client API you use, it might be necessary to add access to more system tables (as done for sys.dummy above).
  2. I have never used that in a production system...
Former Member
0 Kudos

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.

Former Member
0 Kudos

Answered my own question. Thanks.

Answers (0)