why the PC, PD, PE rights aren’t being generated on my machine TD 14.10.7.10

Database
Teradata Employee

why the PC, PD, PE rights aren’t being generated on my machine TD 14.10.7.10

Hi ,

Could anyone help me to understand why this is happenning in my machine.

Problem Description:
The following grant statement appears to work (doesn’t produce any errors), however only 1 of the 4 rights actually takes.

grant create procedure,drop procedure,alter procedure,execute procedure on loghstprocs to SYSTEM_DBA_ROLE;

select *
from dbc.allrolerights
where databasename = 'loghstprocs'
and rolename = 'SYSTEM_DBA_ROLE';
RoleName DatabaseName TableName ColumnName AccessRight GrantorName CreateTimeStamp
1 SYSTEM_DBA_ROLE              LOGHSTPROCS                         All

 

Thanks in Advance.

Best Regards,
Sunder

10 REPLIES
Apprentice

Re: why the PC, PD, PE rights aren’t being generated on my machine TD 14.10.7.10

Hi Sunder,

What release of TD are you using? I've just run this on 15.10 and it works fine for me - I get 4 rows back from the SELECT query.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Apprentice

Re: why the PC, PD, PE rights aren’t being generated on my machine TD 14.10.7.10

Sorry, just spotted that you're on 14.10.

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Apprentice

Re: why the PC, PD, PE rights aren’t being generated on my machine TD 14.10.7.10

Hi Sunder,

This also works for me on 14.10 (an early release).

Have you tried explaining your GRANT request. Check that all the accessrights appear in the various steps. On my 14.10 system they appear in three steps.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: why the PC, PD, PE rights aren’t being generated on my machine TD 14.10.7.10

Can you try to use DBC.AllRoleRightsV instead and check?

Teradata Employee

Re: why the PC, PD, PE rights aren’t being generated on my machine TD 14.10.7.10

Thanks to all i will check and update .

Teradata Employee

Re: why the PC, PD, PE rights aren’t being generated on my machine TD 14.10.7.10

Hi Dave,

 

Yes , all the accessrights appear in the various steps in the explain plan but still it is not showing when i am running the query to check  PC, PD, PE rights.

Explain plan :

1) First, we lock DBC.AccessRights for read on a RowHash, and we lock
     DBC.AccessRights for write on a RowHash.
  2) Next, we execute the following steps in parallel.
       1) We do a single-AMP ABORT test from DBC.DBase by way of the
          unique primary index "Field_1 = 'LOGHSTPROCS'" with no
          residual conditions.
       2) We do a single-AMP ABORT test from DBC.Roles by way of the
          unique primary index "Field_1 = 'SYSTEM_DBA_ROLE'" with no
          residual conditions.
       3) We do a single-AMP RETRIEVE step from DBC.AccessRights by way
          of the primary index "Field_1 = '00000000'XB, Field_2 =
          '00000000'XB" with a residual condition of (
          "((((DBC.AccessRights.Field_5 = 'PC') OR
          (DBC.AccessRights.Field_5 = 'PD')) OR
          (DBC.AccessRights.Field_5 = 'PE')) OR
          (DBC.AccessRights.Field_5 = 'AP')) AND
          (DBC.AccessRights.Field_3 = '000000000000'XB)") into Spool 1
          (all_amps), which is redistributed by the hash code of (
          '00001905'XB, '00001A05'XB) to all AMPs.
  3) We do an all-AMPs SORT to order Spool 1 (all_amps) by row hash.
  4) We do an all-AMPs MERGE into DBC.AccessRights from Spool 1 (Last
     Use).
  5) We do a single-AMP RETRIEVE step from DBC.AccessRights by way of
     the primary index "Field_1 = '00001905'XB, Field_2 = '00001A05'XB"
     with a residual condition of ("((DBC.AccessRights.Field_4 <> 0)
     AND (((((DBC.AccessRights.Field_5 = 'PC') OR
     (DBC.AccessRights.Field_5 = 'PD')) OR (DBC.AccessRights.Field_5 =
     'PE')) OR (DBC.AccessRights.Field_5 = 'AP')) AND
     (DBC.AccessRights.Field_6 = 'N'))) OR ((DBC.AccessRights.Field_4 =
     0) AND (((DBC.AccessRights.Field_6 = 'Y') OR
     ((DBC.AccessRights.Field_3 = '000000000000'XB) AND
     ((DBC.AccessRights.Field_10 <> TIMESTAMP '2017-09-28 04:02:35') OR
     (DBC.AccessRights.Field_10 IS NULL )))) AND
     ((((DBC.AccessRights.Field_5 = 'PC') OR (DBC.AccessRights.Field_5
     = 'PD')) OR (DBC.AccessRights.Field_5 = 'PE')) OR
     (DBC.AccessRights.Field_5 = 'AP'))))") into Spool 2 (all_amps),
     which is redistributed by the hash code of ('00001905'XB,
     '00001A05'XB) to all AMPs.
  6) We do a single-AMP RETRIEVE step from DBC.AccessRights by way of
     the primary index "Field_1 = '00001905'XB, Field_2 = '00001A05'XB"
     with a residual condition of ("(DBC.AccessRights.Field_4 = 0) AND
     (((((DBC.AccessRights.Field_5 = 'PC') OR (DBC.AccessRights.Field_5
     = 'PD')) OR (DBC.AccessRights.Field_5 = 'PE')) OR
     (DBC.AccessRights.Field_5 = 'AP')) AND ((DBC.AccessRights.Field_8
     = 'Y') AND (DBC.AccessRights.Field_6 = 'N')))") into Spool 2
     (all_amps), which is redistributed by the hash code of (
     '00001905'XB, '00001A05'XB) to all AMPs.
  7) We do an all-AMPs SORT to order Spool 2 (all_amps) by row hash.
  8) We do an all-AMPs MERGE DELETE to DBC.AccessRights from Spool 2
     (Last Use).
  9) We spoil the parser's dictionary cache for the database.
 10) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

 

