Teradata 数据库与数据库之间的访问权限 [Database Privileges]

 

1.在A(DIPD_APP)数据库中访问B(DIPD_BLWORK)数据库的object（table、view），语法如下：

GRANT [RIGHTS1, RIGHT2, ...] ON B(DATABASE) TO A(DATABASE) WITH GRANT OPTION;

GRANT SHOW, SELECT ON DIPD_APP TO DIPD_BLWORK WITH GRANT OPTION;

 

2.移除权限：

REVOKE SHOW, SELECT ON DIPD_APP FROM DIPD_DLWORK;

 

 

3.查看所有B数据库的权限

LOCKING ROW FOR ACCESS SELECT
UserName 
,RoleName
,DatabaseName 
,TableName
,ColumnName 
,AccessRight
,case 
    when accessright='AE' then 'ALTER EXTERNALPROCEDURE'
           when accessright='AF' then 'ALTER FUNCTION'
        when accessright='AP' then 'ALTER PROCEDURE'
        when accessright='AS' then 'ABORT SESSION'
        when accessright='CA' then 'CREATE AUTHORIZATION'
        when accessright='CD' then 'CREATE DATABASE'
        when accessright='CE' then 'CREATE EXTERNAL PROCEDURE'
        when accessright='CF' then 'CREATE FUNCTION'
        when accessright='CG' then 'CREATE TRIGGER'
        when accessright='CM' then 'CREATE MACRO'
        when accessright='CO' then 'CREATE PROFILE'
        when accessright='CP' then 'CHECKPOINT'
        when accessright='CR' then 'CREATE ROLE'
        when accessright='CS' then 'CREATE SERVER'
        when accessright='CT' then 'CREATE TABLE'
        when accessright='CU' then 'CREATE USER'
        when accessright='CV' then 'CREATE VIEW'
        when accessright='CZ' then 'CREATE ZONE'
        when accessright='C1' then 'CREATE DATASET SCHEMA'
        when accessright='D' then 'DELETE'
        when accessright='DA' then 'DROP AUTHORIZATION'
        when accessright='DD' then 'DROP DATABASE'
        when accessright='DF' then 'DROP FUNCTION'
        when accessright='DG' then 'DROP TRIGGER'
        when accessright='DM' then 'DROP MACRO'
        when accessright='DO' then 'DROP PROFILE'
        when accessright='DP' then 'DUMP'
        when accessright='DR' then 'DROP ROLE'
        when accessright='DS' then 'DROP SERVER'
        when accessright='DT' then 'DROP TABLE'
        when accessright='DU' then 'DROP USER'
        when accessright='DV' then 'DROP VIEW'
        when accessright='DZ' then 'DROP ZONE'
        when accessright='D1' then 'DROP DATASET SCHEMA'
        when accessright='E' then 'EXECUTE'
        when accessright='EF' then 'EXECUTE FUNCTION'
        when accessright='GC' then 'CREATE GLOP'
        when accessright='GD' then 'DROP GLOP'
        when accessright='GM' then 'GLOP MEMBER'
        when accessright='I' then 'INSERT'
        when accessright='IX' then 'INDEX'
        when accessright='MC' then 'CREATE MAP'
        when accessright='MD' then 'DROP MAP'
        when accessright='MR' then 'MONITOR RESOURCE'
        when accessright='MS' then 'MONITOR SESSION'
        when accessright='NT' then 'NONTEMPORAL'
        when accessright='OD' then 'OVERRIDE DELETE POLICY'
        when accessright='OI' then 'OVERRIDE INSERT POLICY'
        when accessright='OP' then 'CREATE OWNER PROCEDURE'
        when accessright='OS' then 'OVERRIDE SELECT POLICY'
        when accessright='OU' then 'OVERRIDE UPDATE POLICY'
        when accessright='PC' then 'CREATE PROCEDURE'
        when accessright='PD' then 'DROP PROCEDURE'
        when accessright='PE' then 'EXECUTE PROCEDURE'
        when accessright='R' then 'RETRIEVE/SELECT'
        when accessright='RF' then 'REFERENCES'
        when accessright='RS' then 'RESTORE'
        when accessright='SH'  then  'SHOW'
        when accessright='SA' then 'SECURITY CONSTRAINT ASSIGNMENT'
        when accessright='SD' then 'SECURITY CONSTRAINT DEFINITION'
        when accessright='ST' then 'STATISTICS'
        when accessright='SS' then 'SET SESSION RATE'
        when accessright='SR' then 'SET RESOURCE RATE'
        when accessright='TH' then 'CTCONTROL'
        when accessright='U' then 'UPDATE'
        when accessright='UU' then 'UDT Usage'
        when accessright='UT' then 'UDT Type'
        when accessright='UM' then 'UDT Method'
        when accessright='W1' then 'WITH DATASET SCHEMA'
        when accessright='ZO' then 'ZONE OVERRIDE'
        else ' '
end (varchar(26)) as AccessRightDesc
,GrantAuthority
,GrantorName(varchar(128))
,AllnessFlag
,CreatorName
,CreateTimeStamp
from 
(
        select
        a.Grantee as UserName
        ,b.RoleName as RoleName
        ,b.DatabaseName as DatabaseName
        ,TableName
        ,ColumnName
        ,AccessRight 
        ,'' as GrantAuthority
        ,GrantorName
        ,'' as AllnessFlag
        ,'' as CreatorName
        ,CreateTimeStamp
        from dbc.allrolerights b
        join dbc.rolemembers a
        on a.RoleName=b.RoleName
        where UserName='DIPD_BLWORK'
        AND TableName='ALL'
        union all
        SELECT 
                UserName 
                ,'' as RoleName
                ,DatabaseName
                ,TableName
                ,ColumnName 
                ,AccessRight 
                ,GrantAuthority
                ,GrantorName
                ,AllnessFlag
                ,CreatorName
                ,CreateTimeStamp
        from dbc.allrights
        where UserName ='DIPD_BLWORK'
        AND TableName='ALL'
)Allrights
order by RoleName,DatabaseName,TableName,ColumnName,AccessRight;