AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • Início
  • system&network
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • Início
  • system&network
    • Recentes
    • Highest score
    • tags
  • Ubuntu
    • Recentes
    • Highest score
    • tags
  • Unix
    • Recentes
    • tags
  • DBA
    • Recentes
    • tags
  • Computer
    • Recentes
    • tags
  • Coding
    • Recentes
    • tags
Início / dba / Perguntas / 344695
Accepted
folow
folow
Asked: 2025-01-14 18:55:01 +0800 CST2025-01-14 18:55:01 +0800 CST 2025-01-14 18:55:01 +0800 CST

Como atribuir SELECT no esquema, mas DENY em uma tabela específica no esquema?

  • 772

No Db2 v11.5 no Linux, tenho vários bancos de dados com centenas de tabelas no esquema MYSCHEMA.

EXIGÊNCIA

a) Preciso atribuir permissão SELECT a todas as tabelas no esquema MYSCHEMA, exceto para uma tabela específica MYSCHEMA.NO_ACCESS à qual os usuários não devem ter acesso.

b) Se uma nova tabela for criada no esquema MYSCHEMA, preciso adicionar manualmente GRANT SELECT na nova tabela. Gostaria de evitar qualquer tipo de administração de segurança quando uma nova tabela for criada.

c) Quando temos auditoria de uma empresa de auditoria, precisa ser simples fornecer informações sobre qual acesso algum usuário tem e qual não tem. Ter centenas de concessões é difícil de mostrar que os usuários realmente têm apenas o acesso de que precisam.

SOLUÇÃO ATUAL

Atualmente, concedemos seleção em todas as tabelas, exceto na tabela MYCHEMA.NO_ACCESS, assim:

GRANT SELECT ON TABLE MYSCHEMA.TAB1 TO GROUP GROUP1
GRANT SELECT ON TABLE MYSCHEMA.TAB2 TO GROUP GROUP1
...
GRANT SELECT ON TABLE MYSCHEMA.TAB5000 TO GROUP GROUP1 
(make sure there is no MYSCHEMA.NO_ACCESS on grant list)

A abordagem acima tem limitações: sempre que uma nova tabela é criada no esquema MYSCHEMA, preciso definir GRANT SELECT na nova tabela.

Além disso, na auditoria, devido às inúmeras permissões, é difícil provar que os usuários realmente têm apenas as permissões que deveriam ter.

NOVA ABORDAGEM, SE POSSÍVEL

O Db2 v11.5 tem o privilégio GRANT SELECTIN no esquema.

Existe algo parecido como:

GRANT SELECTIN ON SCHEMA MYSCHEMA TO GROUP GRUP1
DENY SELECT ON TABLE MYSCHEMA.NO_ACCESS TO GROUP GROUP1

Onde negar tem maior importância que conceder.

Ou existe alguma outra abordagem?

permissions
  • 2 2 respostas
  • 86 Views

