[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2580] trunk/sql/modules
- Subject: SF.net SVN: ledger-smb:[2580] trunk/sql/modules
- From: ..hidden..
- Date: Wed, 29 Apr 2009 00:44:39 +0000
Revision: 2580
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2580&view=rev
Author: einhverfr
Date: 2009-04-29 00:44:39 +0000 (Wed, 29 Apr 2009)
Log Message:
-----------
Second stage review is complete for user management, and roles/permissions are in place
Modified Paths:
--------------
trunk/sql/modules/Roles.sql
trunk/sql/modules/admin.sql
Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql 2009-04-28 05:37:13 UTC (rev 2579)
+++ trunk/sql/modules/Roles.sql 2009-04-29 00:44:39 UTC (rev 2580)
@@ -1347,8 +1347,6 @@
CREATE ROLE "lsmb_<?lsmb dbname ?>__create_part_translation"
WITH INHERIT NOLOGIN;
--- TODO add db permissions
-
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (77, 'allow', 'lsmb_<?lsmb dbname ?>__create_part_translation');
INSERT INTO menu_acl (node_id, acl_type, role_name)
@@ -1362,8 +1360,6 @@
CREATE ROLE "lsmb_<?lsmb dbname ?>__create_project_translation"
WITH INHERIT NOLOGIN;
--- TODO add db permissions
-
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (98, 'allow', 'lsmb_<?lsmb dbname ?>__create_project_translation');
INSERT INTO menu_acl (node_id, acl_type, role_name)
@@ -1378,10 +1374,33 @@
"lsmb_<?lsmb dbname ?>__create_part_translation",
"lsmb_<?lsmb dbname ?>__create_project_translation";
+CREATE ROLE "lsmb_<?lsmb dbname ?>__manage_users"
+WITH INHERIT NOLOGIN;
+
+GRANT EXECUTE ON FUNCTION admin__add_user_to_role(TEXT, TEXT)
+TO "lsmb_<?lsmb dbname ?>__manage_users";
+GRANT EXECUTE ON FUNCTION admin__remove_user_from_role(TEXT, TEXT)
+TO "lsmb_<?lsmb dbname ?>__manage_users";
+GRANT EXECUTE ON FUNCTION admin__add_function_to_group(TEXT, TEXT)
+TO "lsmb_<?lsmb dbname ?>__manage_users";
+GRANT EXECUTE ON FUNCTION admin__remove_function_from_group(text, text)
+TO "lsmb_<?lsmb dbname ?>__manage_users";
+GRANT EXECUTE ON FUNCTION admin__get_roles_for_user(INT)
+TO "lsmb_<?lsmb dbname ?>__manage_users";
+GRANT EXECUTE ON FUNCTION admin__save_user(int, INT, text, TEXT)
+TO "lsmb_<?lsmb dbname ?>__manage_users";
+GRANT EXECUTE ON FUNCTION admin__create_group(TEXT)
+TO "lsmb_<?lsmb dbname ?>__manage_users";
+GRANT EXECUTE ON FUNCTION admin__delete_user(text)
+TO "lsmb_<?lsmb dbname ?>__manage_users";
+GRANT EXECUTE ON FUNCTION admin__list_roles(text)
+TO "lsmb_<?lsmb dbname ?>__manage_users";
+GRANT EXECUTE ON FUNCTION admin__delete_group(text)
+TO "lsmb_<?lsmb dbname ?>__manage_users";
+
+-- Grants to all users;
GRANT SELECT ON custom_field_catalog TO public;
GRANT SELECT ON custom_table_catalog TO public;
-
--- Grants to all users;
GRANT ALL ON defaults TO public;
GRANT ALL ON "session" TO public;
GRANT ALL ON session_session_id_seq TO PUBLIC;
Modified: trunk/sql/modules/admin.sql
===================================================================
--- trunk/sql/modules/admin.sql 2009-04-28 05:37:13 UTC (rev 2579)
+++ trunk/sql/modules/admin.sql 2009-04-29 00:44:39 UTC (rev 2580)
@@ -44,8 +44,10 @@
return 1;
END;
-$$ language 'plpgsql';
+$$ language 'plpgsql' security definer;
+REVOKE EXECUTE ON FUNCTION admin__add_user_to_role(TEXT, TEXT) FROM PUBLIC;
+
CREATE OR REPLACE FUNCTION admin__remove_user_from_role(in_user TEXT, in_role TEXT) returns INT AS $$
declare
@@ -74,8 +76,10 @@
return 1;
END;
-$$ language 'plpgsql';
+$$ language 'plpgsql' SECURITY DEFINER;
+REVOKE EXECUTE ON FUNCTION admin__remove_user_from_role(TEXT, TEXT) FROM PUBLIC;
+
CREATE OR REPLACE FUNCTION admin__add_function_to_group(in_func TEXT, in_role TEXT) returns INT AS $$
declare
@@ -104,8 +108,10 @@
return 1;
END;
-$$ language 'plpgsql';
+$$ language 'plpgsql' SECURITY DEFINER;
+REVOKE EXECUTE ON admin__add_function_to_group(TEXT, TEXT) FROM PUBIC;
+
CREATE OR REPLACE FUNCTION admin__remove_function_from_group(in_func TEXT, in_role TEXT) returns INT AS $$
declare
@@ -135,10 +141,13 @@
END;
-$$ language 'plpgsql';
+$$ language 'plpgsql' SECURITY DEFINER;
+REVOKE EXECUTE ON FUNCTION admin__remove_function_from_group(text, text)
+FROM public;
+
CREATE OR REPLACE FUNCTION admin__add_table_to_group(in_table TEXT, in_role TEXT, in_perm TEXT) returns INT AS $$
-
+ -- Do we need this table stuff at the moment? CT
declare
stmt TEXT;
a_role name;
@@ -175,7 +184,7 @@
$$ language 'plpgsql';
CREATE OR REPLACE FUNCTION admin__remove_table_from_group(in_table TEXT, in_role TEXT) returns INT AS $$
-
+ -- do we need this table stuff at the moment? CT
declare
stmt TEXT;
a_role name;
@@ -255,9 +264,11 @@
RETURN;
end;
-$$ language 'plpgsql';
+$$ language 'plpgsql' SECURITY DEFINER;
-CREATE OR REPLACE FUNCTION admin__check_my_expiration()
+REVOKE EXECUTE ON FUNCTION admin__get_roles_for_user(in_user_id INT) from PUBLIC;
+
+CREATE OR REPLACE FUNCTION user__check_my_expiration()
returns interval as
$$
DECLARE
@@ -270,7 +281,7 @@
end;
$$ language plpgsql security definer;
-CREATE OR REPLACE FUNCTION admin__change_password(in_new_password text)
+CREATE OR REPLACE FUNCTION user__change_password(in_new_password text)
returns int as
$$
DECLARE
@@ -357,19 +368,27 @@
END;
$$ language 'plpgsql' SECURITY DEFINER;
+REVOKE EXECUTE ON FUNCTION admin__save_user(
+ in_id int,
+ in_entity_id INT,
+ in_username text,
+ in_password TEXT
+) FROM public;
+
create view role_view as
select * from pg_auth_members m join pg_authid a ON (m.roleid = a.oid);
-create or replace function admin__is_group(in_dbname TEXT, in_group_name text) returns bool as $$
-
+create or replace function admin__is_group(in_group_name text) returns bool as $$
+ -- This needs some work. CT
DECLARE
- existant_role role_view;
+ existant_role pg_roles;
stmt text;
BEGIN
- select * into role_view from role_view where rolname = in_group_name;
+ select * into existant_role from pg_roles
+ where rolname = in_group_name AND rolcanlogin is false;
if not found then
return 'f'::bool;
@@ -381,20 +400,23 @@
$$ language 'plpgsql';
-CREATE OR REPLACE FUNCTION admin__create_group(in_group_name TEXT, in_dbname TEXT) RETURNS int as $$
+CREATE OR REPLACE FUNCTION admin__create_group(in_group_name TEXT) RETURNS int as $$
DECLARE
stmt text;
-
+ t_dbname text;
BEGIN
- stmt := 'create role lsmb_'|| quote_ident(in_dbname || '__' || in_group_name);
+ t_dbname := current_database();
+ stmt := 'create role lsmb_'|| quote_ident(t_dbname || '__' || in_group_name);
execute stmt;
return 1;
END;
$$ language 'plpgsql' SECURITY DEFINER;
+REVOKE EXECUTE ON FUNCTION admin__create_group(TEXT) FROM PUBLIC;
+
CREATE OR REPLACE FUNCTION admin__delete_user(in_username TEXT) returns INT as $$
DECLARE
@@ -413,7 +435,6 @@
execute stmt;
-- also gets user_connection
- delete from users where id = a_user.id;
delete from entity where id = a_user.entity_id;
END IF;
@@ -421,8 +442,11 @@
$$ language 'plpgsql' SECURITY DEFINER;
+REVOKE EXECUTE ON FUNCTION admin__delete_user(in_username TEXT) from public;
+
comment on function admin__delete_user(text) is $$
- Drops the provided user, as well as deletes the entity and user configuration data.
+ Drops the provided user, as well as deletes the user configuration data.
+It leaves the entity and person references.
$$;
CREATE OR REPLACE FUNCTION admin__delete_group (in_group_name TEXT) returns bool as $$
@@ -447,7 +471,9 @@
END;
$$ language 'plpgsql' SECURITY DEFINER;
-comment on function admin__delete_group(text,text) IS $$
+REVOKE EXECUTE on function admin__delete_group(text) from public;
+
+comment on function admin__delete_group(text) IS $$
Deletes the input group from the database. Not designed to be used to
remove a login-capable user.
$$;
@@ -472,6 +498,8 @@
END;
$$ LANGUAGE PLPGSQL SECURITY DEFINER;
+REVOKE execute on function admin__list_roles(in_username text) from public;
+
-- TODO: Add admin user
--CREATE OR REPLACE FUNCTION admin_audit_log () returns int as $$
@@ -525,7 +553,8 @@
from
pg_roles
where
- rolname ~ ('^lsmb_' || t_dbname) -- TODO need to suffix with __
+ rolname ~ ('^lsmb_' || t_dbname || '__')
+ and rolcanlogin is false;
order by rolname ASC
LOOP
RETURN NEXT v_rol.rolname;
@@ -533,7 +562,7 @@
END;
$$ language plpgsql;
-create or replace function admin__user_preferences (in_user int) returns setof user_preference as $$
+create or replace function user__get_preferences (in_user int) returns setof user_preference as $$
declare
v_row user_preference;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.