我们的身份管理工具希望定期更改sys密码。它对ORA-01031失败了。我需要授予身份管理工具用户哪些特权或角色才能更改sys密码?
发布于 2020-09-08 23:49:23
您需要以SYSDBA特权作为用户进行连接。即使是“更改任何用户”也是不够的。
SQL> select *
2 from session_privs
3 where privilege like 'ALTER ANY%';
PRIVILEGE
----------------------------------------
ALTER ANY TABLE
ALTER ANY CLUSTER
ALTER ANY INDEX
ALTER ANY SEQUENCE
ALTER ANY ROLE
ALTER ANY PROCEDURE
ALTER ANY TRIGGER
ALTER ANY MATERIALIZED VIEW
ALTER ANY TYPE
ALTER ANY LIBRARY
ALTER ANY OPERATOR
ALTER ANY INDEXTYPE
ALTER ANY DIMENSION
ALTER ANY OUTLINE
ALTER ANY EVALUATION CONTEXT
ALTER ANY RULE SET
ALTER ANY RULE
ALTER ANY SQL PROFILE
ALTER ANY EDITION
ALTER ANY ASSEMBLY
ALTER ANY MINING MODEL
ALTER ANY CUBE DIMENSION
ALTER ANY CUBE
ALTER ANY SQL TRANSLATION PROFILE
ALTER ANY MEASURE FOLDER
ALTER ANY CUBE BUILD PROCESS
ALTER ANY ATTRIBUTE DIMENSION
ALTER ANY HIERARCHY
ALTER ANY ANALYTIC VIEW
29 rows selected.
SQL> alter user sys identified by newpass;
alter user sys identified by newpass
*
ERROR at line 1:
ORA-01031: insufficient privileges
发布于 2020-09-09 10:36:04
我们的身份管理工具希望定期更改sys密码..。
我认为这是不明智的。
不只是Oracle数据库可以使用SYS密码。
DataGuard代理和企业管理器( Enterprise )就是其中的两个。后者将在某一时刻将SYS密码存储在其中(考虑到OEM倾向于为您“记住”事情,这几乎是不可避免的),因此定期更改SYS密码(S)可能会给您带来问题。
我似乎还记得OEM代理也可以使用它(用于自动故障转移?)因此,定期将这个密码从你的下面换掉比你想象的要危险得多。
我在这里假设您是指SYS密码,复数,因为,当然,每个Oracle安装都必须有自己的凭据,与每个其他安装都不同,而且您必须至少有两个安装(Test和Live)!
发布于 2020-09-10 07:51:37
我正在使用Oracle 11.2:
SQL>select banner
2 from v$version
3 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
要修改用户的密码,需要“ALTER”特权。
SQL>connect / as sysdba
Connected.
SQL>create user myuser identified by myuser;
User created.
SQL>grant create session to myuser;
Grant succeeded.
SQL>grant alter user to myuser;
Grant succeeded.
SQL>connect myuser/myuser
Connected.
SQL>alter user sys identified by HalloWorld;
User altered.
SQL>
因此,ALTER是一个非常危险的特权。用户可以更改SYS密码,以SYS登录,并可以修改/读取/删除数据库。因此,您应该执行以下操作
下面是一个例子
SQL>connect / as sysdba
Connected.
SQL>create user secure identified by secure;
User created.
SQL>grant create procedure to secure;
Grant succeeded.
SQL>grant alter user to secure;
Grant succeeded.
SQL>create or replace procedure secure.change_syspw(new_syspw varchar2)
2 as
3 begin
4 EXECUTE IMMEDIATE 'alter user sys identified by "'||new_syspw||'"';
5 end;
6 /
Procedure created.
SQL>create user myuser identified by myuser;
User created.
SQL>grant create session to myuser;
Grant succeeded.
SQL>grant execute on secure.change_syspw to myuser;
Grant succeeded.
SQL>connect myuser/myuser
Connected.
SQL>exec secure.change_syspw('HalloWorld')
PL/SQL procedure successfully completed.
SQL>
https://dba.stackexchange.com/questions/275148
复制相似问题