"alter user" sem privilegio na procedure, mas no sqlplus roda

[NJM 0]

Olá Pessoal,

 

Estou criando uma rotina de alteração de usuario do oracle (criar usuario, alterar senha, desabilitar, etc), para facilitar estou criando procedures, acontece que ao executar a procedure recebo o erro "1031 - ORA-01031: privilégios insuficientes", porem se executo os mesmos comandos no sqlplus não dá erro nenhum. Estou rodando a procedure e os comandos debaixo de role e acho que já dei tudo quanto for tipo de autoridade a ela. Já "googlei" e nada. Alguém tem alguma dica. Não quero dar permissão direto para o usuário, isso pode ser perigoso. Desde já agradeço a todos pela ajuda.

 

Segue os comandos abaixo :

-- PROCEDURE

create or replace procedure PR_SENHA2(p_usuario in usuario.usuario%Type) is

v_erro varchar2(100);

begin

v_erro := ' PR_SENHA - Erro na modificação da senha do USUARIO ' || p_usuario;

BEGIN

EXECUTE IMMEDIATE 'ALTER USER "' || p_usuario || '" IDENTIFIED BY "' || lower(p_usuario)||'"';

exception

WHEN OTHERS THEN

raise_application_error(-20005,'Erro de processamento - '|| v_erro ||' - '|| SQLCODE ||' - '|| SQLERRM);

END;

end PR_SENHA2;

 

-- COMANDO

SQL>

begin

pr_senha2('ROSALINA');

end;

 

ORA-20005: Erro de processamento - PR_RSENHA2 - Erro na modificação da senha do USUARIO ROSALINA - -1031 - ORA-01031: privilégios insuficientes

ORA-06512: em "PR_SENHA2", line 9

ORA-06512: em line 3

 

SQL>

[Motta 645]

O usuário que executa a SP tem a permissão de alter user?

Tem alguma ROLE mesmo que em run time ?

 

Esta SP é chamada como, via aplicação ?

[NJM 0]

Ola Motta, O usuário não tem permissão de "ALTER USER", quem tem é a ROLE e ela está sim em execução, tanto é que se eu fazer o comando "ALTER USER" manualmente no sqlplus ele executa sem erro. A SP deverá ser chamada via trigger, mas tambem poderá ser chamada por aplicação.

 

 

Obrigado pelo tempo disposto.

[Motta 645]

O usuário tem a ROLE como "não default" e a recebe quando se log.

A ROLE tem a permissão de ALTER USER.

O usuário passa a ter permissão de ALTER USER.

Deveria funcionar , o Sistema antigo daqui fazia exatamente isto.

[supernoi 0]

Bom dia,

 

Nao seria falta de grant Execute procedure nao?

 

Executa essas 2 querys ae e posta o resultado:

 

select dba_users.username,

dba_users.account_status,

dba_sys_privs.privilege

from dba_sys_privs,

dba_role_privs,

dba_users

where (dba_users.username = dba_sys_privs.grantee

or (dba_users.username = dba_role_privs.grantee

and dba_role_privs.granted_role = dba_sys_privs.grantee))

and dba_users.username ='USUARIO'

group by dba_sys_privs.privilege, dba_users.username, dba_users.account_status

order by username;

 

 

select du.username usuário,

du.account_status status,

drp.granted_role role

from dba_users du inner join dba_role_privs drp

on du.username = drp.grantee

where du.username ='USUARIO'

and du.username not in (select role from dba_roles)

order by 2,1

[NJM 0]

Então, acho que o problema está em usa-lo com o "execute immediate" na SP. Não sei qual seria a diferença em executa-lo através da SP e o comando direto.

 

SQL> set role rolesis identified by senha

2 ;

 

Role set

 

SQL> alter user ROSALINA identified by rosalina;

 

User altered

 

SQL>

 

Ola Supernoi, obrigado pela ajuda!, segue a saida dos comandos :

 

 

SQL>

SQL> select du.username usuário,

2 du.account_status status,

3 drp.granted_role role

4 from dba_users du inner join dba_role_privs drp

5 on du.username = drp.grantee

6 where du.username ='ROSALINA'

7 and du.username not in (select role from dba_roles)

8 order by 2,1

9 ;

 

USUÁRIO STATUS ROLE

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

