How to set up a ROLE correctly

Database

How to set up a ROLE correctly

I am new to Teradata and trying to set up our developers on a test database. I want to use roles.

This is what I have run:

create database swh_dev from dbadmin as perm=200000000000 NO JOURNAL ;
grant all on swh_dev to swh_dev with grant option;

create role developer;

GRANT EXECUTE, SELECT, INSERT, UPDATE, DELETE, STATISTICS, DUMP,
RESTORE, CHECKPOINT, SHOW, EXECUTE PROCEDURE, ALTER PROCEDURE,
EXECUTE FUNCTION, ALTER FUNCTION, ALTER EXTERNAL PROCEDURE, CREATE
OWNER PROCEDURE, CREATE TABLE, CREATE VIEW, CREATE MACRO,
CREATE TRIGGER, CREATE PROCEDURE, CREATE FUNCTION,
DROP TABLE, DROP VIEW, DROP MACRO, DROP TRIGGER, DROP PROCEDURE,
DROP FUNCTION ON swh_dev TO developer;

grant developer to sstevens;

BTEQ -- Enter your SQL request or BTEQ command:
create table swh_dev.test2 (col1 integer);

create table swh_dev.test2 (col1 integer);
*** Failure 3524 The user does not have CREATE TABLE access to database swh
_dev.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

If I give an explicit grant create table on swh_dev to sstevens, then it works.

Why is this , what am I missing?
Tags (3)
2 REPLIES

Re: How to set up a ROLE correctly

I figured it out , needed to set the default role

modify user sstevens as default role=all;

Re: How to set up a ROLE correctly

You can also use the set role statement to swith between roles (incase default role is not set to all) assigned to a user during the session.

SET ROLE TestRole;