Need help in writing a Stored Procedure

[shiv1607]

Stored Procedure that will grant/revoke all necessary accesses on all databases/objects to valid roles in server
Can some one help me in writing an Stored Proc in Teradata that will grant/revoke all necessary accesses on all databases/objects to valid roles in server

· All the access at the database level. But stored procedure should be flexible enough to handle accesses at object level as well. Also process should not fail, even if any object/roles does not exists.

· To check if the access given are aligned with the standards or not.

· Compare the standard permissions with the current permissions. Any access record that matches, no action required on it. Any access record that exists in standard permissions but does not exists in current permissions, need to grant it. Any access record that exists in current permissions, but does not exists in standard permission, revoke it.

Appriteate your help.

attached is the Sybase Pseudo Code, need something similar in Teradata

SET TEMPORARY OPTION ON_ERROR = "CONTINUE";

if exists (select * from sys.sysprocedure
where proc_name = 'security_schema' and creator = user_id() ) then
drop procedure security_schema;
end if;

SET TEMPORARY OPTION ON_ERROR = "CONTINUE";
SET TEMPORARY OPTION ON_TSQL_ERROR = "CONDITIONAL";

create procedure security_schema (
in @param_in varchar(8) default 'grant'
) ON EXCEPTION RESUME
begin

declare @object_perm varchar(255);
declare @engine varchar(15);
declare @db_name varchar(15);
declare @error_cnt int;


/***************************
** set table permissions **
***************************/

declare local temporary table #object_exceptions (
object_type char(1),
object_name varchar(60)
) in SYSTEM on commit preserve rows;


declare local temporary table #object_permissions (
object_order tinyint,
object_type char,
object_name varchar(60),
grantee varchar(40),
object_perm varchar(120) null
) in SYSTEM on commit preserve rows;

declare local temporary table #current_perms (
table_type char,
table_name varchar(60),
grantee varchar(40)
) in SYSTEM on commit preserve rows;


declare local temporary table #delete_perms (
table_type char,
table_name varchar(60),
grantee varchar(40)
) in SYSTEM on commit preserve rows;

declare grant_cursor dynamic scroll cursor for
select object_perm
from #object_permissions
order by object_order,object_type,object_name;

SET TEMPORARY OPTION ON_TSQL_ERROR = "CONDITIONAL";
SET TEMPORARY OPTION ON_ERROR = "CONTINUE";

if @param_in is null or
@param_in not in ('grant','revoke','list','all') then
message 'Missing input param: security_schema "grant/revoke/list/all"' to client;
message @param_in to client;
return;
end if;


/************** set up exceptions *******************/
-- extraneous procs - no extra permissions handed out for now
-- insert into #object_exceptions values ('P','dummy');
-- insert into #object_exceptions values ('P','ps');
-- insert into #object_exceptions values ('P','du');
-- insert into #object_exceptions values ('P','wc');
-- insert into #object_exceptions values ('P','sp__ddl');
-- insert into #object_exceptions values ('P','sp__diskdevice');
-- insert into #object_exceptions values ('P','__user_ddl');
-- insert into #object_exceptions values ('P','__table_ddl');
-- insert into #object_exceptions values ('P','__column_ddl');
-- insert into #object_exceptions values ('P','__view_ddl');
-- insert into #object_exceptions values ('P','__proc_ddl');
-- insert into #object_exceptions values ('T','dummy');

-- udw objects that do not follow the rules
-- this list removes objects from being granted according to generic rules
-- insert into #object_exceptions values ('T','eforms');
-- insert into #object_exceptions values ('V','cas_wfis_allvars');
-- insert into #object_exceptions values ('V','cas_wfis_flag');
-- insert into #object_exceptions values ('V','cm_master_basic');
-- insert into #object_exceptions values ('V','plastics_mailed_v');
-- insert into #object_exceptions values ('P','security_schema');
-- insert into #object_exceptions values ('P','expnet_load');

set @db_name = db_name();

if @db_name = 'IQP0' then
set @engine = 'engineP';
else
set @engine = 'engineA';
end if;


/**************** end of exceptions ***********************/


