Bgames
Olá visitante! Seja bem vindo ao nosso fórum!

Para você ter total acesso ao nosso fórum é preciso que você se cadastre.

Cadastre-se Aqui!


PARA VER LINKS E IMAGENS É PRECISO SE REGISTRAR!

[ SQL Query ] Redefinição Rbac Max level 4 GM

Ver o tópico anterior Ver o tópico seguinte Ir em baixo

[ SQL Query ] Redefinição Rbac Max level 4 GM

Mensagem por windownsserversbr em Dom 19 Abr 2015 - 0:34

Olá %s

Código:
ALTER TABLE account ENGINE=InnoDB;

DELETE FROM `account_access` WHERE `id` NOT IN (SELECT `id` FROM `account`);

DROP TABLE IF EXISTS `rbac_account_permissions`;
DROP TABLE IF EXISTS `rbac_account_roles`;
DROP TABLE IF EXISTS `rbac_account_groups`;
DROP TABLE IF EXISTS `rbac_role_permissions`;
DROP TABLE IF EXISTS `rbac_group_roles`;
DROP TABLE IF EXISTS `rbac_security_level_groups`;
DROP TABLE IF EXISTS `rbac_permissions`;
DROP TABLE IF EXISTS `rbac_roles`;
DROP TABLE IF EXISTS `rbac_groups`;

