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
Assigning Create,Drop and Truncate privileges for roles
Moderator: NorbertKrupa
Re: Assigning Create,Drop and Truncate privileges for roles
Hi Nandhina M,
First, you need to create a role.
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.
http://my.vertica.com/docs/7.1.x/HTML/i ... TEROLE.htm
http://my.vertica.com/docs/7.1.x/HTML/i ... Schema.htm
First, you need to create a role.
Code: Select all
CREATE ROLE rolename;
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
Refer: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
http://my.vertica.com/docs/7.1.x/HTML/i ... TEROLE.htm
http://my.vertica.com/docs/7.1.x/HTML/i ... Schema.htm
Itipong Chewinpipat (Bay)
DBA Specialist (Vertica/Oracle)
DBA Specialist (Vertica/Oracle)
Re: Assigning Create,Drop and Truncate privileges for roles
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.
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.
Re: Assigning Create,Drop and Truncate privileges for roles
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.
http://my.vertica.com/docs/7.1.x/HTML/i ... ETABLE.htm
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.
Refer: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.
http://my.vertica.com/docs/7.1.x/HTML/i ... ETABLE.htm
Itipong Chewinpipat (Bay)
DBA Specialist (Vertica/Oracle)
DBA Specialist (Vertica/Oracle)
Re: Assigning Create,Drop and Truncate privileges for roles
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.....
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.....
Re: Assigning Create,Drop and Truncate privileges for roles
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
Refer:
http://my.vertica.com/docs/7.1.x/HTML/i ... ETABLE.htm
Itipong Chewinpipat (Bay)
DBA Specialist (Vertica/Oracle)
DBA Specialist (Vertica/Oracle)