Users, Profile, Privileges & Roles:
Typical User Types:
Functional Category Description
General users Business Users/ Data Analyst – Read Data – Select
ETL / Report Create Table, Delete/Drop Table
Developers Select, Insert, Update
SQL Programmers Create complex queries, macros and stored procedures etc
Assistant Administrative users that assist the principal administrator.
administrators Program/Function specific DBA support – CDA example
Users: System users (DBC, SYSTEMADMIN, SYSTEMFE), Regular User, LDAP based users.
CREATE USER username AS
PASSWORD = password
PERMANENT = 50000 (BYTES)
TEMPORARY = n BYTES
SPOOL = n BYTES;
NO BEFORE JOURNAL -- Data snapshot before an activity like insert or update
NO AFTER JOURNAL
ACCOUNT = (‘IT’,’Admin’);
DEFAULT DATABASE = “SALES”
PROFILE = “ETL_DEVELOPER”
NO FALLBACK
While creating a user, the values for user name, password and permanent space is mandatory.
Other fields are optional.
ACCOUNT are used by the database to Prioritize requests/ work load/ Monitor resource usage,
user activity.
MODIFY USER username AS PASSWORD= "NewPassword"
MODIFY USER username AS DEFAULT DATABASE= “Marketing”
Access/Privileges: Examples
GRANT ALL ON database name TO user name; - Assistant DBA
GRANT SELECT ON database name TO user name; - Business User or Data Analyst
User Profiles: Use profiles to define resource parameters for groups of users with similar needs
instead of defining them for each individual user. Use profiles to specify parameters such as:
Temporary and spool space allocations
Accounts
Password control parameters
Default database assignment
Query band
The value for a profile attribute supersedes and replaces any corresponding values specified in
global defaults, or in a CREATE USER request.
CREATE PROFILE profile_name AS
ACCOUNT = ('account_str1','account_str2')
DEFAULT DATABASE = database_name,
SPOOL = spool_space,
PASSWORD =
(EXPIRE = n_days,
MAXLOGONATTEMPTS = attempts,
LOCKEDUSEREXPIRE = n_minutes),
QUERY_BAND = 'org=Finance;report=EndOfYear; universe=west' - Help in query analysis.
Privileges:
SELECT
CREATE /ALTER / DROP – TABLE / VIEW / MACRO/ SP /FUNCTION/ TRIGGER
EXECUTE MACRO / SQL
CALL STORED PROCEDURE
INSERT, UPDATE, and DELETE
Types of Privileges: You can explicitly grant database privileges to users, roles, or databases.
Privilege Description
Implicit Privileges
Ownership Users that own perm space have certain implicit (ownership) privileges on any
object contained in the space they own, even if they did not create the object.
You can transfer ownership using the GIVE statement.
Explicit privileges
Automatic When a user creates a database object example Table, Teradata automatically
grants privileges to the creator of the Table.
GRANT Directly to a user
To a role, then GRANT membership in the role to one or more users
Inherited Privileges that a user acquires indirectly.
A user inherits all the privileges granted to each role of which the user is a
member.
Directory users inherit the privileges of the database users and external
roles to which they are mapped.
Using Roles to Manage User Privileges/Rights
1. Create roles,
2. Grant privileges to each role.
3. Grant role membership to users.
4. Revoking Privileges From Roles:
CREATE USER prateek
CREATE ROLE prateek_role;
GRANT CREATE SESSION,ALTER SESSION,CREATE TABLE,CREATE CLUSTER,CREATE
SYNONYM,CREATE VIEW,CREATE SEQUENCE,
CREATE DATABASE LINK,CREATE PROCEDURE,CREATE TRIGGER,CREATE TYPE,CREATE
OPERATOR,CREATE INDEXTYPE,BACKUP ANY TABLE,
SELECT ANY TABLE,CREATE ANY TABLE,CREATE ANY INDEX,ALTER ANY INDEX,ALTER ANY
INDEXTYPE,DROP ANY INDEX,DROP ANY INDEXTYPE to prateek_role;
GRANT prateek_role to prateek ;
REVOKE INSERT ON applog FROM logwriter;