一般创建用户会给予resource和connect权限,但是这两种权限没有存储过程
1.检查test用户权限
SQL> select * from dba_role_privs where grantee='TEST';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST CONNECT NO YES
2. 测试
--test用户执行
SQL> create or replace procedure p_create_table
is
begin
Execute Immediate 'create table test(id int)';
end ;
/
create or replace procedure p_create_table
*
ERROR at line 1:
ORA-01031: insufficient privileges
--sys用户赋权
SQL> grant create any procedure to test;
SQL> grant execute any procedure to test;
SQL> alter user test quota unlimited on users;
--test用户重新创建,执行存储过程
SQL> create or replace procedure p_create_table
is
begin
Execute Immediate 'create table test(id int)';
end ;
/
Procedure created.
SQL> exec p_create_table;
SQL> insert into test (id) values (3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID
----------
3