Assigning Create,Drop and Truncate privileges for roles

[NandhiniA]

Hi,

I am facing an issue while assigning CREATE,DROP and TRUNCATE privileges to a role. I need to create a user who has the above privileges. But i get an error message stating CREATE,DROP,TRUNCATE is not valid statements.
GRANT CREATE,USAGE ON SCHEMA STAGING to CreateRole;
GRANT CREATE,DROP,TRUNCATE,SELECT On ALL TABLES IN STAGING TO CreateRole;

Kindly suggest me on how to assign truncate, create and drop privileges to a specific role.

Thanks,
Regards,
Nandhini M

[BoMBaY]

Hi Nandhina M,

First, you need to create a role.

Code: Select all

CREATE ROLE rolename;

Second, you grant privileges to the role. I think all you need is only CREATE privilege (Allows the user read access to the schema and the right to create tables and views within the schema). The object owner can DROP and TRUNCATE by themselves. Please tell if you want more than that.

Code: Select all

GRANT CREATE ON SCHEMA schemaname TO rolename;

CREATE ROLE

Creates a new, empty role. You must then add permissions to the role using one of the GRANT statements.

Syntax

CREATE ROLE role;

Parameters

role
The name for the new role. Name follows the conventions described in Identifiers.

Permissions

Must be a superuser to create a role.

Examples

This example shows to create an empty role called roleA.

=> CREATE ROLE roleA;
CREATE ROLE

GRANT (Schema)

Grants privileges on a schema to a database user or role.

Syntax

GRANT { ... { CREATE | USAGE } [ , ... ]
... | ALL [ PRIVILEGES ] }
... ON SCHEMA [db-name.]schema [ , ... ]
... TO { username | role | PUBLIC } [ , ... ]
... [ WITH GRANT OPTION ]

Parameters

CREATE
Allows the user read access to the schema and the right to create tables and views within the schema.

USAGE
Allows the user access to the objects contained within the schema. This allows the user to look up objects within the schema. Note that the user must also be granted access to the individual objects. See the GRANT TABLE and GRANT VIEW statements.

ALL
Applies to all privileges.

PRIVILEGES
Is for SQL standard compatibility and is ignored.

[db-name.]
[Optional] Specifies the current database name. Using a database name prefix is optional, and does not affect the command in any way. You must be connected to the specified database.

schema
Identifies the schema to which you are granting privileges.

username
Grants the privilege to a specific user.

role
Grants the privilege to a specific role.

PUBLIC
Grants the privilege to all users.

WITH GRANT OPTION
Allows the recipient of the privilege to grant it to other users.

Notes

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.

Examples

This example shows how to grant user Joe usage on schema online_sales.

=> CREATE USER Joe;
CREATE USER
=> GRANT USAGE ON SCHEMA online_sales TO Joe;
GRANT PRIVILEGE

Refer:
http://my.vertica.com/docs/7.1.x/HTML/i ... TEROLE.htm
http://my.vertica.com/docs/7.1.x/HTML/i ... Schema.htm

[NandhiniA]

Thanks for immediate reply.....

Please refers to the first query i have already specified the CREATE Privilege to the Schema. The role was already created. Sorry for not posting the create role statement. Create and usage privilege are specified to the STAGING Schema. For Any ETL users to execute the job that too especially for a staging database a user should possess the truncate privilege. Kindly let me know if we can assign CREATE, DROP, Truncate privileges specific to a Role.

[BoMBaY]

OK, Let say you have a ETL user (ETLUSER user) and a ETL role (ETLROLE role). ETLROLE was granted to ETLUSER. Assume that a STAGING schema was created by DBADMIN. Now you already grant CREATE on STAGING schema to ETLROLE. ETLUSER should have a privilege to create objects (Table, View, etc.) on STAGING schema. That mean any objects was created by ETLUSER is own by ETLUSER.

Example :

ETLUSER create T1 table on STAGING schema (Because ETLUSER has a CREATE privilege on the schema). ETLUSER will have all privilege on T1 table including TRUNCATE and DROP.

Please tell if I miss understand something.

TRUNCATE TABLE

Removes all storage associated with a table, while preserving the table definitions. TRUNCATE TABLE auto-commits the current transaction after statement execution and cannot be rolled back.

Syntax

TRUNCATE TABLE [[db-name.]schema.]table
Parameters

[[db-name.]schema.]
[Optional] Specifies the database name and optional schema name, for example, mydb.myschema. The database name identifies objects that are not unique within the current search path. (See Setting Search Paths.)

table
Specifies the name of a base table or temporary table. Cannot truncate an external table.

Permissions

Superuser or table owner. A schema owner can drop a table but cannot truncate a table.

Refer:

http://my.vertica.com/docs/7.1.x/HTML/i ... ETABLE.htm

[NandhiniA]

Thanks much for your quick reply.......

I got it worked for CREATE and truncate for the ETL User. I will also detail out the requirement very clearly. Sorry for not elaborating it very clearly.

I have two users ETLUSER and DDLUser and need to create two roles stating CREATE,DROP privileges for DDLUser and INSERT/DELETE/UPDATE/TRUNCATE for ETLUSer. DDLUser will create the tables on a given schema and ETLUser would just consume it to load/update/truncate data in the table created by DDLUser.

As dbadmin user
CREATE SCHEMA STAGING;

CREATE USER DDLUSER;
CREATE ROLE DDLCreate;
GRANT CREATE,USAGE ON SCHEMA STAGING to DDLCreate;
GRANT ALL On ALL TABLES IN SCHEMA STAGING TO DDLCreate;
GRANT DDLCreate TO DDLUSER;

Using DDLUser we can login and create/drop/truncate tables.

CREATE USER DMLUSER;
CREATE ROLE DMLRole;
GRANT USAGE ON SCHEMA STAGING to DMLRole;
GRANT INSERT.UPDATE,DELETE,SELECT On ALL TABLES IN SCHEMA STAGING TO DMLRole;

GRANT DMLRole TO DMLUSER;

Now i also need to give truncate privilege to DMLRole. Please advice.....

[BoMBaY]

As I know currently TRUNCATE TABLE operation need to be table owner or superuser.

Refer:

http://my.vertica.com/docs/7.1.x/HTML/i ... ETABLE.htm