Cannot access table in PL/SQL even with direct grant [message #615631] |
Fri, 06 June 2014 05:21 |
|
d_seng
Messages: 78 Registered: November 2011 Location: UK
|
Member |
|
|
Hi, I am unable to create a table in a schema when performing a select as a table from another schema, in spite of the direct grant. Am I missing something obvious?
@> CONN GMIS_ODS/"xxxxx"@AGMISAT
Connected.
GMIS_ODS@AGMISAT>
GMIS_ODS@AGMISAT> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
GMIS_ODS@AGMISAT>
GMIS_ODS@AGMISAT> GRANT SELECT ON AAMC_D_AIRPORTCODES TO GMIS_ODS_COPY;
Grant succeeded.
GMIS_ODS@AGMISAT>
GMIS_ODS@AGMISAT> CONN GMIS_ODS_COPY/"xxxxx"@AGMISAT
Connected.
GMIS_ODS_COPY@AGMISAT>
GMIS_ODS_COPY@AGMISAT> SELECT * FROM SESSION_PRIVS WHERE PRIVILEGE IN ('UNLIMITED TABLESPACE','CREATE TABLE');
PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE
CREATE TABLE
GMIS_ODS_COPY@AGMISAT>
GMIS_ODS_COPY@AGMISAT> SELECT COUNT(*) FROM GMIS_ODS.AAMC_D_AIRPORTCODES;
COUNT(*)
----------
278
GMIS_ODS_COPY@AGMISAT>
GMIS_ODS_COPY@AGMISAT> CREATE OR REPLACE PROCEDURE TEST_COPY AS
2 BEGIN
3 EXECUTE IMMEDIATE('CREATE TABLE AAMC_D_AIRPORTCODES COMPRESS NOLOGGING AS SELECT * FROM GMIS_ODS.AAMC_D_AIRPORTCODES');
4 END;
5 /
Procedure created.
GMIS_ODS_COPY@AGMISAT>
GMIS_ODS_COPY@AGMISAT> EXECUTE TEST_COPY
BEGIN TEST_COPY; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "GMIS_ODS_COPY.TEST_COPY", line 3
ORA-06512: at line 1
GMIS_ODS_COPY@AGMISAT>
GMIS_ODS_COPY@AGMISAT> CREATE TABLE AAMC_D_AIRPORTCODES COMPRESS NOLOGGING AS SELECT * FROM GMIS_ODS.AAMC_D_AIRPORTCODES;
Table created.
GMIS_ODS_COPY@AGMISAT>
|
|
|
|
|
|
|