DEFINER (default): owner/creator privileges during compile, owner during execution
Nested views: privileges of creator checked at create time, for owner checked at run-time.
UPDATE/DELETE commands with WHERE clause require SELECT as well
DEL/DROP DB/USER: Cannot drop entire hierarchy, only objects within specified DB/USER
CREATE USER grants CREATE/DROP USER/DATABASE automatic rights to creator
Column level privileges: SEL/UPD/REF/INS
GRANT .. TO ALL <db> allows new childs to inherit previously granted rights to parent
ALL DBC is same as PUBLIC
Dropping GRANTOR does not revoke granted access rights
To GRANT/REVOKE LOGON from specific host id, need EXEC access on dbc.LogonRule
SHOW STATISTICS without VALUES requires only ANY privilege, whereas VALUES clause requires SELECT access to underlying objects. OVERRIDE SELECT CONSTRAINT for RLS tables
Trusted connection allow Teradata to check privileges, query/access logging against end-user IDs instead of middle-tier ID
GRANT CONNECT THROUGH <trusted-user> [WITH TRUST_ONLY] TO <app-user>
4 UDF enforce SELECT, INSERT, UPDATE and DELETE security checks
Sample UDF rules:
select session >= row or session superset of row
insert row gets session labels
updates select + are reclassified using user’s level.
Deletes are not permitted unless at the lowest level or NULL
GRANT CONSTRAINT DEFINITION|ASSIGNMENT for defining|using constraints
BEGIN LOGGING DENIALS… FOR CONSTRAINT log row if security constrained on the object is not defined for the session. If defined but lacks required value then no logging is done.
Security is bypassed for users with OVERRIDE CONSTRAINT privilege
Compound statement (INS-SEL)
All tables must have same security constraints
constraints are copied from source, unless user has override privilege
All RLS tables within a vIew must have same security constraints
if there is NO RANGE partition, DROP WITH DELETE actually moves rows to it
Alternate to using CASE_N -> RANGE_N(HASHBUCKET(HASHROW(COL_EXPR)) MOD nn)
For partition elimination on TIMESTAMP casted to DATE, AT clause must be specified, so the expression becomes deterministic. Otherwise date is dependent on session timezone
Can’t alter partitioning expression unless stats on PARTITION is dropped
Use DELETE instead of ALTER to drop partitions for lengthy op (maybe because of SI), since DELETE takes WRITE lock whereas ALTER will take EXCLUSIVE lock
MLPPI:
most likely eliminated or less granular levels at higher level.
ordering is less imp if many data blocks at combined level.
For non-empty tables, only RANGE_N can be added/removed only at highest level
if ALTER changes # of partitions, it can be done only on level 1
SPE can occur with CURRENT_DATE, :var, certain built-in function. Only CURREN_DATE allows caching of plans (TD12)
Can ALTER to add/remove partition only at the ends and if RANGE_N is used.
Can ALTER to modify multiple levels, but number of non-level 1 parts cannot be changed
ROT Overpartitioning is when blocks/AMP/Partition < 10
ALTER TO CURRENT ONPARTITION BY CASE_N(EXP_DATE = CURRENT_DATE, NO CASE) will perform worse than on PARTITION BY CASE_N(EXP_DATE >= CURRENT_DATE, EXP_DATE < CURRENT_DATE) because the former will require scanning both partitions to move rows. The latter only the first partition needs to be scanned for moving rows
UNKNOWN partition is always scanned
Preferable to use contiguous timelines instead of gaps when using CASE_N
Excess partitions allocated to:
The only single partition overriding ADD
MLPPI and only CP doesn’t have ADD, then CP
MLPPI, no ADD on CP and at least 1 row partition, then 10 to CP and 1st no-ADD RP
MLPPI, only RP with no ADD, no-ADD RP in level order
Only for CPNoPI: Use HASH BY [RANDOM| …] to reduce data skew
Use LOCAL ORDER BY in increase compressibility
Special INSERT (CP) SELECT (NOPI) optimization to avoid spool
CP JI must be single table, non compressed, must have ROWID, no AJI, but can be sparse
Reduces IO, so if workload is CPU bound then CP may not be a good fit
Use INTO for adding columns: ALTER TABLE x ADD storeid INT INTO store_name
If # of available contexts < # of column partitions being selected, multiple sub-row partitions will be created using available contexts and then all will need to be consolidated
COLLECT STATS ON PARTITION#Ln is allowed only for column partition level and collects compression info rather than row ratio of each column partition.
INSERT-SELECT into CP (vs CP+RP) only table from NOPI can avoid sorting rows.
INSERT-SELECT may not spool source even when access lock on the source if source needs to be read only once (that is if there are enough contexts to write all columns at once)
Auto-compression isn’t carried to spool, but user compression is. => Spool requirements are usually higher (than base CP table)
DDL:
Grouped columns are combined into 1 column partition CT T ((c1 INT, C2 INT))
ALL BUT columns are in single column partition with auto compress
Without all but, unspecified columns are in single partition
ROT: Have column partitions at first level then row partitions.
Otherwise INSERTs are slow because it requires searching through several combined partitions to find correct container that is end point.
ROT: Use COLUMN format when columns are narrow and many duplicates. Use ROW format when columns are wide and not many duplicates
ROT: INSERTs are costly because auto-compression and row needs to be split into multiple CP. If the source table is not a NOPI table, it may be scanned multiple time depending on # of contexts
ROT: Number of read contexts are (inversely) affected by DB size. Write contexts are not.
ROT: If # of column contexts are constantly running out increase PPICacheThrP