你只有create view
直接授予您的用户。您可以看到的其他系统权限来自角色,并且角色在定义者权限存储过程中被禁用。在看user_role_privs
查看您已被授予的角色,并且您可以查看每个角色授予您哪些权限role_sys_privs
(角色名称为受让人)。也可能有多层角色。
如果您这样做,您会看到同样的错误set role none
在尝试静态创建表之前。具有最少设置的演示:
create role myrole;
grant create session, create table, create procedure to myrole;
create user myuser identified by mypasswd;
grant myrole to myuser;
grant create view, unlimited tablespace to myuser;
然后作为该用户:
SQL> connect myuser/mypasswd
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
MYUSER UNLIMITED TABLESPACE NO
MYUSER CREATE VIEW NO
2 rows selected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE
5 rows selected.
SQL> Create table Dummy99_99 (Dummy_Field number);
Table created.
SQL> drop table Dummy99_99 purge;
Table dropped.
SQL> set role none;
Role set.
SQL> Create table Dummy99_99 (Dummy_Field number);
Create table Dummy99_99 (Dummy_Field number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
并使用您的存储过程版本:
SQL> connect myuser/mypasswd
Connected.
SQL> create or replace procedure sp_dummy
2 as
3 begin
4 execute immediate 'Create table Dummy99_99 (Dummy_Field number)';
5 end sp_dummy;
6 /
Procedure created.
SQL> exec sp_dummy;
BEGIN sp_dummy; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "MYUSER.SP_DUMMY", line 4
ORA-06512: at line 1
为了能够从存储过程动态创建表,您的 DBA 需要授予create table
直接发送给您的用户:
grant create table to myuser;
然后再次尝试该过程:
SQL> connect myuser/mypasswd
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
MYUSER UNLIMITED TABLESPACE NO
MYUSER CREATE TABLE NO
MYUSER CREATE VIEW NO
SQL> exec sp_dummy;
PL/SQL procedure successfully completed.
SQL> desc Dummy99_99
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY_FIELD NUMBER
请注意user_sys_privs
现在表明create table
已经被直接授予,而之前或在问题中没有。
然而,您不太可能真正想要动态创建对象,因为模式应该定义良好并且稳定 - 这种类型的更改应该受到控制并成为发布过程的一部分。但作为一项练习,您需要直接拨款。