November 12, 2002 at 6:34 am
-Using MS SQL Server 2000
-Several different domains (non-trusted)
-users accessing database/view via Crystal Reports
I have a database holding information from many non-local departments (some are overseas). I need to enable those departments to access (SELECT only) the data that pertains only to them.
I have a single database with 65 tables. The data that these departments need to view are in two tables.
So far, I have created a test VIEW and the data is in it just fine. Now the problem...
I created a test user and tried to give them access (SELECT) to just the VIEW and not the rest of the tables. But it didn't work, I got the error message that the user does not have access to the database. (I gave permission using GRANT SELECT on myview TO testuser). If I allow access to the database, then the user can access any table.
How do I set up permissions/security so that the user can only access the VIEW and not the rest of the database tables?
-Bill
November 12, 2002 at 6:59 am
If you do not give the group public permissions to the tables, the user will only have those permissions you give him.
Joachim.
November 12, 2002 at 8:13 am
Run the sp_grantdbaccess to grant them access to the database (this is a requirement).
As joachim has pointed out, if you've not given them access to the tables, simply by giving them access to the database they don't have access to them.
Don't put them in the db_datareader role. That will give implicit access to all tables.
If the view and the underlying table(s) are owned by the same owner, grant access only on the view.
If the view and the underlying table(s) are owner by different owners, you'll have to grant access on the view AND grant access on the table. If they should only be able to see certain columns on the tables, you'll have to use column-level security to restrict access. If you just give a GRANT SELECT ON <table> TO <user> the user will be able to select against all the columns. Instead, use GRANT SELECT ON <table> (<column list>) TO <user> to grant column-level permissions.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 12, 2002 at 8:49 am
All members are automatically part of the PUBLIC role, I can't drop anyone from PUBLIC nor drop PUBLIC (I tried via Enterprise Manager and got the error 'members cannot be dropped from Public').
Here's what I've done so far:
1. Created a view - October (database, view, and tables are all the same owner)
---using Enterprise Manager
2. created standard database role - t_CrystalReports
3. created a login - bfleming
4. Specified which database could be accessed - mydatabase
5. In Database Roles for 'mydatabase' I checked t_CrystalReports
and db_denywriter.
I openned Crystal Reports and selected New Report, entered user name bfleming, and I can access every table in the database.
What am I missing?
-Bill
November 12, 2002 at 9:00 am
You can see every table in the database, but can you actually return data from every table?
If you can, check to make sure the public role hasn't been placed in the db_datareader role. If it hasn't check the permissions given to public. Public may have had SELECT permissions granted on all the tables. If this is the case, you'll have to undo those permissions OR create a second role and slap DENY on all the tables.
If you have to slap DENY on all the tables, the view has to be owned by the same owner as the table. Better yet would be to drop this new role in db_denydatareader and then add users to this role. Grant this role permission to the view. The reason for same owner over table and view is SQL Server will perform a security check when going from the view to the table and you'll break if you don't. If the table and the view have the same owner, as SQL Server crosses from view to table, it'll see the ownership hasn't changed and assumes the owner knew what he or she was doing when pointing at the table from the view. It won't perform a security check.
BTW, assigning permissions against the public role is a bad idea. Here's an article from Andy which details a bit why:
http://qa.sqlservercentral.com/columnists/awarren/sqlpermissionspublicrole.asp
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply