Create table privilege limited to one schema

Moderator: NorbertKrupa

Post Reply
User avatar
Julie
Master
Master
Posts: 221
Joined: Thu Apr 19, 2012 9:29 pm

Create table privilege limited to one schema

Post by Julie » Mon Jun 25, 2012 7:01 pm

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!
Thanks,
Juliette

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Create table privilege limited to one schema

Post by JimKnicely » Mon Jun 25, 2012 7:47 pm

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
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Security”