If you want users to select from the view, why are you granting to the table? By "revoke" do you mean explicitly revoke/deny? Deny will override grant so there's your problem... you should be able to accomplish this by adding grant to the view and not doing anything either way on the tables.
Here's a quick example where SELECT
has not been explicitly granted on the table, but has been on the view. The user can select from the view but not the table.
CREATE USER foo WITHOUT LOGIN;
GO
CREATE TABLE dbo.a(id INT);
CREATE TABLE dbo.b(id INT);
GO
CREATE VIEW dbo.v
AS
SELECT a.id FROM a INNER JOIN b ON a.id = b.id;
GO
GRANT SELECT ON dbo.v TO foo;
GO
EXECUTE AS USER = N'foo';
GO
-- works:
SELECT id FROM dbo.v;
GO
-- Msg 229, SELECT denied:
SELECT id FROM dbo.a;
GO
REVERT;
Note that this assumes foo
has not been granted elevated privileges through explicit permissions on the schema or database, or through role or group membership.
Since you are using tables in multiple databases (sorry I missed the end of that first sentence initially), you also may need explicit grants on the table(s) in the database where the view does not exist. In order to avoid granting select to the table(s), you could create a view in each database, and then join the views.
Create two databases and a login:
CREATE DATABASE d1;
GO
CREATE DATABASE d2;
GO
USE [master];
GO
CREATE LOGIN blat WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
In database d1
, create a user, then create a table and a simple view against that table. Grant select to the user only against the view:
USE d1;
GO
CREATE USER blat FROM LOGIN blat;
GO
CREATE TABLE dbo.t1(id INT);
GO
CREATE VIEW dbo.v1
AS
SELECT id FROM dbo.t1;
GO
GRANT SELECT ON dbo.v1 TO blat;
GO
Now, in the second database, create the user, then create another table and a view that joins that table to the view in d1
. Grant select only to the view.
USE d2;
GO
CREATE USER blat FROM LOGIN blat;
GO
CREATE TABLE dbo.t2(id INT);
GO
CREATE VIEW dbo.v2
AS
SELECT v1.id FROM dbo.t2
INNER JOIN d1.dbo.v1 AS v1
ON t2.id = v1.id;
GO
GRANT SELECT ON dbo.v2 TO blat;
GO
Now launch a new query window and change the credentials to be for the login blat
(EXECUTE AS
does not work here). Then run the following from the context of either database, and it should work fine:
SELECT id FROM d1.dbo.v2;
These should both yield Msg 229 errors:
SELECT id FROM d1.dbo.t1;
GO
SELECT id FROM d2.dbo.t2;
Results:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 't1', database 'd1', schema 'dbo'.
Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 't2', database 'd2', schema 'dbo'.
You should log in as the table owner (the user you used to create the tables) and then grant the necessary privileges to your user:
grant select on acs_activities to your_user;
If you want to do that for all tables, you can use:
grant select on all tables in schema public to you_user;
Or maybe you created them as the superuser, but your regular user should be the real owner, then you should do:
alter table acs_activities owner to your_user;
Best Answer
Read the manual on
GRANT
andALTER TABLE
.There is no
USAGE
privilege for tables. You would see an error message if you tried yourGRANT
command.You need permissions on the schema, too. Here
USAGE
is right. But you certainly want to revokeCREATE
.Ownership for sequences that are owned by
serial
columns is changed to the new table owner automatically. But any other sequences you'll need to reown manually. Or you go a more aggressive route withREASSIGN OWNED
.You may need permissions on sequences, too. (If you grant
INSERT
or want them to usecurrval()
ornextval()
functions). Those are currently (v9.6) separate from table permissions.I strongly suggest to bundle permissions in a group role and grant / revoke membership in that role to selected user roles.
Something along these lines, as superuser:
Related:
The schema
public
is in no way special. It only happens to be installed by default with permissions forPUBLIC
and preset in the defaultsearch_path
. Some DB admins even delete it. You can use it like any other schema. Depends on the complete situation. Related: