ORA-01031 error prompted during DDL statement execution

The process executes DDL statements to prompt ORA-01031 errors [SQL] -- create test user create user hat identified by hat default tablespace doldolphin; www.2cto.com -- grant permissions grant connect, resource to hat; -- view the role permissions of the hat user select * from dba_role_privs where grantee = 'hat '; GRANTEE GRANTED_ROLE adm def ------------------------------ -------------------------- --- hat resource no yes hat connect no yes -- view the resource role permission select * from role_sys_privs w Here role = 'resource '; role privilege adm ---------------------------- usage --- resource create sequence no resource create trigger no resource create cluster no resource create procedure no resource create type no resource create operator no resource create table no -- pay attention to RESOURCE CREATE INDEXTYPE NO -- create or replace procedure p_createtable as v_ SQL va Rchar2 (100); begin v_ SQL: = 'create table t_createtable (id integer, name varchar2 (10) '; execute immediate v_ SQL; end; -- execute begin p_createtable (); end; ORA-01031: insufficient privileges ORA-06512: at "HAT. p_CREATETABLE ", line 6 ORA-06512: at line 2 -- view the system permissions of the hat user select * from dba_sys_privs where grantee = 'hat '; no data -- grant the hat user create table permission grant create table to hat; -- view the system permission of the hat user select * from Dba_sys_privs where grantee = 'hat'; grantee privilege adm ---------------------------- success --- hat create table no -- execution process begin p_createtable (); end; Execution successful www.2cto.com conclusion, the role resource also includes the create table permission, but the execution process (this process is a table creation process) reports an error, and we directly grant the create table permission to user hat, the stored procedure is successfully executed. This indicates that the user's role permissions cannot be inherited from the stored procedure. The stored procedure can be inherited only when the user is granted direct system permissions. Let's look at revoking the create table permission revoke create table from hat; -- Query again the hat user's permission select * from dba_sys_privs where grantee = 'hat '; no data -- execute the begin p_createtable (); end; ORA-01031: insufficient privileges ORA-06512: at "HAT. p_CREATETABLE ", line 6 ORA-06512: at line 2 still reports an error, just to verify the above statement. Www.2cto.com is there any other method besides directly granting the create table permission to the user? -- Modify the stored procedure and add the authid current_user option create or replace procedure p_createtable authid current_user as v_ SQL varchar2 (100); begin v_ SQL: = 'create table t_createtable (id integer, name varchar2 (10) '; execute immediate v_ SQL; end; -- execute the Stored Procedure begin p_createtable (); end; Execution success summary, if you want your role permissions to inherit from the stored procedure, add authid current_user when creating the stored procedure.