The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 02 Mar '11, 14:18

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%


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...
permanent link

answered 02 Mar '11, 14:36

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

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.

(07 Mar '11, 16:26) zippidydo

Answered my own question. Thanks.

(07 Mar '11, 18:47) zippidydo
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×24
×24
×22
×17

question asked: 02 Mar '11, 14:18

question was seen: 900 times

last updated: 02 Mar '11, 14:36