Menu de navegação

[Edgar Rombesso RisolaParticipante]

Galera…
Tenho um database 10g que tem 6 Schemas.
Foi criado um usuário x que deve ter permissão em todos objetos do schema 1 e 3.
Como proceder para que esse usuário tenha acesso full em apenas esses dois schemas?

[José Laurindo ChiappaModerador]

Opa, blz ? Então, pode consultar na Documentação que vc vai ver que Não Existe um comando de dar permissões/grants em todos os objetos de um schema de uma vez, vc TEM que executar um GRANT pra cada objeto de cada schema….
Obviamente, pra vc não ficar fazendo serviço de louco manso e escrever na mão esses comandos todinhos (o que seria Inviável de tão trabalhoso), o que nós fazemos é usar a técnica amiga do DBA e escrever um programa que já gere esses comandos todos : pra isso, tem quem goste de usar outras linguagens de programação, tem que use o EXECUTE IMMEDIATE dentro de um LOOP, tem quem goste de usar o UTL_FILE pra gerar um arquivo-texto com os comandos todos, N opções…
Essas opções FUNCIONAM, claro, mas pessoalmente minha preferência é executar o SELECT no dicionário de dados numa tool que permita gravar o resultado do SELECT num arquivo-texto E executar os comandos contidos nesse arquivo-texto : pode ser TOAD, pode ser SQL DEVELOPER, há n tools que permitem isso….
A minha preferida é sqlplus, eis um EXEMPLO (não completo, é só um EXEMPLO mas mostra a idéia) :

C:Usersjlchiappa>type gera_grants.sql
set lines 300
set pages 0
spool grant.sql
select ‘grant ‘ ||
case
when object_type in (‘SEQUENCE’, ‘VIEW’) then ‘SELECT’
when object_type = ‘TABLE’ then ‘SELECT,INSERT,UPDATE,DELETE’
when object_type in (‘PACKAGE’,’FUNCTION’,’PROCEDURE’) then ‘EXECUTE’
end ||
‘ ON ‘ || owner || ‘.’ || object_name || ‘ TO schema1;’
from dba_objects where owner=’CHIAPPA’ and object_type in (‘TABLE’,’VIEW’,’PACKAGE’, ‘PROCEDURE’, ‘FUNCTION’, ‘SEQUENCE’);
spool off
exit
/

C:Usersjlchiappa>

==> executo o script de geração :

C:Usersjlchiappa>sqlplus CHIAPPA/senha@wteste @gera_grants.sql
…….

==> graças ao comando EXIT no final, ele saiu sozinho após a geração, veja que a lista de comandos necessárias foi gerada pra mim :

C:Usersjlchiappa>dir grant.sql
O volume na unidade C não tem nome.
O Número de Série do Volume é 5C20-573C

Pasta de C:Usersjlchiappa

12/09/2018 10:06 352.163 grant.sql
1 arquivo(s) 352.163 bytes
0 pasta(s) 103.507.636.224 bytes disponíveis

C:Usersjlchiappa>type grant.sql
grant SELECT,INSERT,UPDATE,DELETE ON CHIAPPA.PRO_HISTORICO TO schema1;
grant SELECT,INSERT,UPDATE,DELETE ON CHIAPPA.PRO_MOTIVO TO schema1;
grant SELECT,INSERT,UPDATE,DELETE ON CHIAPPA.PRO_ORIGEM TO schema1;
grant SELECT,INSERT,UPDATE,DELETE ON CHIAPPA.PRO_STATUS TO schema1;
grant SELECT,INSERT,UPDATE,DELETE ON CHIAPPA.PRO_TABLET_PROC TO schema1;
grant SELECT,INSERT,UPDATE,DELETE ON CHIAPPA.REC_BAIXA TO schema1;
…..

Prontinho : agora é só conectar como o usuário dono das tabelas (OU como um usuário apropriadamente permissionado) e executar o script gerado grants.sql com a tool da minha preferência que permita executar lista de comandos contida num arquivo-texto, okdoc ?? Eu iria de sqlplus, conectando como o usuário correto e executando via @grant.sql …
É isso, simples….

[]s

Chiappa

[José Laurindo ChiappaModerador]

Ah, dois detalhes adicionais :

1. normalmente é mais de um usuário do banco que precisa receber um determinado conjunto de GRANTs : assim, muitas vezes o que se faz é cria uma ROLE, associar a ROLE com os usuários desejados e dar os privilégiso desejados PARA A ROLE : automaticamente, quando vc fazer um GRANT pribilégio TO ROLE, os usuários TODOS que estão assignados para aquela ROLE herdam o privilégio…
A vantagem principal das ROLEs é que quando vc precisar criar um novo usuário que deve ter os mesmos privs já dados para a role X, basta vc associar a role X para o novo usuário e pronto…
A desvantagem principal das ROLEs é que elas são DESLIGADAS/DESCONSIDERADAS para programas PL/SQL armazenados no banco que foram criados com DEFINER RIGHTS (AUTHID DEFINER, que é o Default) – roles só funcionam ok para stored PL/SQLs criados com AUTHID CURRENT_USER

2. óbvio, sempre que vc criar um novo usuário ou um novo objeto que precisa ser permissionado, os GRANTs específicos para esse novo objeto TEM que ser refeitos (para a role ou para os usuários diretos) – idealmente vc teria um conjunto de scripts que fazem os GRANTs necessários à mão, e os executaria logo após criar o novo usuário….
Em tese é possível Automatizar isso com uma trigger de DDL (http://www.oaktable.net/content/automatically-granting-privileges-newly-created-tables exemplifica) mas eu prefiro a opção de scripts…

[]s

Chiappa

[Autor]

Posts