CREATE TABLE IF NOT EXISTS `rbac_groups` (
  `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Group id',
  `name` varchar(50) NOT NULL COMMENT 'Group name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Group List';

CREATE TABLE IF NOT EXISTS `rbac_roles` (
  `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Role id',
  `name` varchar(50) NOT NULL COMMENT 'Role name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Roles List';

CREATE TABLE IF NOT EXISTS `rbac_permissions` (
  `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Permission id',
  `name` varchar(100) NOT NULL COMMENT 'Permission name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Permission List';

CREATE TABLE IF NOT EXISTS `rbac_group_roles` (
  `groupId` int(10) unsigned NOT NULL COMMENT 'group id',
  `roleId` int(10) unsigned NOT NULL COMMENT 'Role id',
  PRIMARY KEY (`groupId`, `roleId`),
  CONSTRAINT `fk__rbac_group_roles__rbac_roles`
    FOREIGN KEY (`roleId`) REFERENCES `rbac_roles`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk__rbac_group_roles__rbac_groups`
    FOREIGN KEY (`groupId`) REFERENCES `rbac_groups`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Group Role relation';

CREATE TABLE IF NOT EXISTS `rbac_role_permissions` (
  `roleId` int(10) unsigned NOT NULL COMMENT 'Role id',
  `permissionId` int(10) unsigned NOT NULL COMMENT 'Permission id',
  PRIMARY KEY (`roleId`, `permissionId`),
  CONSTRAINT `fk__role_permissions__rbac_roles`
    FOREIGN KEY (`roleId`) REFERENCES `rbac_roles`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk__role_permissions__rbac_permissions`
    FOREIGN KEY (`permissionId`) REFERENCES `rbac_permissions`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Role Permission relation';

CREATE TABLE IF NOT EXISTS `rbac_security_level_groups` (
  `secId` int(10) unsigned NOT NULL COMMENT 'Security Level id',
  `groupId` int(10) unsigned NOT NULL COMMENT 'group id',
  PRIMARY KEY (`secId`, `groupId`),
  CONSTRAINT `fk__rbac_security_level_groups__rbac_groups`
    FOREIGN KEY (`groupId`) REFERENCES `rbac_groups`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Default groups to assign when an account is set gm level';

CREATE TABLE IF NOT EXISTS `rbac_account_groups` (
  `accountId` int(10) unsigned NOT NULL COMMENT 'Account id',
  `groupId` int(10) unsigned NOT NULL COMMENT 'Group id',
  `realmId` int(11) NOT NULL DEFAULT '-1' COMMENT 'Realm Id, -1 means all',
  PRIMARY KEY (`accountId`, `groupId`, `realmId`),
  CONSTRAINT `fk__rbac_account_groups__account`
    FOREIGN KEY (`accountId`) REFERENCES `account`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk__rbac_account_groups__rbac_groups`
    FOREIGN KEY (`groupId`) REFERENCES `rbac_groups`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Account-Group relation';

CREATE TABLE IF NOT EXISTS `rbac_account_roles` (
  `accountId` int(10) unsigned NOT NULL COMMENT 'Account id',
  `roleId` int(10) unsigned NOT NULL COMMENT 'Role id',
  `granted` tinyint(1) NOT NULL default 1 COMMENT 'Granted = 1, Denied = 0',
  `realmId` int(11) NOT NULL DEFAULT '-1' COMMENT 'Realm Id, -1 means all',
  PRIMARY KEY (`accountId`, `roleId`, `realmId`),
  CONSTRAINT `fk__rbac_account_roles__account`
    FOREIGN KEY (`accountId`) REFERENCES `account`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk__rbac_account_roles__rbac_roles`
    FOREIGN KEY (`roleId`) REFERENCES `rbac_roles`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Account-Role relation';

CREATE TABLE IF NOT EXISTS `rbac_account_permissions` (
  `accountId` int(10) unsigned NOT NULL COMMENT 'Account id',
  `permissionId` int(10) unsigned NOT NULL COMMENT 'Permission id',
  `granted` tinyint(1) NOT NULL default 1 COMMENT 'Granted = 1, Denied = 0',
  `realmId` int(11) NOT NULL DEFAULT '-1' COMMENT 'Realm Id, -1 means all',
  PRIMARY KEY (`accountId`, `permissionId`, `realmId`),
  CONSTRAINT `fk__rbac_account_permissions__account`
    FOREIGN KEY (`accountId`) REFERENCES `account`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk__rbac_account_roles__rbac_permissions`
    FOREIGN KEY (`permissionId`) REFERENCES `rbac_permissions`(`id`)
    ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Account-Permission relation';

DELETE FROM `rbac_permissions` WHERE `id` BETWEEN 1 AND 10;
INSERT INTO `rbac_permissions` (`id`, `name`) VALUES
(1, 'Instant logout'),
(2, 'Skip Queue'),
(3, 'Join Normal Battleground'),
(4, 'Join Random Battleground'),
(5, 'Join Arenas'),
(6, 'Join Dungeon Finder'),
(7, 'Player Commands (Temporal till commands moved to rbac)'),
(8, 'Moderator Commands (Temporal till commands moved to rbac)'),
(9, 'GameMaster Commands (Temporal till commands moved to rbac)'),
(10, 'Administrator Commands (Temporal till commands moved to rbac)');

DELETE FROM `rbac_roles` WHERE `id` BETWEEN 1 AND 7;
INSERT INTO `rbac_roles` (`id`, `name`) VALUES
(1, 'Player Commands'),
(2, 'Moderator Commands'),
(3, 'GameMaster Commands'),
(4, 'Administrator Commands'),
(5, 'Quick Login/Logout'),
(6, 'Use Battleground/Arenas'),
(7, 'Use Dungeon Finder');

DELETE FROM `rbac_groups` WHERE `id` BETWEEN 1 AND 4;
INSERT INTO `rbac_groups` (`id`, `name`) VALUES
(1, 'Player'),
(2, 'Moderator'),
(3, 'GameMaster'),
(4, 'Administrator');

DELETE FROM `rbac_role_permissions` WHERE `roleId` BETWEEN 1 AND 7;
INSERT INTO `rbac_role_permissions` (`roleId`, `permissionId`) VALUES
(5, 1),
(5, 2),
(6, 3),
(6, 4),
(6, 5),
(7, 6),
(1, 7),
(2, 8),
(3, 9),
(4, 10);

DELETE FROM `rbac_group_roles` WHERE `groupId` BETWEEN 1 AND 4;
INSERT INTO `rbac_group_roles` (`groupId`, `roleId`) VALUES
(1, 1),
(1, 6),
(1, 7),
(2, 2),
(2, 5),
(3, 3),
(4, 4);

TRUNCATE `rbac_account_groups`;
INSERT INTO `rbac_account_groups` (`accountId`, `groupId`, `realmId`) SELECT `id`, 1, -1 FROM `account`; -- Add Player group to all accounts
INSERT INTO `rbac_account_groups` (`accountId`, `groupId`, `realmId`) SELECT `id`, 2, `RealmID` FROM `account_access` WHERE `gmlevel` > 0; -- Add Moderator group to all Moderator or higher GM level
INSERT INTO `rbac_account_groups` (`accountId`, `groupId`, `realmId`) SELECT `id`, 3, `RealmID` FROM `account_access` WHERE `gmlevel` > 1; -- Add GameMaster group to all GameMasters or higher GM level
INSERT INTO `rbac_account_groups` (`accountId`, `groupId`, `realmId`) SELECT `id`, 4, `RealmID` FROM `account_access` WHERE `gmlevel` > 2; -- Add Administrator group to all Administrators

TRUNCATE `rbac_security_level_groups`;
INSERT INTO `rbac_security_level_groups` (`secId`, `groupId`) VALUES
(0, 1),
(1, 1),
(1, 2),
(2, 1),
(2, 2),
(2, 3),
(3, 1),
(3, 2),
(3, 3),
(3, 4);

Para executar esse script você precisa ter sqlyog ou navicat e executar na tabela auth

obrigado

se gostaram +repp

windownsserversbr


Mensagens : 233
Pontos : 739

Ver perfil do usuário http://windownsservers.jimdo.com

Voltar ao Topo Ir em baixo

Re: [ SQL Query ] Redefinição Rbac Max level 4 GM

Mensagem por WoWCore em Dom 19 Abr 2015 - 7:26

Informa a revisão pra quem for usar, porque vai estragar a database se usar em uma versão diferente.

WoWCore


Mensagens : 619
Pontos : 2179
Evolução : 5


Ver perfil do usuário http://www.mthsena.info

Voltar ao Topo Ir em baixo

Re: [ SQL Query ] Redefinição Rbac Max level 4 GM

Mensagem por windownsserversbr em Dom 19 Abr 2015 - 8:33

Man eu não tenho muita certeza mais acho que ate a 58 funciona por as colunas ser iguais

windownsserversbr


Mensagens : 233
Pontos : 739

Ver perfil do usuário http://windownsservers.jimdo.com

Voltar ao Topo Ir em baixo

Re: [ SQL Query ] Redefinição Rbac Max level 4 GM

Mensagem por derbirt em Dom 19 Abr 2015 - 16:54

vou nem ariscar rsrs

derbirt


Mensagens : 147
Pontos : 620

Ver perfil do usuário http://www.wowpandora.com.br

Voltar ao Topo Ir em baixo

Re: [ SQL Query ] Redefinição Rbac Max level 4 GM

Mensagem por thiejana em Qua 27 Maio 2015 - 19:36

Testei em uma data TDB 58 e funcionou de boa parabéns pela Querie e Vallew pela ajuda. +rep

thiejana


Mensagens : 98
Pontos : 350

Ver perfil do usuário http://www.megawow.com.br

Voltar ao Topo Ir em baixo

Re: [ SQL Query ] Redefinição Rbac Max level 4 GM

Mensagem por Conteúdo patrocinado Hoje à(s) 20:55


Conteúdo patrocinado


Voltar ao Topo Ir em baixo

Ver o tópico anterior Ver o tópico seguinte Voltar ao Topo


 
Permissão deste fórum:
Você não pode responder aos tópicos neste fórum