Olá,
No artigo de Setembro/2014 eu compartilhei aqui no blog a stored procedure DROP_USER_DISCONNECT_SESSION que tem como objetivo fazer o trabalho de desconectar as sessões correntes de um determinado usuário de banco de dados, bloquear o schema (account lock) enquanto as sessões correntes vão sendo eliminadas, e por fim, dropar o schema. Neste artigo irei compartilhar uma outra stored procedure chamada de CREATE_SYNONYM_GRANT_DML_PRIVS que foi desenvolvida com o intuito de facilitar a criação/exclusão de sinônimos e a concessão/revogação de privilégios de objetos (grant e revoke) aos usuários de bancos de dados. Por questões de segurança, é comum ter aplicações que se conectam no banco de dados à partir de múltiplos usuários. Por exemplo, imagine uma aplicação de RH que contém os objetos de schema de banco de dados criados no schema chamado RH. A aplicação não irá se conectar com o usuário RH, mas sim com um outros usuários criados no banco de dados que possuem privilégios para acessar os objetos do schema RH. Neste caso, esses usuários teriam apenas sinônimos apontando para os objetos do schema RH e privilégios DML (SELECT, INSERT, UPDATE, DELETE). Para exemplificar, teríamos o schema RH que seria o owner (dono) das tabelas, índices, etc e outros usuários como PAULA, MARCELO e JOAO.
Os usuários PAULA, MARCELO e JOAO somente teriam privilégios DML (SELECT, INSERT, UPDATE, DELETE) para acessarem os objetos do schema RH. Por questões de segurança, nenhum privilégio DDL como ALTER, DROP, CREATE seriam concedidos a estes usuários. Para evitar que os objetos tenham que ser qualificados com o nome do schema, sinônimos privados serão criados para cada usuário.
Enfim, para facilitar a concessão de privilégios para usuários que deverão ter acessos DML a objetos de um outro usuário, eu criei a stored procedure CREATE_SYNONYM_GRANT_DML_PRIVS que permitirá que à partir de um OWNER e um USER passados como parâmetros, sejam criados scripts ou executados os comandos de criação de sinônimos e de concessão de privilégios DML sobre todos os objetos do OWNER para o USER.
Irei criar a procedure CREATE_SYNONYM_GRANT_DML_PRIVS como demonstrado abaixo. Para evitar quaisquer problemas com privilégios, irei criá-la no usuário SYS. Se desejar, a criação de um sinônimo e a concessão do privilégio EXECUTE poderá ser concedido a um outro usuário no banco de dados, como por exemplo, o SYSTEM.
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Qua Out 1 08:51:01 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
SQL> create or replace procedure create_synonym_grant_dml_privs
2 (p_owner VARCHAR2,
3 p_user VARCHAR2,
4 p_operation VARCHAR2,
5 p_type VARCHAR2,
6 p_access VARCHAR2 DEFAULT 'A'
7 )
8 as
9 l_word VARCHAR2(10);
10 l_operation VARCHAR2(10);
11 l_type VARCHAR2(10);
12 l_errm VARCHAR2 (2000);
13 l_dml VARCHAR2 (100);
14 l_qtd_owner NUMBER;
15 l_qtd_user NUMBER;
16 invalid_parameter EXCEPTION;
17 invalid_owner EXCEPTION;
18 invalid_user EXCEPTION;
19 invalid_operation EXCEPTION;
20 invalid_type EXCEPTION;
21 invalid_owneruser EXCEPTION;
22 invalid_schema EXCEPTION;
23 BEGIN
24 IF p_owner is NULL or p_user is NULL or p_operation is NULL or p_type is NULL THEN
25 RAISE invalid_parameter;
26 END IF;
27
28 IF UPPER (p_owner) IN ('PUBLIC','SYSTEM','SYS') OR UPPER (p_user) IN ('PUBLIC','SYSTEM','SYS') THEN
29 RAISE invalid_schema;
30 END IF;
31
32 SELECT COUNT (*) INTO l_qtd_owner FROM all_users WHERE username = UPPER (p_owner);
33 IF l_qtd_owner = 0 THEN
34 RAISE invalid_owner;
35 END IF;
36
37 IF UPPER (p_owner) = UPPER (p_user) THEN
38 RAISE invalid_owneruser;
39 END IF;
40
41 l_operation := UPPER (p_operation);
42 l_type := UPPER (p_type);
43
44 IF l_operation NOT IN ('GRANT','REVOKE') THEN
45 RAISE invalid_operation;
46 END IF;
47
48 IF l_type NOT IN ('EXECUTE','SPOOL') THEN
49 RAISE invalid_type;
50 END IF;
51
52 IF l_operation = 'GRANT' THEN
53 l_word := 'TO';
54 IF upper(p_access) = 'R' THEN
55 l_dml := 'SELECT';
56 ELSE
57 l_dml := 'SELECT, INSERT, UPDATE, DELETE';
58 END IF;
59 ELSIF l_operation = 'REVOKE' THEN
60 l_word := 'FROM';
61 l_dml := 'ALL';
62 END IF;
63
64 IF l_type = 'EXECUTE' THEN
65 SELECT COUNT (*) INTO l_qtd_user FROM all_users WHERE username = UPPER (p_user);
66 IF l_qtd_user = 0 THEN
67 RAISE invalid_user;
68 END IF;
69 END IF;
70
71 FOR C1 IN (SELECT OBJECT_NAME, OBJECT_TYPE
72 FROM ALL_OBJECTS
73 WHERE OWNER = UPPER (p_owner)
74 AND OBJECT_TYPE IN ('TABLE')
75 AND OBJECT_NAME NOT LIKE ('MLOG$\_%') ESCAPE '\'
76 AND OBJECT_NAME NOT LIKE ('RUPD$\_%') ESCAPE '\'
77 AND OBJECT_NAME NOT IN
78 (SELECT OBJECT_NAME FROM ALL_OBJECTS
79 WHERE OWNER = UPPER (p_owner)
80 AND OBJECT_TYPE IN ('MATERIALIZED VIEW')
81 UNION ALL
82 SELECT OBJECT_NAME FROM DBA_RECYCLEBIN
83 WHERE OWNER = UPPER (p_owner))
84 UNION ALL
85 SELECT OBJECT_NAME, OBJECT_TYPE
86 FROM ALL_OBJECTS
87 WHERE OWNER = UPPER (p_owner) AND OBJECT_TYPE IN ('MATERIALIZED VIEW')
88 UNION ALL
89 SELECT OBJECT_NAME, OBJECT_TYPE
90 FROM ALL_OBJECTS
91 WHERE OWNER = UPPER (p_owner)
92 AND OBJECT_TYPE IN ('VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'SEQUENCE')
93 ORDER BY OBJECT_TYPE, OBJECT_NAME)
94 LOOP
95 IF C1.OBJECT_TYPE IN ('TABLE') THEN
96 IF l_type = 'EXECUTE' THEN
97 BEGIN
98 EXECUTE IMMEDIATE l_operation||' '||l_dml||' ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'"';
99 EXCEPTION
100 WHEN OTHERS THEN NULL;
101 END;
102 ELSIF l_type = 'SPOOL' THEN
103 dbms_output.put_line (l_operation||' '||l_dml||' ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'";');
104 END IF;
105 ELSIF C1.OBJECT_TYPE IN ('SEQUENCE','VIEW','MATERIALIZED VIEW') THEN
106 IF l_type = 'EXECUTE' THEN
107 BEGIN
108 EXECUTE IMMEDIATE l_operation||' SELECT'||' ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'"';
109 EXCEPTION
110 WHEN OTHERS THEN NULL;
111 END;
112 ELSIF l_type = 'SPOOL' THEN
113 dbms_output.put_line (l_operation||' SELECT'||' ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'";');
114 END IF;
115 ELSIF C1.OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
116 IF l_type = 'EXECUTE' THEN
117 BEGIN
118 EXECUTE IMMEDIATE l_operation||' EXECUTE ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'"';
119 EXCEPTION
120 WHEN OTHERS THEN NULL;
121 END;
122 ELSIF l_type = 'SPOOL' THEN
123 dbms_output.put_line (l_operation||' EXECUTE ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'";');
124 END IF;
125 END IF;
126
127 IF l_operation = 'GRANT' THEN
128 IF l_type = 'EXECUTE' THEN
129 BEGIN
130 EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM "'||upper(p_user)||'"."'||C1.OBJECT_NAME||'" FOR "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'"';
131 EXCEPTION
132 WHEN OTHERS THEN NULL;
133 END;
134 ELSIF l_type = 'SPOOL' THEN
135 dbms_output.put_line ('CREATE OR REPLACE SYNONYM "'||upper(p_user)||'"."'||C1.OBJECT_NAME||'" FOR "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'";');
136 END IF;
137 ELSIF l_operation = 'REVOKE' THEN
138 IF l_type = 'EXECUTE' THEN
139 BEGIN
140 EXECUTE IMMEDIATE 'DROP SYNONYM "'||upper(p_user)||'"."'||C1.OBJECT_NAME||'"';
141 EXCEPTION
142 WHEN OTHERS THEN NULL;
143 END;
144 ELSIF l_type = 'SPOOL' THEN
145 dbms_output.put_line ('DROP SYNONYM "'||upper(p_user)||'"."'||C1.OBJECT_NAME||'";');
146 END IF;
147 END IF;
148 END LOOP;
149 EXCEPTION
150 WHEN invalid_parameter THEN
151 raise_application_error (-20001,'Invalid parameters. ');
152 WHEN invalid_owner THEN
153 raise_application_error (-20001,'The OWNER especified does not exist.');
154 WHEN invalid_user THEN
155 raise_application_error (-20001,'The USER especified does not exist.');
156 WHEN invalid_owneruser THEN
157 raise_application_error (-20001,'The OWNER/USER cannot be the same.');
158 WHEN invalid_schema THEN
159 raise_application_error (-20001,'The OWNER/USER specified is not permitted.');
160 WHEN invalid_operation THEN
161 raise_application_error (-20001,'Invalid operation. Use (GRANT/REVOKE).');
162 WHEN invalid_type THEN
163 raise_application_error (-20001,'Invalid type. Use (EXECUTE/SPOOL).');
164 WHEN OTHERS THEN
165 l_errm := SQLERRM;
166 raise_application_error (-20001, l_errm);
167 END;
168 /
Procedimento criado.
Segue abaixo os parâmetros que precisarão ser informados:
SQL> desc create_synonym_grant_dml_privs
PROCEDURE create_synonym_grant_dml_privs
Nome do Argumento Tipo In/Out Default?
------------------------------ ----------------------- ------ --------
P_OWNER VARCHAR2 IN
P_USER VARCHAR2 IN
P_OPERATION VARCHAR2 IN
P_TYPE VARCHAR2 IN
P_ACCESS VARCHAR2 IN DEFAULT
- P_OWNER: Nome do OWNER dos objetos.
- P_USER: Nome do USER para o qual serão criados/excluídos os sinônimos dos objetos e concedidos/revogados os privilégios sobre os mesmos.
- P_OPERATION: Operação a ser realizada. Possui suas opções:
- GRANT: concede privilégios de acordo com o tipo de cada objeto e cria de sinônimos.
- REVOKE: revoga privilégios concedidos e exclui sinônimos criados.
- P_TYPE: Tipo do resultado da execução da stored procedure. Possui duas opções:
- EXECUTE: executa os comandos de criação de sinônimos e concessão de privilégios.
- SPOOL: gera uma saída (spool) dos comandos de criação de sinônimos e concessão de privilégios na tela.
- P_ACCESS: Intruções DML que serão concedidas. Possui duas opções:
- A: É o valor o default, ou seja, todas as instruções DML (SELECT, INSERT, DELETE , UPDATE) serão concedidas ao usuário.
- R: Concede ao usuário apenas acesso leitura (SELECT).
Vale a pena salientar que somente serão aceitos parâmetros válidos. Por exemplo, NULL não é um valor válido. Deverão ser especificados usuários OWNER/USER existentes e distintos. Usuários como PUBLIC, SYSTEM e SYS não poderão ser utilizados.
Segue abaixo os objetos que a stored procedure irá analisar.
+----------------------------------------------------+
|Objeto | Privilégios |
|----------------------------------------------------|
| Table | Select, Insert, Update, Delete |
| View | Select |
| Materialized View | Select |
| Sequence | Select |
| Procedure | Execute |
| Function | Execute |
| Package | Execute |
+----------------------------------------------------+
OBS: Caso seja passado no parâmetro P_ACCESS o valor 'R', então apenas o privilégio SELECT será concedido ao usuário.
A stored procedure irá gerar os comandos de concessão de privilégios e criação de sinônimos dos objetos do OWNER para o USER de acordo com o tipo do objeto. Para demonstração, irei usar o schema RH como owner e o usuário JOAO como user. Segue abaixo os objetos existentes e de propriedade do schema RH.
SQL> select owner,object_name,object_type from dba_objects where owner='RH';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
RH CUSTOMER TABLE
RH DEPT_VIEW VIEW
RH SEQUENCE_CUSTOMER SEQUENCE
RH FC_CONCATENATE FUNCTION
RH SP_INCREASE_SALARY PROCEDURE
RH MVIEW_EMPLOYEE TABLE
RH MVIEW_EMPLOYEE MATERIALIZED VIEW
RH SALARY TABLE
8 linhas selecionadas.
Irei agora executar a procedure conforme exemplo abaixo:
SQL> set serveroutput on size 1000000
SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT','SPOOL');
GRANT EXECUTE ON "RH"."FC_CONCATENATE" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."FC_CONCATENATE" FOR "RH"."FC_CONCATENATE";
GRANT SELECT ON "RH"."MVIEW_EMPLOYEE" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."MVIEW_EMPLOYEE" FOR "RH"."MVIEW_EMPLOYEE";
GRANT EXECUTE ON "RH"."SP_INCREASE_SALARY" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SP_INCREASE_SALARY" FOR "RH"."SP_INCREASE_SALARY";
GRANT SELECT ON "RH"."SEQUENCE_CUSTOMER" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SEQUENCE_CUSTOMER" FOR "RH"."SEQUENCE_CUSTOMER";
GRANT SELECT, INSERT, UPDATE, DELETE ON "RH"."CUSTOMER" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."CUSTOMER" FOR "RH"."CUSTOMER";
GRANT SELECT, INSERT, UPDATE, DELETE ON "RH"."SALARY" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SALARY" FOR "RH"."SALARY";
GRANT SELECT ON "RH"."DEPT_VIEW" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."DEPT_VIEW" FOR "RH"."DEPT_VIEW";
Procedimento PL/SQL concluído com sucesso.
No exemplo acima eu pedi apenas para gerar os comandos, em vez de executá-los diretamente. Para executar os comandos diretamente bastará apenas informar a opção EXECUTE:
SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT','EXECUTE');
Procedimento PL/SQL concluído com sucesso.
Após a execução da procedure, poderemos verificar abaixo que foram criados sinônimos privados no usuário JOAO e que os privilégios foram concedidos com sucesso.
SQL> select owner,object_name,object_type from dba_objects where owner='JOAO';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
JOAO EXAMPLE SYNONYM
JOAO FC_CONCATENATE SYNONYM
JOAO MVIEW_EMPLOYEE SYNONYM
JOAO SP_INCREASE_SALARY SYNONYM
JOAO SEQUENCE_CUSTOMER SYNONYM
JOAO CUSTOMER SYNONYM
JOAO SALARY SYNONYM
JOAO DEPT_VIEW SYNONYM
8 linhas selecionadas.
SQL> select grantee,owner,table_name,grantor,privilege
2 from dba_tab_privs
3 where grantee='JOAO';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
------------ ------------ -------------------- ------------------ ----------------------
JOAO RH CUSTOMER RH UPDATE
JOAO RH CUSTOMER RH SELECT
JOAO RH CUSTOMER RH INSERT
JOAO RH CUSTOMER RH DELETE
JOAO RH DEPT_VIEW RH SELECT
JOAO RH FC_CONCATENATE RH EXECUTE
JOAO RH MVIEW_EMPLOYEE RH SELECT
JOAO RH SALARY RH UPDATE
JOAO RH SALARY RH SELECT
JOAO RH SALARY RH INSERT
JOAO RH SALARY RH DELETE
JOAO RH SEQUENCE_CUSTOMER RH SELECT
JOAO RH SP_INCREASE_SALARY RH EXECUTE
13 linhas selecionadas.
Para fazer o inverso, ou seja, remover os sinônimos que foram criados no usuário JOAO e revogar os privilégios concedidos, bastará apenas informar a opção REVOKE. No exemplo abaixo irei usar a opção SPOOL para mostrar os comandos.
SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','REVOKE','SPOOL');
REVOKE EXECUTE ON "RH"."FC_CONCATENATE" FROM "JOAO";
DROP SYNONYM "JOAO"."FC_CONCATENATE";
REVOKE SELECT ON "RH"."MVIEW_EMPLOYEE" FROM "JOAO";
DROP SYNONYM "JOAO"."MVIEW_EMPLOYEE";
REVOKE EXECUTE ON "RH"."SP_INCREASE_SALARY" FROM "JOAO";
DROP SYNONYM "JOAO"."SP_INCREASE_SALARY";
REVOKE SELECT ON "RH"."SEQUENCE_CUSTOMER" FROM "JOAO";
DROP SYNONYM "JOAO"."SEQUENCE_CUSTOMER";
REVOKE ALL ON "RH"."CUSTOMER" FROM "JOAO";
DROP SYNONYM "JOAO"."CUSTOMER";
REVOKE ALL ON "RH"."SALARY" FROM "JOAO";
DROP SYNONYM "JOAO"."SALARY";
REVOKE SELECT ON "RH"."DEPT_VIEW" FROM "JOAO";
DROP SYNONYM "JOAO"."DEPT_VIEW";
Caso seja necessário conceder apenas privilégios de leitura para o usuário, bastará apenas informar a opção 'R' como último parâmetro.
SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT','SPOOL','R');
GRANT EXECUTE ON "RH"."FC_CONCATENATE" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."FC_CONCATENATE" FOR "RH"."FC_CONCATENATE";
GRANT SELECT ON "RH"."MVIEW_EMPLOYEE" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."MVIEW_EMPLOYEE" FOR "RH"."MVIEW_EMPLOYEE";
GRANT EXECUTE ON "RH"."SP_INCREASE_SALARY" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SP_INCREASE_SALARY" FOR "RH"."SP_INCREASE_SALARY";
GRANT SELECT ON "RH"."SEQUENCE_CUSTOMER" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SEQUENCE_CUSTOMER" FOR "RH"."SEQUENCE_CUSTOMER";
GRANT SELECT ON "RH"."CUSTOMER" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."CUSTOMER" FOR "RH"."CUSTOMER";
GRANT SELECT ON "RH"."SALARY" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SALARY" FOR "RH"."SALARY";
GRANT SELECT ON "RH"."DEPT_VIEW" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."DEPT_VIEW" FOR "RH"."DEPT_VIEW";
Procedimento PL/SQL concluído com sucesso.
Para finalizar, segue algumas validações que stored procedure realiza.
SQL> exec create_synonym_grant_dml_privs ('RH','MARIA','GRANT','EXECUTE');
BEGIN create_synonym_grant_dml_privs ('RH','MARIA','GRANT','EXECUTE'); END;
*
ERRO na linha 1:
ORA-20001: The USER especified does not exist.
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 155
ORA-06512: em line 1
SQL> exec create_synonym_grant_dml_privs ('RH','RH','GRANT','EXECUTE');
BEGIN create_synonym_grant_dml_privs ('RH','RH','GRANT','EXECUTE'); END;
*
ERRO na linha 1:
ORA-20001: The OWNER/USER cannot be the same.
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 157
ORA-06512: em line 1
SQL> exec create_synonym_grant_dml_privs ('SYSTEM','RH','GRANT','EXECUTE');
BEGIN create_synonym_grant_dml_privs ('SYSTEM','RH','GRANT','EXECUTE'); END;
*
ERRO na linha 1:
ORA-20001: The OWNER/USER specified is not permitted.
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 159
ORA-06512: em line 1
SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANTS','SPOOL');
BEGIN create_synonym_grant_dml_privs ('RH','JOAO','GRANTS','SPOOL'); END;
*
ERRO na linha 1:
ORA-20001: Invalid operation. Use (GRANT/REVOKE).
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 161
ORA-06512: em line 1
SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT','SPOOLS');
BEGIN create_synonym_grant_dml_privs ('RH','JOAO','GRANT','SPOOLS'); END;
*
ERRO na linha 1:
ORA-20001: Invalid type. Use (EXECUTE/SPOOL).
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 163
ORA-06512: em line 1
SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT',NULL);
BEGIN create_synonym_grant_dml_privs ('RH','JOAO','GRANT',NULL); END;
*
ERRO na linha 1:
ORA-20001: Invalid parameters.
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 151
ORA-06512: em line 1
2 comentários:
Muito bom, Legatti!
Olá Franky,
Obrigado pela visita e pelo comentário ;-)
Abraços
Legatti
Postar um comentário