ACL cleanup

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



This might not be 100% performance compilant but i guess its better than -hackers since
these day's there seem to be some big consern :)
 
So feel free to comment
 
[Abstract: Underlyin plpgsql should remove all public user ACL's from Function,Table Sequence,View ... ]
 
-elz
 
---------------------------------------------
---------------------------------------------
 
CREATE OR REPLACE FUNCTION cleanup_public_perm_on_function()
  RETURNS int4 AS
'
DECLARE
r_record  record;
v_record  record;
exec_string text;
argument_string text;
i int2;
BEGIN
    FOR r_record IN SELECT * FROM pg_proc WHERE proowner !=''1''  LOOP
    
 exec_string = '''';
 argument_string = '''';
    
 exec_string = ''REVOKE ALL ON FUNCTION '' || r_record.proname  || ''('';
 
 IF (r_record.pronargs > 0) THEN
  i = 0;
  WHILE (i < r_record.pronargs) LOOP
                  IF i > 0 THEN
    argument_string  = argument_string || '','' ;
                  END IF;
    FOR v_record IN SELECT * from pg_type WHERE oid=r_record.proargtypes[i] LOOP 
    argument_string = argument_string || v_record.typname ;
    END LOOP;
   i = i+1;
  END LOOP;
        END IF;
 
 
 exec_string = exec_string || argument_string || '') FROM public;'';
 
    IF exec_string != '''' THEN
    
     RAISE NOTICE ''exec_string is %'', exec_string;
     EXECUTE exec_string;
    END IF;
    END LOOP;
    RETURN 1;
END;
'
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION cleaup_public_on_table_sequence_view()
  RETURNS int4 AS
'
DECLARE
r_record  record;
exec_string text;
BEGIN
    FOR r_record IN SELECT * FROM pg_class WHERE relowner !=''1'' LOOP
    
 exec_string = '''';
    IF (r_record.relkind::char = ''r''::char) THEN
 exec_string = ''REVOKE ALL ON TABLE '' || r_record.relname || '' FROM public'';
    END IF;
    IF (r_record.relkind::char = ''c''::char) THEN
    
 exec_string = ''REVOKE ALL ON TABLE '' || r_record.relname || '' FROM public'';
    
     END IF;
     IF (r_record.relkind::char = ''v''::char) THEN
    
 exec_string = ''REVOKE ALL ON TABLE '' || r_record.relname || '' FROM public'';
    
     END IF;
   
    IF (r_record.relkind::char = ''S''::char) THEN
 
        exec_string = ''REVOKE ALL ON TABLE '' || r_record.relname || '' FROM public'';
    END IF;
 
    IF exec_string != '''' THEN
    
     RAISE NOTICE ''exec_string is %'', exec_string;
 EXECUTE exec_string;
    END IF;
    END LOOP;
    RETURN 1;
END;
'
  LANGUAGE 'plpgsql' VOLATILE;
 

SELECT * FROM cleanup_public_perm_on_function();
SELECT * FROM cleaup_public_on_table_sequence_view();
DROP FUNCTION cleanup_public_perm_on_function();
DROP FUNCTION cleaup_public_on_table_sequence_view();

AVERTISSEMENT CONCERNANT LA CONFIDENTIALITÉ 

Le présent message est à l'usage exclusif du ou des destinataires mentionnés ci-dessus. Son contenu est confidentiel et peut être assujetti au secret professionnel. Si vous avez reçu le présent message par erreur, veuillez nous en aviser immédiatement et le détruire en vous abstenant d'en faire une copie, d'en divulguer le contenu ou d'y donner suite.

CONFIDENTIALITY NOTICE

This communication is intended for the exclusive use of the addressee identified above. Its content is confidential and may contain privileged information. If you have received this communication by error, please notify the sender and delete the message without copying or disclosing it.


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux