Rodrigo Silva e Silva

Drop ACL Oracle

Publicado em 15 de abril de 2019 by rodrigosilvaesilva

Verificando ACL

SQL>
SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
    JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)
UNION ALL
SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
    JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);


PRINCIPA HOST  LOWER_PORT UPPER_PORT ACL                    PRIVILEGE    PRIVILE
-------- ----- ---------- ---------- ---------------------- ------------ -------
CPD      *                           /sys/acls/ACL_TI.xml  connect      GRANTED
CPD      *                           /sys/acls/ACL_TI.xml  connect      GRANTED
CPD      *                           /sys/acls/ACL_TI.xml  resolve      GRANTED
CPD      *                           /sys/acls/ACL_TI.xml  resolve      GRANTED

SQL>

SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,

DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS

FROM DBA_NETWORK_ACLS

JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)

UNION ALL

SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,

DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS

FROM DBA_NETWORK_ACLS

JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);

PRINCIPA HOST LOWER_PORT UPPER_PORT ACL PRIVILEGE PRIVILE

-------- ----- ---------- ---------- ---------------------- ------------ -------

CPD * /sys/acls/ACL_TI.xml connect GRANTED

CPD * /sys/acls/ACL_TI.xml resolve GRANTED

Dropando ACL

SQL>
BEGIN
   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
      acl => '/sys/acls/ACL_TI.xml');
   END;
/

PL/SQL procedure successfully completed.

SQL>

BEGIN

DBMS_NETWORK_ACL_ADMIN.DROP_ACL(

acl => '/sys/acls/ACL_TI.xml');

END;

PL/SQL procedure successfully completed.

Verificando ACL

SQL>
SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
    JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)
UNION ALL
SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
  9  JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);

no rows selected

SQL>

SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,

DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS

FROM DBA_NETWORK_ACLS

JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)

UNION ALL

SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,

DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS

FROM DBA_NETWORK_ACLS

9 JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);

no rows selected

Create ACL Oracle

Publicado em 15 de abril de 2019 by rodrigosilvaesilva

Create ACL Oracle

begin
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl => 'ACL_TEST.xml',
    description => 'Allow acl_viacep',
    principal => 'TI',
    is_grant => true,
    privilege => 'connect'
    );
  
  dbms_network_acl_admin.add_privilege (
  acl => 'ACL_TEST.xml',
  principal => 'TI',
  is_grant => true,
  privilege => 'resolve'
  );
 
  dbms_network_acl_admin.assign_acl(
  acl  => 'ACL_TEST.xml',
  host => '*'
  );
  commit;
end;
/

PL/SQL procedure successfully completed.

begin

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(

acl => 'ACL_TEST.xml',

description => 'Allow acl_viacep',

principal => 'TI',

is_grant => true,

privilege => 'connect'

);

dbms_network_acl_admin.add_privilege (

acl => 'ACL_TEST.xml',

principal => 'TI',

is_grant => true,

privilege => 'resolve'

);

dbms_network_acl_admin.assign_acl(

acl => 'ACL_TEST.xml',

host => '*'

);

commit;

end;

PL/SQL procedure successfully completed.

Set ACL Owners

Setando as ACLs para outro Owner
begin 
  dbms_network_acl_admin.add_privilege (
  acl => 'ACL_TESTE.xml',
  principal => 'TI',
  is_grant => true,
  privilege => 'resolve'
  );

  dbms_network_acl_admin.add_privilege (
  acl => 'ACL_TESTE.xml',
  principal => 'TI',
  is_grant => true,
  privilege => 'connect'
  );

  dbms_network_acl_admin.assign_acl(
  acl  => 'ACL_TESTE.xml',
  host => '*'
  );
  commit;
end;
/

Setando as ACLs para outro Owner

begin

dbms_network_acl_admin.add_privilege (

acl => 'ACL_TESTE.xml',

principal => 'TI',

is_grant => true,

privilege => 'resolve'

);

dbms_network_acl_admin.add_privilege (

acl => 'ACL_TESTE.xml',

principal => 'TI',

is_grant => true,

privilege => 'connect'

);

dbms_network_acl_admin.assign_acl(

acl => 'ACL_TESTE.xml',

host => '*'

);

commit;

end;

Check ACL

column owner format a12;
column index_name format a18;
column status format a10;
column domidx_status format a10;
column domidx_opstatus format a10;
column funcidx_status format a10;

