ORA-01031: insufficient privileges CDB/PDB

jnrjian

于 2023-07-12 15:02:18 发布

阅读量447

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/jnrjian/article/details/131681925

版权

SYMPTOMS

On : 12.2.0.1 version, Database Security

1. When attempting to run alter user in DBV environment with secondary DV acct mgr user, the following error occurs.

ERROR
-----------------------
alter user dbsnmp identified by "********" ;-----common user containe=rall
*
ERROR at line 1:
ORA-01031: insufficient privileges

2. Password of Primary dv_acctmgr has been forgotten and not working.

3. DV_ACCTMGR role is already granted to the secondary DV acct mgr but still this secondary user is not able to reset the other users password.

select granted_role from dba_role_privs where grantee='C##<secondary DV acct manager>';

GRANTED_ROLE
--------------------------------------------------------------------------------
DV_ACCTMGR
DBA
DV_OWNER

SQL> conn C##<secondary DV acct manager>;

SQL> alter user dbsnmp identified by "********" ;
*
ERROR at line 1:
ORA-01031: insufficient privileges

CHANGES

CAUSE

Role Grant of DV_ACCTMGR was provided to CDB only and should be provided container=all so that will reflect in all PDBs as well.

select * from dba_role_privs where grantee = 'C##<secondary DV acct manager>';

GRANTEE
--------------------------------------------------------------------------------
GRANTED_ROLE
--------------------------------------------------------------------------------
ADM DEL DEF COM INH
--- --- --- --- ---
C##<secondary DV acct manager>
DV_ACCTMGR
NO NO YES NO NO <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Common is no

Refer: DBA_ROLE_PRIVS

COMMON

VARCHAR2(3)

Indicates how the grant was made. Possible values:

YES if the role was granted commonly (CONTAINER=ALL was used)
NO if the role was granted locally (CONTAINER=ALL was not used)

SOLUTION

use container=all while granting the grants to common users(Secondary dv_acctmgr) so that role will be effective in all PDB as well instead of only in CDB.

grant DV_ACCTMGR to C##<secondary DV acct manager> container=all;

Workaround : If you can login through dv owner account then perform below steps to reset the password of primary DV account manager account.

Connect as dvowner and disable the DB vault. conn <dvowner>
exec dbms_macadm.disable_dv;

2. retry changing the password of dv acct mgr.

3. if still unable to change, restart the db and retry to change the password of dv acct mgr.

4. Once done enable the DBV
conn dvowner/XX
exec dbms_macadm.enable_dv;

Restart the DB

Only do disable and then enable DV, don't remove DV (don't run dvremov.sql).