-- start logic according to rules.
-- generate list of object permissions from generic logic
insert into #object_permissions (object_order, object_type, object_name, grantee, object_perm)
select 1,
(case table_type when 'BASE' then 'T' when 'VIEW' then 'V' else 'U' end),
table_name,
(case when table_name like '[_]%' then 'cminfo' else 'adhoc' end) grantee,
(case when table_name like '[_]%' then 'grant select on '||table_name||' to cminfo;'
else 'grant select on '||table_name||' to adhoc;'
end)
from sys.systable
where table_type in ('BASE','VIEW') and creator = user_id() and table_id > 206;
commit;

insert into #object_permissions
select 2, 'P', proc_name, @engine, 'grant execute on '||proc_name||' to '||@engine||';'
from sys.sysprocedure
where creator = user_id() and
proc_id > 350 and
proc_name not like 'sp[_]%' and
proc_name not like '%[_][_]%' and
convert(varchar(25),proc_defn) not like '%function%' and
datalength(proc_name) > 4;
commit;

-- remove exceptions listed above to give
-- a list of generated permissions with exceptions removed.
delete #object_permissions
from #object_permissions inner join #object_exceptions
on #object_exceptions.object_name = #object_permissions.object_name and
#object_exceptions.object_type = #object_permissions.object_type and
#object_permissions.object_order < 9;

-- get list of current permissions given to tables.
insert into #current_perms (table_type, table_name, grantee)
select (case table_type when 'BASE' then 'T' when 'VIEW' then 'V' else 'U' end) table_type,
cast(t.table_name as varchar(60)) table_name,
cast(u.user_name as varchar(40)) grantee
from systable t
inner join systableperm p
on p.stable_id = t.table_id and
t.table_id > 206 and
t.creator = user_id() and
t.table_type in ('BASE','VIEW')
inner join sysuserperm u on u.user_id = p.grantee;

-- get list of current permissions given to procs.
insert into #current_perms (table_type, table_name, grantee)
select 'P', proc_name, u.user_name
from sysprocedure t
inner join sysprocperm p
on p.proc_id = t.proc_id and
t.creator = user_id() and
t.proc_id > 350 and
t.proc_name not like 'sp[_]%' and
t.proc_name not like '%[_][_]%' and
convert(varchar(25),proc_defn) not like '%function%' and
datalength(t.proc_name) > 4
inner join sysuserperm u on u.user_id = p.grantee;

-- join between generic logic and current permissions to get list to be deleted from both
insert into #delete_perms (table_type, table_name, grantee)
select c.table_type, c.table_name, c.grantee
from #current_perms c
inner join #object_permissions p
on p.object_type = c.table_type and
p.object_name = c.table_name and
p.grantee = c.grantee;


-- delete table permissions that match generic logic
delete #current_perms
from #current_perms
inner join #delete_perms
on #delete_perms.table_type = #current_perms.table_type and
#delete_perms.table_name = #current_perms.table_name and
#delete_perms.grantee = #current_perms.grantee;

-- delete generic grants that already exists in system.
delete #object_permissions
from #object_permissions
inner join #delete_perms
on #delete_perms.table_type = #object_permissions.object_type and
#delete_perms.table_name = #object_permissions.object_name and
#delete_perms.grantee = #object_permissions.grantee;

if @param_in = 'revoke' then
delete #object_permissions;
end if;

if @param_in in ( 'revoke', 'list', 'all') then
insert into #object_permissions (object_order, object_type, object_name, grantee, object_perm)
select 10,table_type,table_name,grantee,'revoke all on ' || table_name || ' from ' || grantee || ';'
from #current_perms
where table_type != 'P';

insert into #object_permissions (object_order, object_type, object_name, grantee, object_perm)
select 11,table_type,table_name,grantee,'revoke execute on ' || table_name || ' from ' || grantee || ';'
from #current_perms
where table_type = 'P';
end if;

open grant_cursor;

lp: loop

fetch next grant_cursor into @object_perm;
if sqlcode <> 0 then leave lp end if;

message @object_perm to client;

if @param_in != 'list' then
execute immediate @object_perm;
if @error_cnt = 0 then set @error_cnt = 1 end if;
end if;

end loop;

close grant_cursor;


commit;


-- following list of perms not matching generic logic
-- select * from #current_perms;


end;


Thanks

Shiv