Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 30 Jan 2004 @ 19:07:05 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: Transferring grants b/w environments
 
From:   Glen Blood

The easiest was is to restore DBC.

However, I have used the below during migrations where copying the DBC is not feasible. FYI - This assumes that that roles are not used. I will have to add steps to copy over role rights and role memberships in the future. The group by is there since if two different users grant the same right to the same user, then there are two records for one.

I would copy dbc.allrights into another table on the source system and move it over to the new system.

I would start with rights that are granted to all members of a user group.

I would then grant rights to individuals that have not already been granted.

Glen


select
'grant ' ||
(case accessright
WHEN 'DP' THEN
'  dump  '
WHEN 'RS' THEN
'  restore  '
WHEN 'R' THEN
'  select  '
WHEN 'IX' THEN
'  index  '
WHEN 'U' THEN
'  update '
WHEN 'I' THEN
'  insert  '
WHEN 'D' THEN
'  delete '
WHEN 'E' THEN
'  execute '
WHEN 'CD' THEN
'  CREATE DATABASE  '
WHEN 'DD' THEN
'  DROP DATABASE '
WHEN 'CU' THEN
'  CREATE USER  '
WHEN 'DU' THEN
'  DROP USER  '
WHEN 'CV' THEN
'  CREATE VIEW '
WHEN 'DV' THEN
'  DROP VIEW  '
WHEN 'CM' THEN
'  CREATE MACRO '
WHEN 'DM' THEN
'  DROP MACRO  '
WHEN 'CT' THEN
'  CREATE TABLE '
WHEN 'DT' THEN
'  DROP TABLE  '
WHEN 'PD' THEN
'  DROP PROCEDURE  '
WHEN 'PC' THEN
'  CREATE PROCEDURE  '
WHEN 'PE' THEN
'  EXECUTE PROCEDURE  '
WHEN 'DG'
THEN ' DROP TRIGGER '
WHEN 'CG'
THEN ' CREATE TRIGGER '
ELSE accessright
END ) ||
' on ' || trim(databasename) ||
(CASE tablename
WHEN 'all' THEN ' '
ELSE '.' || Trim(tablename)
END
)
||
(
CASE columnname
WHEN 'all' THEN ' '
ELSE '.' || Trim(columnname)
END
)
|| ' to ' ||
(
case allnessflag
WHEN 'Y'  THEN 'all '
ELSE ' '
END
)
|| TRIM(USERNAME) ||
(
case grantauthority
WHEN 'Y' THEN ' WITH GRANT OPTION '
ELSE ' '
END
)
|| ';'


from dbc.allrights
WHERE
username = 'o10782'

group by 1
order by 1


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016