Snippet Name: boca

Tags: helpful,scriptgeneration

Created Date: Oct 1 2021 8:05AM
Last Modified Date: Oct 1 2021 8:05AM

–desc: boca SQL to permission mapping

Dependencies: none-standalone code

Sample Output:

 
 --desc: boca SQL to permission mapping
 
 SELECT tabz.[TableName],tabz.[SQLSelect],HASHBYTES('SHA1',tabz.[SQLSelect]) AS SQLSelectHash FROM [BocaVoxSystem].[TableMap] tabz WHERE tabz.TableName = 'Communication'
 
 
 
 SELECT DISTINCT 
 
 @@SERVERNAME AS ServerName,
 
 DB_NAME() AS DataBaseName,
 
 tabz.[TableMapIndex],
 
 tabz.[TableName],
 
 Role.Code,
 
 HASHBYTES('SHA1',tabz.[SQLSelect]) AS SQLSelectHash,
 
 HASHBYTES('SHA1',sec.[SecuritySql]) AS SecuritySqlHash,
 
 Role.RoleIndex,
 
 tabz.[SQLSelect],
 
 --tabz.[SQLAdd],
 
 --tabz.[SQLUpdate],
 
 --tabz.[SQLDelete],
 
 --tabz.[IsAudited],
 
 sec.[SecuritySql],
 
 SecurityRuleIndex,
 
 SelUpdateSQL = 'UPDATE tabz SET SQLSelect  = '''  + REPLACE(tabz.SQLSelect,'''','''''') + ''' FROM [BocaVoxSystem].[TableMap] WHERE TableName = ''' + tabz.TableName + '''',
 
 SelRollbackSQL = 'UPDATE tabz SET SQLSelect  = '''  + REPLACE(tabz.SQLSelect,'''','''''') + ''' FROM [BocaVoxSystem].[TableMap] WHERE TableName = ''' + tabz.TableName + '''',
 
 SecUpdateSQL = 'UPDATE sec SET SecuritySql = '''  + REPLACE(sec.[SecuritySql],'''','''''') + '''
 
  FROM [BocaVoxSystem].[TableMap] tabz
 
    INNER JOIN BocaVoxSystem.SecurityRule sec
 
      ON tabz.[TableMapIndex] = sec.[TableMapIndex] --AND tabz.RoleIndex = sec.RoleIndex
 
  INNER JOIN BocaVoxSystem.Role 
 
    ON Role.RoleIndex = sec.RoleIndex
 
  WHERE  Role.Code = ''' + Role.Code +''' AND tabz.TableName = ''' + tabz.TableName + ''';',
 
 SecRollbackSQL = 'UPDATE sec SET SecuritySql = '''  + REPLACE(sec.[SecuritySql],'''','''''') + '''
 
  FROM [BocaVoxSystem].[TableMap] tabz
 
    INNER JOIN BocaVoxSystem.SecurityRule sec
 
      ON tabz.[TableMapIndex] = sec.[TableMapIndex] --AND tabz.RoleIndex = sec.RoleIndex
 
  INNER JOIN BocaVoxSystem.Role 
 
      ON Role.RoleIndex = sec.RoleIndex
 
  WHERE  Role.Code = ''' + Role.Code +''' AND tabz.TableName = ''' + tabz.TableName + ''';'
 
  FROM [BocaVoxSystem].[TableMap] tabz
 
  INNER JOIN BocaVoxSystem.SecurityRule sec
 
  ON tabz.[TableMapIndex] = sec.[TableMapIndex] --AND tabz.RoleIndex = sec.RoleIndex
 
  INNER JOIN BocaVoxSystem.Role ON Role.RoleIndex = sec.RoleIndex
 
 --where tabz.TableName = 'StudentHistory'
 
 ORDER BY tabz.TableName,SecuritySqlHash