[MySQL][PHP] Projekt systemu ACL Opcje

[mentoos]

Chciałbym napisać swój system ACL. Zastanawiam się jak go zaprojektować by był elastyczny. Bazę danych chcę zaprojektować tak:

acl_resources - zasoby
id | resource

acl_roles -> role
id | role_name

acl_permissions - tabela wiążąca 2 poprzednie, w permission określany jest dostęp lub nie
id | resource_id | role_id | permission

i jeszcze tabela users normalnie wykorzystywana do logowania

Co powinien mieć dobry system ACL?
Czy powinien mieć też możliwość przypisywania uprawnień konkretnemu użytkownikowi?

Ten post edytował mentoos 19.08.2010, 11:41:33

[phpion]

Poniżej zrzuty tabel (w kolejności alfabetycznej, nie zakładania - powycinałem ze zrzutu bazy):

[SQL] pobierz, plaintext 
CREATE TABLE acl_resource_groups (
    id integer NOT NULL,
    name character varying(50) NOT NULL,
    description text,
    priority smallint DEFAULT 0 NOT NULL
);
CREATE TABLE acl_resource_groups_acl_resources (
    acl_resource_group_id integer NOT NULL,
    acl_resource_id integer NOT NULL,
    permission BOOLEAN DEFAULT false NOT NULL
);
 
CREATE TABLE acl_resource_groups_acl_roles (
    acl_resource_group_id integer NOT NULL,
    acl_role_id integer NOT NULL
);
 
CREATE TABLE acl_resource_groups_auth_users (
    acl_resource_group_id integer NOT NULL,
    auth_user_id integer NOT NULL
);
 
CREATE TABLE acl_resource_namespaces (
    id integer NOT NULL,
    name character varying(255) NOT NULL
);
 
CREATE TABLE acl_resources (
    id integer NOT NULL,
    name character varying(255) NOT NULL,
    acl_resource_namespace_id integer NOT NULL,
    permission BOOLEAN DEFAULT false NOT NULL
);
 
CREATE TABLE acl_resources_acl_roles (
    acl_resource_id integer NOT NULL,
    acl_role_id integer NOT NULL,
    permission BOOLEAN DEFAULT false NOT NULL
);
 
CREATE TABLE acl_resources_auth_users (
    acl_resource_id integer NOT NULL,
    auth_user_id integer NOT NULL,
    permission BOOLEAN DEFAULT false NOT NULL
);
 
CREATE TABLE acl_roles (
    id integer NOT NULL,
    name character varying(25) NOT NULL,
    priority smallint DEFAULT 0 NOT NULL
);
 
CREATE TABLE acl_roles_auth_users (
    auth_user_id integer NOT NULL,
    acl_role_id integer NOT NULL
);
[SQL] pobierz, plaintext 

Do tego wspomniana funkcja, a do niej potrzebny nowy typ danych:

[SQL] pobierz, plaintext 
CREATE TYPE acl_user_resurces_permissions AS (
	resource_id integer,
	permission BOOLEAN,
	level integer
);
 
CREATE FUNCTION f_get_acl_user_resources_permissions(_user_id integer) RETURNS SETOF acl_user_resurces_permissions
    AS $$
DECLARE
	row record;
BEGIN
	FOR row IN
		SELECT
			DISTINCT ON (resource_id) resource_id, permission, level
		FROM
			(
			-- Pobranie uprawnien przypisanych bezposrednio do uzytkownika (priorytet = 5)
			(SELECT
				acl_resource_id AS resource_id, permission AS permission, 5 AS level
			FROM
				acl_resources_auth_users
			WHERE
				auth_user_id=_user_id)
			UNION
			-- Pobranie uprawnien przypisanych do grupy ACL uzytkownika (priorytet = 4)
			(SELECT
				DISTINCT ON (resource_id) b.acl_resource_id AS resource_id, permission AS permission, 4 AS level
			FROM
				acl_resource_groups_auth_users AS a
				JOIN acl_resource_groups_acl_resources b USING (acl_resource_group_id)
				JOIN acl_resource_groups c ON (c.id = b.acl_resource_group_id)
			WHERE
				a.auth_user_id=_user_id
			GROUP BY
				resource_id, permission, level, priority
			ORDER BY
				resource_id ASC, c.priority DESC)
			UNION
			-- Pobranie uprawnien przypisanych do rol uzytkownika (priorytet = 3)
			(SELECT
				DISTINCT ON (resource_id) b.acl_resource_id AS resource_id, permission AS permission, 3 AS level
			FROM
				acl_roles_auth_users AS a
				JOIN acl_resources_acl_roles b USING (acl_role_id)
				JOIN acl_roles c ON (c.id = b.acl_role_id)
			WHERE
				a.auth_user_id=_user_id
			GROUP BY
				resource_id, permission, level, priority
			ORDER BY
				resource_id ASC, c.priority DESC)
			UNION
			-- Pobranie uprawnien przypisanych do roli ACL uzytkownika (priorytet = 2)
			(SELECT
				DISTINCT ON (resource_id) c.acl_resource_id AS resource_id, permission AS permission, 2 AS level
			FROM
				acl_roles_auth_users AS a
				JOIN acl_resource_groups_acl_roles AS b USING (acl_role_id)
				JOIN acl_resource_groups_acl_resources AS c USING (acl_resource_group_id )
				JOIN acl_resource_groups d ON (d.id = b.acl_resource_group_id)
			WHERE
				a.auth_user_id=_user_id
			GROUP BY
				resource_id, permission, level, priority
			ORDER BY
				resource_id ASC, d.priority DESC)
			UNION
			-- Pobranie uprawnien przypisanych bezposrednio do zasobow (priorytet = 1)
			(SELECT
				id AS resource_id, permission AS permission, 1 AS level
			FROM
				acl_resources)
			) AS t
		GROUP BY
			resource_id, permission, level
		ORDER BY
			resource_id ASC, level DESC
		LOOP
		RETURN NEXT row;
	END LOOP;
END;
$$
    LANGUAGE plpgsql;
[SQL] pobierz, plaintext 

(IMG:style_emoticons/default/smile.gif)

PS: Okazuje się jednak, że działa to na 5, a nie 3 poziomach (IMG:style_emoticons/default/smile.gif) hehehe.