All Forums Database
sstevens 2 posts Joined 05/11
27 Jul 2011
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?

sstevens 2 posts Joined 05/11
28 Jul 2011

I figured it out , needed to set the default role

modify user sstevens as default role=all;

Fahdkhan111 3 posts Joined 09/13
09 Dec 2013

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;

Regards

You must sign in to leave a comment.