常见权限问题处理思路

报错：

案例1.

SQL> l
  1  DECLARE
  2
  3   l_plans_altered  PLS_INTEGER;
  4
  5  BEGIN
  6
  7   l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
  8
  9      sql_handle      => 'SQL_93961960a2d79e68',
 10
 11      plan_name       => 'SQL_PLAN_975htc2jdg7m890704d19',
 12
 13     attribute_name  => 'ENABLED',
 14
 15     attribute_value => 'NO');
 16
 17   DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
 18
 19* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-38171: Insufficient privileges for SQL management object operation
ORA-06512: at "SYS.DBMS_SPM", line 2477
ORA-06512: at line 7

解决：

1.grant execute on dbms_spm to aiki;

结果：报错依然

2.查看PLSQL Packages and Types Reference文档

Security Model
The package is owned by SYS. The EXECUTE package privilege is required to execute
its procedures. Any user granted the ADMINISTER SQL MANAGEMENT OBJECT
privilege is able to execute the DBMS_SPM package.

grant  ADMINISTER SQL MANAGEMENT OBJECT  to aiki;

结果：正常

案例2.

SQL> l
  1* select dbms_metadata.get_ddl('TABLE','AA','AIKI2') from dual
SQL> /
ERROR:
ORA-31603: object "AA" of type TABLE not found in schema "AIKI2"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1

SQL> desc aiki2.aa
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 OBJECT_ID   

解决：查询文档

The DBMS_METADATA package considers a privileged user to be one who is connected
as user SYS or who has the SELECT_CATALOG_ROLE role. The object views of the
Oracle metadata model implement security as follows:

结果：解决