Create table privilege limited to one schema

[Julie]

Hi guys,

Is there a way that I can grant the create table privilege to a user and have that only be for a particular schema?

Thanks!

[JimKnicely]

Hi Julie,

That's easy to do with the GRANT CREATE ON SCHEMA command:

Here's an example:

Code: Select all

dbadmin=> create user test_user;
CREATE USER
dbadmin=> create schema schema1;
CREATE SCHEMA
dbadmin=> create schema schema2;
CREATE SCHEMA
dbadmin=> grant usage on schema schema1, schema2 to test_user;
GRANT PRIVILEGE                               ^
dbadmin=> grant create on schema schema1 to test_user;
GRANT PRIVILEGE

Now I'll connect as the test_user user and will be able to create a table in the schema1 schema but not the schema2 schema:

Code: Select all

dbadmin=> \c v_db test_user;
You are now connected to database "v_db" as user "test_user".
v_db=> create table schema1.test (test_col int);
CREATE TABLE
v_db=> create table schema2.test (test_col int);
ROLLBACK 4367:  Permission denied for schema schema2