SQL> select acl,principal,privilege,is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                    PRINCIPA PRIVILEGE    IS_GR
---------------------- -------- ------------ -----
/sys/acls/ACL_TEST.xml  TI      connect      true
/sys/acls/ACL_TEST.xml  TI      resolve      true

SQL> select acl,host,lower_port,upper_port from DBA_NETWORK_ACLS;

ACL                    HOST  LOWER_PORT UPPER_PORT
---------------------- ----- ---------- ----------
/sys/acls/ACL_TEST.xml  *

column owner format a12;

column index_name format a18;

column status format a10;

column domidx_status format a10;

column domidx_opstatus format a10;

column funcidx_status format a10;

SQL> select acl,principal,privilege,is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL PRINCIPA PRIVILEGE IS_GR

---------------------- -------- ------------ -----

/sys/acls/ACL_TEST.xml TI connect true

/sys/acls/ACL_TEST.xml TI resolve true

SQL> select acl,host,lower_port,upper_port from DBA_NETWORK_ACLS;

ACL HOST LOWER_PORT UPPER_PORT

---------------------- ----- ---------- ----------

/sys/acls/ACL_TEST.xml *

Check permissão ACLs, DBA_NETWORK_ACL_PRIVILEGES e DBA_NETWORK_ACLS

Publicado em 15 de abril de 2019 by rodrigosilvaesilva

Check permissão DBA_NETWORK_ACL_PRIVILEGES e DBA_NETWORK_ACLS Formatando visualização

column ACL format a25;
column PRINCIPAL formata20;
column PRINCIPAL format a20;
column PRIVILEGE format a20;
column HOST format a20;

column ACL format a25;

column PRINCIPAL formata20;

column PRINCIPAL format a20;

column PRIVILEGE format a20;

column HOST format a20;

Checando DBA_NETWORK_ACL_PRIVILEGES

SQL> select acl,principal,privilege,is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                       PRINCIPAL            PRIVILEGE            IS_GR
------------------------- -------------------- -------------------- -----
/sys/acls/ACL_TI.xml        TI                  connect              true
/sys/acls/ACL_TI.xml        TI                  resolve              true

SQL> select acl,principal,privilege,is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL PRINCIPAL PRIVILEGE IS_GR

------------------------- -------------------- -------------------- -----

/sys/acls/ACL_TI.xml TI connect true

/sys/acls/ACL_TI.xml TI resolve true

Checando DBA_NETWORK_ACLS

SQL> select acl,host,lower_port,upper_port from DBA_NETWORK_ACLS;

ACL                       HOST                 LOWER_PORT UPPER_PORT
------------------------- -------------------- ---------- ----------
/sys/acls/ACL_TI.xml       *

SQL> select acl,host,lower_port,upper_port from DBA_NETWORK_ACLS;

ACL HOST LOWER_PORT UPPER_PORT

------------------------- -------------------- ---------- ----------

/sys/acls/ACL_TI.xml *

GPG key retrieval failed: [Errno 14] Could not open/read file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

Publicado em 11 de abril de 2019 by rodrigosilvaesilva

Erro ao instalar oracle-rdbms-server-11gR2-preinstall

warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle


GPG key retrieval failed: [Errno 14] Could not open/read file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

GPG key retrieval failed: [Errno 14] Could not open/read file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

Acesse o diretório: cd /etc/yum.repos.d/ Faça o Download:

[root@bancora] wget https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle --no-check-certificate

1	[root@bancora] wget https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle --no-check-certificate

[root@bancora yum.repos.d]# wget https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle --no-check-certificate
--2019-04-11 19:27:25--  https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
Resolving public-yum.oracle.com... 23.195.64.183
Connecting to public-yum.oracle.com|23.195.64.183|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1011 [text/plain]
Saving to: “/etc/pki/rpm-gpg/RPM-GPG-KEY-oracle”

100%[=============================================================================================>] 1,011       --.-K/s   in 0s

2019-04-11 19:27:27 (253 MB/s) - “/etc/pki/rpm-gpg/RPM-GPG-KEY-oracle” saved [1011/1011]

[root@bancora yum.repos.d]# wget https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle --no-check-certificate

--2019-04-11 19:27:25-- https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

Resolving public-yum.oracle.com... 23.195.64.183