2 respostas

  • Voted
  1. Best Answer
    Mark Barinstein
    2025-01-17T01:19:16+08:002025-01-17T01:19:16+08:00

    Você pode criar uma Permissão de Linha nesta tabela.

    Você deve criar uma função segura com base na função padrão que gera uma exceção:

    CREATE OR REPLACE FUNCTION RAISE_ERROR_MY(P_SQLSTATE CHAR(5), P_MSG VARCHAR(70))
    RETURNS INT
    CONTAINS SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    SECURED
    RETURN RAISE_ERROR(P_SQLSTATE, P_MSG)::INT
    

    Usando esta função:

    CREATE PERMISSION DENY_ALL_ON_NO_ACCESS_TO_GROUP1 ON MYSCHEMA.NO_ACCESS
    FOR ROWS WHERE
    CASE 
      WHEN VERIFY_GROUP_FOR_USER(SESSION_USER, 'GROUP1') = 0 THEN 1
      ELSE RAISE_ERROR_MY('75000', 'Not allowed')
    END = 1
    ENFORCED FOR ALL ACCESS
    ENABLE;
    
    ALTER TABLE MYSCHEMA.NO_ACCESS ACTIVATE ROW ACCESS CONTROL;
    

    Se o usuário atual for um membro de GROUP1, então o DB2 gera uma exceção com SQLCODE = -438 e SQLSTATE = '75000' em qualquer tentativa de acessar esta tabela. A expressão é avaliada como TRUE, permitindo o acesso à tabela correspondente para não um membro de GROUP1.
    Você não obtém o SQLCODE padrão = -551 em uma tentativa de acesso "errônea", mas não deve ser importante, eu acredito...

    Atualização
    Além disso, você pode até fazer o DB2 levantar uma exceção com SQLCODE = -551 em tal acesso.
    Você precisa usar uma rotina não documentada SYSIBMINTERNAL.SQLEML_RAISE_ERRORpara isso.
    Envolva-a em uma função escalar:

    CREATE OR REPLACE FUNCTION RAISE_ERROR_MY(P_SQLCODE INT, P_MSG VARCHAR(4000))
    RETURNS INT
    CONTAINS SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    SECURED
    BEGIN --ATOMIC
      CALL SYSIBMINTERNAL.SQLEML_RAISE_ERROR(P_SQLCODE, P_MSG);
      RETURN 0;
    END
    

    e use-o em uma expressão de permissão de linha como abaixo:

    RAISE_ERROR_MY(-551, USER || x'ff' || 'ANY ACCESS' || x'ff' || 'MYSCHEMA.NO_ACCESS')
    

    Você obtém uma exceção com o SQLCODE/SQLSTATE padrão então.
    Note que você deve usar a função compilada RAISE_ERROR_MY(sem a cláusula ATOMIC). Ela é chamada internamente de qualquer forma CASEna expressão de permissão de linha, lançando uma exceção inesperada caso contrário.

    • 2
  2. Lennart - Slava Ukraini
    2025-01-16T01:30:10+08:002025-01-16T01:30:10+08:00

    Você está certo, é claro, só é possível revogar privs que são concedidos. Embora não seja uma resposta exata para sua pergunta, você pode usar uma solução alternativa como (adicionei revoke e um manipulador para quando o usuário não tem select auth):

    CREATE OR REPLACE PROCEDURE GRANT_ALL_BUT(s VARCHAR(128), t VARCHAR(128), u VARCHAR(128))
    LANGUAGE SQL
    DYNAMIC RESULT SETS 0
    BEGIN
        DECLARE CONTINUE HANDLER FOR SQLSTATE '42504' BEGIN END;
        FOR v AS c1 CURSOR FOR SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = s AND TABNAME <> t
        DO
            EXECUTE IMMEDIATE 'GRANT SELECT ON TABLE ' || v.TABSCHEMA || '.' || v.TABNAME || ' TO USER ' || u;
        END FOR;
        EXECUTE IMMEDIATE 'REVOKE SELECT ON TABLE ' || s || '.' || t || ' FROM USER ' || u;
    END @
    
    db2 "create table tmp.t1 (x int)"
    db2 "create table tmp.t2 (x int)"
    
    db2 "call GRANT_ALL_BUT('TMP','T1','DB2FENC1')"
    

    Como db2fenc1:

    db2 "select * from tmp.t2"
    
    X          
    -----------
    
      0 record(s) selected.
    
    db2 "select * from tmp.t1"
    SQL0551N  The statement failed because the authorization ID does not have the 
    required authorization or privilege to perform the operation.  
    Authorization ID: "DB2FENC1".  Operation: "SELECT". Object: "TMP.T1".  SQLSTATE=42501
    

    Agora vejo que era um grupo, não um usuário, mas fica como exercício para o leitor;-)

    Se isso não for o caso, você pode dar uma olhada no controle de acesso de linha, o usuário (no meu caso db2fenc1), pode selecionar da tabela, mas não pode ver nenhuma linha. Você pode encontrar mais informações em Visão geral do controle de acesso de linha e coluna (RCAC)

    Este é apenas um exemplo bobo:

    DROP TABLE TMP.t1;
    DROP TABLE TMP.t2;
    
    CREATE TABLE tmp.t1 (x int);
    CREATE TABLE tmp.t2 (x int);
    
    insert into tmp.t1 values (1),(2);
    insert into tmp.t2 values (2),(3);
    
    grant selectin on schema tmp to user DB2FENC1;
    
    CREATE PERMISSION ROW_ACCESS ON tmp.t1
    FOR ROWS WHERE (SESSION_USER <> 'DB2FENC1')
    enforced for all access
    enable;
    
    ALTER TABLE tmp.t1 ACTIVATE ROW ACCESS CONTROL;
    

    O que db2fenc1 vê

    [db2fenc1@db2host ~]$ db2 "select * from tmp.t2"
    
    X          
    -----------
              2
              3
    
      2 record(s) selected.
    
    [db2fenc1@db2host ~]$ db2 "select * from tmp.t1"
    
    X          
    -----------
    
      0 record(s) selected.
    
    • 1

relate perguntas

  • Erro de permissão negada do SQL Server quando sou DBOwner?

  • É possível encontrar os valores das variáveis ​​do MySQL se eu não tiver privilégios SELECT no mysql.user?

  • Como recuperar privilégios concedidos sem acesso ao banco de dados “mysql”?

  • Um usuário MySQL de 2 hosts não na mesma sub-rede

  • Você pode "su -" no MySQL?

Sidebar

Stats

  • Perguntas 205573
  • respostas 270741
  • best respostas 135370
  • utilizador 68524
  • Highest score
  • respostas
  • Marko Smith

    conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

    • 12 respostas
  • Marko Smith

    Como fazer a saída do sqlplus aparecer em uma linha?

    • 3 respostas
  • Marko Smith

    Selecione qual tem data máxima ou data mais recente

    • 3 respostas
  • Marko Smith

    Como faço para listar todos os esquemas no PostgreSQL?

    • 4 respostas
  • Marko Smith

    Listar todas as colunas de uma tabela especificada

    • 5 respostas
  • Marko Smith

    Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

    • 4 respostas
  • Marko Smith

    Como você mysqldump tabela (s) específica (s)?

    • 4 respostas
  • Marko Smith

    Listar os privilégios do banco de dados usando o psql

    • 10 respostas
  • Marko Smith

    Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

    • 4 respostas
  • Marko Smith

    Como faço para listar todos os bancos de dados e tabelas usando o psql?

    • 7 respostas
  • Martin Hope
    Jin conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane Como faço para listar todos os esquemas no PostgreSQL? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh Por que o log de transações continua crescendo ou fica sem espaço? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland Listar todas as colunas de uma tabela especificada 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney O MySQL pode realizar consultas razoavelmente em bilhões de linhas? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx Como posso monitorar o andamento de uma importação de um arquivo .sql grande? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison Como você mysqldump tabela (s) específica (s)? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas Como posso cronometrar consultas SQL usando psql? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas Como faço para listar todos os bancos de dados e tabelas usando o psql? 2011-02-18 00:45:49 +0800 CST

Hot tag

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • Início
  • Perguntas
    • Recentes
    • Highest score
  • tag
  • help

Footer

AskOverflow.Dev

About Us

  • About Us
  • Contact Us

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve