Hybris95's questions

Eu tenho um usuário encarregado de implantar DDL em outros esquemas que não o seu.
Esse usuário pode criar uma tabela nesses esquemas, também pode criar sequências nesses esquemas.

Mas quando tento criar uma tabela com uma coluna GENERATED IDENTITY nesses esquemas,
o usuário encontra umaORA-01031: privileges insuffisants

Quando este usuário tenta criar a mesma tabela em seu próprio esquema, ele pode fazê-lo corretamente.

Este usuário recebeu os seguintes direitos:

GRANT CREATE ANY SEQUENCE TO ddl_role;
GRANT CREATE ANY TABLE TO ddl_role;

Aqui está um exemplo da consulta executada:

CREATE TABLE "sch1"."tab1"
   (        "col1" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE)
TABLESPACE "sch1_data";

As seguintes consultas funcionam:

CREATE TABLE "sch1"."tab1"
    (        "col1" NUMBER )
TABLESPACE "sch1_data";

CREATE SEQUENCE "sch1"."seq1";

Referências:
https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1

Tenho 2 logins:

• LoginA: role dbcreator + CONECTE QUALQUER BANCO DE DADOS
• LoginB: função sysadmin

Tenho 2 bancos de dados:

• dbA: todos têm todos os direitos sobre ele
• dbB: apenas LoginB tem direitos sobre ele, LoginA é apenas o proprietário desse banco de dados, mas não mais

Gostaria de saber se é possível criar um usuário (vinculado ao LoginA) em dbB a partir de um procedimento armazenado de dbA.
Considerando que este procedimento armazenado deve ser executado por LoginA.
Talvez algum tipo de mecanismo "EXECUTE AS" possa ajudar?
Também tenho problemas sobre como usar um "USE [dbB]" em um procedimento armazenado.

A solução pode ter algumas modificações de direitos,
mas elas não podem dar funções securityadmin nem sysadmin.

Aqui está o que está feito por enquanto, mas claramente há muita depuração e funcionalidades a serem feitas, mas o espírito do que eu quero fazer está aqui:

CREATE PROCEDURE [dbB_UserAfterRestore] 
AS
BEGIN
    SET NOCOUNT ON;

    USE [dbB]
    CREATE USER [HOSTNAME\LoginA] FOR LOGIN [HOSTNAME\LoginA]
    ALTER ROLE [db_datareader] ADD MEMBER [HOSTNAME\LoginA]
    ALTER ROLE [db_datawriter] ADD MEMBER [HOSTNAME\LoginA]
END
GO

Eu tenho uma consulta relativamente simples que SELECT dados em uma tabela.

SELECT l.columnRecovered FROM schema.TABLE l
WHERE (column1 = :a or (:a is null AND column1 is null))
AND (column2 = :b or (:b is null AND column2 is null))
AND (column3 = :c or (:c is null AND column3 is null));

Isso gera o seguinte plano:

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   309 (100)|          |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   309   (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((("COLUMN2"=:3 OR ("COLUMN2" IS NULL AND :4 IS NULL)) AND
              ("COLUMN1"=:1 OR ("COLUMN1" IS NULL AND :2 IS NULL)) AND ((:6 IS NULL
              AND "COLUMN3" IS NULL) OR "COLUMN3"=:5)))

Nesta tabela, tenho um UNIQUE INDEX que contém todas essas 3 colunas.
Mas o otimizador prefere fazer um FULL SCAN na mesa ao invés de usar o INDEX.

Onde as coisas começam a ficar estranhas, é quando tento essa consulta sem meus vínculos, mas substituindo os valores diretamente na consulta.
Claro que o SQL_ID muda, mas dessa vez o otimizador decide usar o INDEX e vai de 1104 para 6 (o que é claro uma ótima otimização).

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE         |     1 |    56 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | TABLE_IDX     |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COLUMN3"='DATA3' AND "COLUMN2"='DATA2' AND "COLUMN1"='DATA1')

As estatísticas estão atualizadas e não há histogramas nesta tabela.

Alguma maneira de fazer o otimizador usar esse índice sem modificar a consulta?

Eu acho que o otimizador pensa que a possibilidade de ter 3 NULLs pode acontecer então isso desabilita ele de usar o índice certo?

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    56 |   199   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   199   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COLUMN2" IS NULL AND "COLUMN1" IS NULL AND "COLUMN3" IS
              NULL)