Page 1 of 1
Create table privilege limited to one schema
Posted: Mon Jun 25, 2012 7:01 pm
by 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!
Re: Create table privilege limited to one schema
Posted: Mon Jun 25, 2012 7:47 pm
by 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