ROSALINA OPEN ROLESIS

ROSALINA OPEN CONNECT

 

SQL>

SQL>

SQL> select dba_users.username,

2 dba_users.account_status,

3 dba_sys_privs.privilege

4 from dba_sys_privs,

5 dba_role_privs,

6 dba_users

7 where (dba_users.username = dba_sys_privs.grantee

8 or (dba_users.username = dba_role_privs.grantee

9 and dba_role_privs.granted_role = dba_sys_privs.grantee))

10 and dba_users.username ='ROSALINA'

11 group by dba_sys_privs.privilege, dba_users.username, dba_users.account_status

12 order by username;

 

USERNAME ACCOUNT_STATUS PRIVILEGE

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

ROSALINA OPEN EXECUTE ANY PROCEDURE

ROSALINA OPEN CREATE USER

ROSALINA OPEN CREATE SESSION

ROSALINA OPEN ALTER USER

 

SQL>

[supernoi 0]

Eh ai complica... vamos utilizar a tecnica milenar dos DBAs...

Jogar nos buzios... brincadeira... rs

 

Assim que der tento simular o erro aqui e tento ajudar mais...

 

Flw

[NJM 0]

Pelas minhas pesquisas a unica forma de ter condições de executar este "ALTER USER" atraves de SP é dando autoridade de "ALTER/CREATE USER" diretamente para o usuário e não para "ROLE", que no meu caso não serve porque mantenho a entrada no sistema usando o oracle como autorizador, assim qualquer usuário com um pouco de conhecimento em sql poderia mudar a senha de um usuário mais graduado e fazer o que quiser no sistema. Qualquer opinião é bem-vinda.

 

Obrigado!

[Motta 645]

Creio que não pois como disse no sistema antigo que tinhamos aqui se fazia exatamente isto.

 

ROSALINA tem o GRANT da role rolesis ?

[supernoi 0]

Realmente esqueci de testar essa SP,

Mas se der um Grant, alterar a senha e logo apos der um Revoke...

 

Nao funciona nao?

[NJM 0]

Oi Motta/Supernoi, obrigado pela atenção. Sim, a Rosalina tem grant no rolesis. Eu coloquei os procedimentos dentro do programa que faz manutenção no usuario, aí funcionou bem, mas, o certo seria coloca-los na trigger chamando uma procedure ou executando de dentro dela mesmo, assim ficaria despreocupado e mais fácil em garantir a integridade e a criação do usuário com as devidas autoridades. Bom Supernoi, acho que de dentro da trigger/procedure tambem não conseguirá executar o grant/revoke só com autoridade de role. Mas farei um teste.

 

 

grato.

[Motta 645]

O Sistema que citei aqui era em Delphi e tinha um FORM que fazia a alteração da senha, o usuário se logava com uma conta Oracle e recebia a ROLE com superpoderes em runtime.

 

Tendo o usuário já recebido a ROLE não velo motivo para o Oracle implicar com a chamada via TRIGGER.

[NJM 0]

Então Motta, penso da mesma forma, é o que faço aqui, um form para o usuario se logar, assim que logado seta a role e pronto, se colocar os comandos dentro do form roda tudo direitinho, mas, gostaria que fosse executado por uma trigger chamando uma SP para fazer o trabalho, ai ele não roda, só não entendo a diferença entre executar dentro do form e executar através da trigger com 'execute immediate'.

[Motta 645]

Mas por que por uma trigger o form não poderia mandar o alter user, como um comando simplesmente ?

[NJM 0]

Oi Motta, como controlo os usuarios através de uma tabela a trigger seria pra ficar totalmente automatizada, se inserir na tabela então crio e usuario, se colocar como inativo então desativo no oracle e assim por diante, a vantagem que ficaria tudo amarrado a tabela e poderia usar em qualquer parte do sistema sem me preocupar. Acabei por criar uma função no sistema para automatizar esses procedimentos, funciona bem tambêm, o que gostaria de entender é o que muda para o Oracle em executar o comando pela trigger e no sistema, todos estão com os mesmo privilégios da role, de qualquer forma acredito que eles devem ter algum motivo para bloquear. Vamos fechar o post colocando como "não tem como realizar alterações em usuário através de trigger somente com privilégios de role, tem que ter privilégios no usuário".

 

Grato.