Connecting to public-yum.oracle.com|23.195.64.183|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: 1011 [text/plain]

Saving to: “/etc/pki/rpm-gpg/RPM-GPG-KEY-oracle”

100%[=============================================================================================>] 1,011 --.-K/s in 0s

2019-04-11 19:27:27 (253 MB/s) - “/etc/pki/rpm-gpg/RPM-GPG-KEY-oracle” saved [1011/1011]

Resolvido!

ORA-30554: function-based index XDB.XDB$ACL_XIDX is disabled

Publicado em 9 de abril de 2019 by rodrigosilvaesilva

Erro ao criar ACL

begin
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl => 'acl_http.xml',
    description => 'Allow acl_viacep',
    principal => 'CPD',
    is_grant => true,
    privilege => 'connect'
    );

  dbms_network_acl_admin.add_privilege (
  acl => 'acl_http.xml',
  principal => 'CPD',
  is_grant => true,
  privilege => 'resolve'
  );

  dbms_network_acl_admin.assign_acl(
  acl  => 'acl_http.xml',
  host => 'serasa.com.br'
  );
  commit;
end;
/

begin

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(

acl => 'acl_http.xml',

description => 'Allow acl_viacep',

principal => 'CPD',

is_grant => true,

privilege => 'connect'

);

dbms_network_acl_admin.add_privilege (

acl => 'acl_http.xml',

principal => 'CPD',

is_grant => true,

privilege => 'resolve'

);

dbms_network_acl_admin.assign_acl(

acl => 'acl_http.xml',

host => 'serasa.com.br'

);

commit;

end;

Erro

ORA-30554: function-based index XDB.XDB$ACL_XIDX is disabled
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 258
ORA-06512: at line 2

ORA-30554: function-based index XDB.XDB$ACL_XIDX is disabled

ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 258

ORA-06512: at line 2

Consultando index

column owner format a12;
column index_name format a18;
column status format a10;
column domidx_status format a10;
column domidx_opstatus format a10;
column funcidx_status format a10;


SQL> select owner, index_name, status, domidx_status, domidx_opstatus,funcidx_status from dba_indexes where domidx_opstatus is not null;

OWNER        INDEX_NAME         STATUS     DOMIDX_STA DOMIDX_OPS FUNCIDX_ST
------------ ------------------ ---------- ---------- ---------- ----------
XDB          XDBHI_IDX          VALID      VALID      VALID      ENABLED
XDB          XDB$ACL_XIDX       VALID      VALID      VALID      DISABLED

column owner format a12;

column index_name format a18;

column status format a10;

column domidx_status format a10;

column domidx_opstatus format a10;

column funcidx_status format a10;

SQL> select owner, index_name, status, domidx_status, domidx_opstatus,funcidx_status from dba_indexes where domidx_opstatus is not null;

OWNER INDEX_NAME STATUS DOMIDX_STA DOMIDX_OPS FUNCIDX_ST

------------ ------------------ ---------- ---------- ---------- ----------

XDB XDBHI_IDX VALID VALID VALID ENABLED

XDB XDB$ACL_XIDX VALID VALID VALID DISABLED

Recompilando Index

SQL>alter index XDB.XDB$ACL_XIDX rebuild;

Index altered.

SQL>alter index XDB.XDB$ACL_XIDX rebuild;

Index altered.

SQL> select owner, index_name, status, domidx_status, domidx_opstatus,funcidx_status from dba_indexes where domidx_opstatus is not null;

OWNER        INDEX_NAME         STATUS     DOMIDX_STA DOMIDX_OPS FUNCIDX_ST
------------ ------------------ ---------- ---------- ---------- ----------
XDB          XDBHI_IDX          VALID      VALID      VALID      ENABLED
XDB          XDB$ACL_XIDX       VALID      VALID      VALID      ENABLED

SQL> select owner, index_name, status, domidx_status, domidx_opstatus,funcidx_status from dba_indexes where domidx_opstatus is not null;

OWNER INDEX_NAME STATUS DOMIDX_STA DOMIDX_OPS FUNCIDX_ST

------------ ------------------ ---------- ---------- ---------- ----------

XDB XDBHI_IDX VALID VALID VALID ENABLED

XDB XDB$ACL_XIDX VALID VALID VALID ENABLED

Resolvido!

← Posts recentes Posts antigos →