Error: Permission denied for schema public

[harryrundles]

Hi guys,

Thanks for the help with a few of my earlier questions. I'm really new to Vertica and am having a bit of trouble catching on to a few things (We've been using MySQL for years).

What does this error mean?

diego=> create table Ttest (test1 int);
ROLLBACK 4367: Permission denied for schema public

Obviously its a permission issue, but I don't know what the public schema is. Can someone please explain?

Thank you,
Harold

[id10t]

Hi Harold!


Schema it is just a space name, scope you are working in it, look at schema like a folder and table like files. Since only one database can be up so schemes allows you define different work spaces. Data mart is a good example: firm with few departments(Sales, Marketing, Adv...). Departments its like users in OS, each "user" have access only to his scope.

Public schema it is like root directory in linux/unix - all tables, schemes begins from root. I agree that name "public" little confusing, I think that name "root" much more better than "public". It similar, but not equals, to GLOBAL spacename/scope in programming.

So you have no permissions to write in GLOBAL spacename, in root directory.

[janice]

Can we think of the public schema in Vertica as being the same as the public role in Oracle? Or is that too generic?

[rajasekhart]

Hi Harold,

Public schema is a default schema.

So, when you try to create a table without mentioning schema name before the table name, it will be directly created in Public schema.

Eg:
create table public.Ttest(test1 int) --Mentioning public is optional here.
or
create table Ttest (test1 int); Here table will be created in public schema if u have permission.

The reason for your error is , the user with which you logged in doesnt have any kind of permissions on public schema.

To have such permissions to that user, just login with dbadmin user and run the following command.

GRANT ALL ON SCHEMA PUBLIC TO <User_Name>;

Eg: GRANT ALL ON SCHEMA PUBLIC TO HAROLD;

Now Login with HAROLD user and run the create table Ttest(test1 int) command.
It will be successfully created.

Thanks,
Raj.

[piglet]

Is the "public" schema in vertica like "system" schema in Oracle then?

[harryrundles]

Thanks everyone! I fixed the issue per your directions

FYI.. I found this on page 773 of the SQL Refernece Guide (A little late )

Newly-created users do not have access to schema PUBLIC by default. Make sure to GRANT USAGE ON SCHEMA PUBLIC to all users you create.