Oracle – Página: 6 – Rodrigo Silva e Silva

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!

Oracle SELECT, OBTER TODAS AS PERMISSOES SELECT DO OWNER E CONCEDER A UM USUARIO

Publicado em 12 de março de 2019 by rodrigosilvaesilva

Obtendo todas as permissões SELECT do Owner e conceder a um usuário.

SYS@ORCL > select 'GRANT SELECT ON '|| owner ||'.' || object_name || ' TO USER;' from all_objects where owner='NOME';

GRANT SELECT ON NBS.PK_BKP_OPER_ID TO USER;
GRANT SELECT ON NBS.SEQ_BKP_OPER_ID TO USER;
GRANT SELECT ON NBS.BKP_TABELA TO USER;
GRANT SELECT ON NBS.PK_BKP_TABELA_ID TO USER;
GRANT SELECT ON NBS.SEQ_BKP_TABELA_ID TO USER;
GRANT SELECT ON NBS.BKP_CAMPO_BLOB TO USER;
GRANT SELECT ON NBS.PK_BKP_CAMPO_BLOB_ID TO USER;
GRANT SELECT ON NBS.SEQ_BKP_CAMPO_BLOB_ID TO USER;
GRANT SELECT ON NBS.BKP_MOVIMENTO TO USER;
GRANT SELECT ON NBS.PK_BKP_MOV_ID TO USER;
GRANT SELECT ON NBS.SEQ_BKP_MOV_ID TO USER;
GRANT SELECT ON NBS.HYUNDAI_WS_LOG TO USER;
GRANT SELECT ON NBS.SYS_C00101260 TO USER;
GRANT SELECT ON NBS.SEQ_HYUNDAI_WS_LOG TO USER;
GRANT SELECT ON NBS.IDXCONTROLE_SERIE TO USER;
GRANT SELECT ON NBS.IDX_SPED_ITENS_DOCUMENTO_DATA TO USER;
GRANT SELECT ON NBS.FORD_EXT_MOV_OIC TO USER;
GRANT SELECT ON NBS.IDX_FORD_EXT_MOV_OIC TO USER;

13786 rows selected.

SYS@ORCL > select 'GRANT SELECT ON '|| owner ||'.' || object_name || ' TO USER;' from all_objects where owner='NOME';

GRANT SELECT ON NBS.PK_BKP_OPER_ID TO USER;

GRANT SELECT ON NBS.SEQ_BKP_OPER_ID TO USER;

GRANT SELECT ON NBS.BKP_TABELA TO USER;

GRANT SELECT ON NBS.PK_BKP_TABELA_ID TO USER;

GRANT SELECT ON NBS.SEQ_BKP_TABELA_ID TO USER;

GRANT SELECT ON NBS.BKP_CAMPO_BLOB TO USER;

GRANT SELECT ON NBS.PK_BKP_CAMPO_BLOB_ID TO USER;

GRANT SELECT ON NBS.SEQ_BKP_CAMPO_BLOB_ID TO USER;

GRANT SELECT ON NBS.BKP_MOVIMENTO TO USER;

GRANT SELECT ON NBS.PK_BKP_MOV_ID TO USER;

GRANT SELECT ON NBS.SEQ_BKP_MOV_ID TO USER;

GRANT SELECT ON NBS.HYUNDAI_WS_LOG TO USER;

GRANT SELECT ON NBS.SYS_C00101260 TO USER;

GRANT SELECT ON NBS.SEQ_HYUNDAI_WS_LOG TO USER;

GRANT SELECT ON NBS.IDXCONTROLE_SERIE TO USER;

GRANT SELECT ON NBS.IDX_SPED_ITENS_DOCUMENTO_DATA TO USER;

GRANT SELECT ON NBS.FORD_EXT_MOV_OIC TO USER;

GRANT SELECT ON NBS.IDX_FORD_EXT_MOV_OIC TO USER;

13786 rows selected.

← Posts recentes Posts antigos →