Thanks ,

Sunder

Apprentice

Re: why the PC, PD, PE rights aren’t being generated on my machine TD 14.10.7.10

Hi Sunder,

 

Please run the following code if you can (it requires access to the DBC tables and not just views):

SELECT r.RoleName,
       db.DatabaseName,
       ar.AccessRight,
       ar.CreateTimeStamp
FROM DBC.DBase as db
    ,DBC.Roles as r
    ,DBC.AccessRights as ar

WHERE    r.RoleId        = ar.UserId
AND         db.DatabaseId = ar.DatabaseId
and r.rolenamei = 'system_dba_role'
order by 1,2,3;

If this does not return 4 rows then the problem is with the GRANT statement processing and you'll need to report it to Teradata support.

If this returns 4 rows then the access rights are in the table and this problem is one of reporting (e.g. the view definition), in which case can you provide a 'show view' output for dbc.allrolerights.

- NOTE: An earlier post suggested using dbc.allrolerightsv. Whilst I think it is a better practise to use the 'V' views, they typically only makes a difference to processing when you have long object names or object names containing non ASCII characters. I don't think either condition occurs here.

 

From my 14.10 system (albeit an earlier release than you) my DBC.AllRoleRights view is:

 REPLACE VIEW DBC.AllRoleRights
   (RoleName,
    DatabaseName,
    TableName,
    ColumnName,
    AccessRight,
    GrantorName,
    CreateTimeStamp)
AS
SELECT CAST(SUBSTRING(TRANSLATE(Roles.RoleName USING UNICODE_TO_LOCALE WITH ERROR) FROM 1 FOR 30)
            AS CHAR(30)),
       CAST(SUBSTRING(TRANSLATE(DBase.DatabaseName USING UNICODE_TO_LOCALE WITH ERROR) FROM 1 FOR 30)
            AS CHAR(30)),
       CAST(SUBSTRING(TRANSLATE(TVM.TVMName USING UNICODE_TO_LOCALE WITH ERROR) FROM 1 FOR 30)
            AS CHAR(30)),
       CAST(SUBSTRING(TRANSLATE(TVFields.FieldName USING UNICODE_TO_LOCALE WITH ERROR) FROM 1 FOR 30)
            AS CHAR(30)),
       AccessRights.AccessRight,
       CAST(SUBSTRING(TRANSLATE(COALESCE(GrantorDB.DataBaseName,'Dropped User') USING
                      UNICODE_TO_LOCALE WITH ERROR) FROM 1 FOR 30) AS CHAR(30)),
       AccessRights.CreateTimeStamp
FROM DBC.DBase
    ,DBC.TVM
    ,DBC.TVFields
    ,DBC.Roles
    ,DBC.AccessRights
       LEFT JOIN
     DBC.DBase GrantorDB
       ON GrantorDB.DatabaseId = AccessRights.GrantorId
WHERE    Roles.RoleId        = AccessRights.UserId
AND         DBase.DatabaseId = AccessRights.DatabaseId
AND         TVM.TVMId         = AccessRights.TVMId
AND         Tvfields.FieldId     = AccessRights.Fieldid
AND        (AccessRights.TvmId = Tvfields.Tableid OR
                  AccessRights.FieldId=0) WITH CHECK OPTION;

If you view definition is different from mine that might well be your problem. In which case I suggest you report it to Teradata support.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Teradata Employee

Re: why the PC, PD, PE rights aren’t being generated on my machine TD 14.10.7.10

That's an interesting query plan. It adds all the access rights requested to the role, then removes the ones for which the grantor does not have "GRANT OPTION". It appears that the grantor is not an owner of the subject database.

 

Double check whether the additional rights ought to be granted:

SELECT AccessRight, GrantAuthority FROM DBC.AllRightsV

WHERE DatabaseName='LOGHSTPROCS' and TableName='All' and AccessRight in ('PC','PD','PE','AP') and UserName='grantor';

Apprentice

Re: why the PC, PD, PE rights aren’t being generated on my machine TD 14.10.7.10

Hi Fred,

 

An interesting plan indeed. But if the grantor did not have 'with grant option' I'd have expected the GRANT to fail? Or maybe this is the root cause of the problem?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com