Assigning a select to 1 view to a user make him see all my objects?!!

Question

Assigning a select to 1 view to a user make him see all my objects?!!

mjksoft

Old Hand

Points: 344
More actions
March 22, 2005 at 4:10 am

#113625

I have a problem in giving access permission(Select) to user
I have a big database with hundreds of Views and tables and DTS packages ..
I want to give select privilege to users “AAA” and I made the following
From Server Security/Logins/ new users
The I assigned the required database then assigned him the public role
And from users under that database I assigned a select privilege to him for the target View.
It works fine, But when he registered my server in his server he was able to see all database and all objects I have in my server, he cant change any thing but he can read all my tables and views design even he can read and open my DTS packages ..

The question is how to preventing him to access all those objects ??!! I want him just to see the View which I assigned to .
Thanks and Regards
M. A

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Helen-84411 Hall of Fame Points: 3320 More actions · Answer 1

Hi,

You need to revoke the permission for all objects except the one you want him to see. With what kind of login is he connecting via EM.

Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me

mjksoft Old Hand Points: 344 More actions · Answer 2

Thanks for your reply

Yes this happens if the user login from EM.

Also I am already revoke all other permissions !?

Regards

Gabriela Nanau SSCommitted Points: 1967 More actions · Answer 3

What atuthentication method uses the server? Is the user member in some Windos groups that has larger permissions on the server?

mjksoft Old Hand Points: 344 More actions · Answer 4

Hello

SQL Authentication, its normal user not belong to any group its a son of SQL server only

I've just create it in Logins in EM and assigened it to 1 view only, Acctually he can read the data from that view, but also he can browse the whole other views design, even all DTS logic and design

How come ?!

Try yourself

1- add/create a user to SQL server Login and assigne him to 1 view in your dayabase

2- register the server in other computer using EM and see how many object you can see and how namy you can browse there design ? ???!!

I am working with Oracle Basiclly and this case is not allowed or happen in oracle EM?! I am sure i am doing a wrong thing ..

Regards

Gabriela Nanau SSCommitted Points: 1967 More actions · Answer 5

First, check for the existence of the user guest all the databases that you didn't give access to that login but it can see them. If such user exists, remove it.

Second, if the login can see all the objects (not select from a table, but see its schema; not execute a sp, but see its text) this is because public role in each database has SELECT rights in sysobjects, syscomments, syscolumns and so on. If the login also sees the DTS definition could be that either it has at least public access to msdb database and the packages are not password protected, or because there is a guest user in msdb database.

Hope it helps.

Gabriela