Home » SQL & PL/SQL » SQL & PL/SQL » Create table can not be executed in procedure? (oracle 10.2.0 RHEL5)
Create table can not be executed in procedure? [message #340437] Tue, 12 August 2008 20:35 Go to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
this code can be execute successfully:
begin
  EXECUTE IMMEDIATE '
  create table test_info_cdma as
  select * from busi.info_cdma@db2
  where rownum <= 10
  ';
END;


but this can just be complied successfully:
create or replace procedure test is
begin
  EXECUTE IMMEDIATE '
  create table test_info_cdma as
  select * from busi.info_cdma@db2
  where rownum <= 10
  ';
end test;


if I execute the procedure test, I get this message:
ORA-01031: privilege not enough
ORA-06512: on "ORI.TEST", line 3
ORA-06512: on line 1


and both of the create and execute user are 'ori', she has this privilege:
ORI	CREATE PROCEDURE
ORI	CREATE JOB
ORI	UNLIMITED TABLESPACE
ORI	SELECT ANY TABLE


the user ori can do this:
create or replace procedure test2 is
begin
  EXECUTE IMMEDIATE '
  create table test_info_cdma as
  select * from orb.info_cdma
  where rownum <= 10
  ';
end test2;

the procedure test2 can be executed.

so, my question is , what privilege do the user ori need?
thanks.
Re: Create table can not be executed in procedure? [message #340438 is a reply to message #340437] Tue, 12 August 2008 21:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Privs acquired via ROLE do not apply within PL/SQL procedures.
Do complete CUT & PASTE of the code below while using sqlplus.
SET ROLE NONE
begin
  EXECUTE IMMEDIATE '
  create table test_info_cdma as
  select * from busi.info_cdma@db2
  where rownum <= 10
  ';
END;
/
Re: Create table can not be executed in procedure? [message #340453 is a reply to message #340437] Wed, 13 August 2008 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

Regards
Michel
Re: Create table can not be executed in procedure? [message #340462 is a reply to message #340437] Wed, 13 August 2008 00:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
crazyzhou wrote on Wed, 13 August 2008 03:35

if I execute the procedure test, I get this message:
ORA-01031: privilege not enough
ORA-06512: on "ORI.TEST", line 3
ORA-06512: on line 1



If you are translating or in any way modifying the results you get, please indicate so. In this case it was easy to spot, but you make it look as though this was copied verbatim from the sqlplus output.
It could be confusing if you did this without telling.
Re: Create table can not be executed in procedure? [message #340527 is a reply to message #340462] Wed, 13 August 2008 03:30 Go to previous messageGo to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
Frank wrote on Wed, 13 August 2008 00:46
crazyzhou wrote on Wed, 13 August 2008 03:35

if I execute the procedure test, I get this message:
ORA-01031: privilege not enough
ORA-06512: on "ORI.TEST", line 3
ORA-06512: on line 1



If you are translating or in any way modifying the results you get, please indicate so. In this case it was easy to spot, but you make it look as though this was copied verbatim from the sqlplus output.
It could be confusing if you did this without telling.


Yes, I translated it from Chinese. the Oracle documents describe the message like this:
ORA-01031 insufficient privileges

Re: Create table can not be executed in procedure? [message #340541 is a reply to message #340437] Wed, 13 August 2008 04:28 Go to previous messageGo to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
Let me describe the situation in another way:
I have three user "ori', "orb","cdc" on the database. user ori has the same system privilege and roles as the user orb, but orb can create and execute procedure successfully, but ori can not, both of their procedure have the same code and used a table owned by user cdc. and I have specified AUTHID CURRENT_USER when the procedure owned by ori was created. but the message ORA-01031 still there when executed the procedure .
what should I do?
thanks again.
Re: Create table can not be executed in procedure? [message #340559 is a reply to message #340541] Wed, 13 August 2008 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste the whole script and the execution of what you did.

Regards
Michel
Re: Create table can not be executed in procedure? [message #340653 is a reply to message #340559] Wed, 13 August 2008 11:22 Go to previous messageGo to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
the user orb:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as orb

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
ORB                            SELECT ANY TABLE                         NO
ORB                            UNLIMITED TABLESPACE                     NO

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
------------------------------ ------------------------------ ------------ ------------ ----------
ORB                            CONNECT                        NO           YES          NO
ORB                            RESOURCE                       NO           YES          NO

SQL> 
SQL> CREATE OR REPLACE PROCEDURE test
  2  AUTHID CURRENT_USER IS
  3  BEGIN
  4    EXECUTE IMMEDIATE '
  5      CREATE TABLE TEST_CODE_GRID AS
  6      SELECT * FROM CDC.CODE_GRID WHERE ROWNUM <= 5
  7    ';
  8  END;
  9  /

Procedure created

SQL> exec test;

PL/SQL procedure successfully completed

SQL> 


the user ori:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as ori

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
ORI                            UNLIMITED TABLESPACE                     NO
ORI                            SELECT ANY TABLE                         NO
ORI                            CREATE PROCEDURE                         NO
ORI                            CREATE JOB                               NO

SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
------------------------------ ------------------------------ ------------ ------------ ----------
ORI                            CONNECT                        NO           YES          NO
ORI                            RESOURCE                       NO           YES          NO

SQL> ed
SQL> ED
SQL> 
SQL> CREATE OR REPLACE PROCEDURE test
  2  AUTHID CURRENT_USER IS
  3  BEGIN
  4    EXECUTE IMMEDIATE '
  5      CREATE TABLE TEST_CODE_GRID AS
  6      SELECT * FROM CDC.CODE_GRID WHERE ROWNUM <= 5
  7    ';
  8  END;
  9  /

Procedure created

SQL> exec test;

begin test; end;

ORA-01031: 权限不足(insufficient privilege)
ORA-06512: 在 "ORI.TEST", line 4
ORA-06512: 在 line 1

SQL> 

Re: Create table can not be executed in procedure? [message #340654 is a reply to message #340653] Wed, 13 August 2008 11:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Does this user have select privs on CDC.CODE_GRID?
(Directly, not through a role)
Re: Create table can not be executed in procedure? [message #340655 is a reply to message #340437] Wed, 13 August 2008 11:29 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
see my previous response in this thread
Re: Create table can not be executed in procedure? [message #340659 is a reply to message #340437] Wed, 13 August 2008 13:24 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
wHERE IS THE CREATE TABLE PRIVILEDGE?
Re: Create table can not be executed in procedure? [message #340691 is a reply to message #340659] Wed, 13 August 2008 15:21 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
I believe the RESOURCE privilege gives it.

That is why it is important not to give pirvileges like the old legacy days of
grant connect,resource to...

[Updated on: Wed, 13 August 2008 15:22]

Report message to a moderator

Re: Create table can not be executed in procedure? [message #340703 is a reply to message #340437] Wed, 13 August 2008 20:26 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Quote:

I believe the RESOURCE privilege gives it.


Rrrrmmmm.......
Excuse me. "RESOURCE privilege"?
 1* select role from dba_roles order by 1
SQL> /

ROLE
------------------------------
AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE
AUTHENTICATEDUSER
CONN
CONNECT
CTXAPP
DBA
DELETE_CATALOG_ROLE
EJBCLIENT
EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
GLOBAL_AQ_USER_ROLE
HS_ADMIN_ROLE
IMP_FULL_DATABASE
JAVADEBUGPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVAUSERPRIV
JAVA_ADMIN
JAVA_DEPLOY
LOGSTDBY_ADMINISTRATOR
MGMT_USER
OEM_ADVISOR
OEM_MONITOR
OLAP_DBA
OLAP_USER
RECOVERY_CATALOG_OWNER
RESOURCE
SCHEDULER_ADMIN
SELECT_CATALOG_ROLE
WM_ADMIN_ROLE
XDBADMIN
XDBWEBSERVICES

34 rows selected.


Priveleges acquired via ROLEs do NOT apply within PL/SQL procedures.

[Updated on: Wed, 13 August 2008 20:27] by Moderator

Report message to a moderator

Re: Create table can not be executed in procedure? [message #340871 is a reply to message #340703] Thu, 14 August 2008 13:16 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Yeah, yeah, I made a tounge-o (typo of the mouth).
Previous Topic: rc4 encryption in 9i
Next Topic: CREATE VIEW
Goto Forum:
  


Current Time: Fri Dec 09 00:04:49 CST 2016

Total time taken to generate the page: 0.05586 seconds