文章存档
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2008-07-02 21:54:19
sql> select * from v$version;
banner
----------------------------------------------------------------
oracle database 10g enterprise edition release 10.2.0.1.0 - prod
pl/sql release 10.2.0.1.0 - production
core 10.2.0.1.0 production
tns for linux: version 10.2.0.1.0 - production
nlsrtl version 10.2.0.1.0 - production
sql> select * from dba_role_privs where grantee = 'gan';
grantee granted_role adm def
------------------------------ ------------------------------ --- ---
gan connect no yes
gan dba no yes
sql> @proc_create_tab.sql
procedure created.
sql> l
1 create or replace procedure p_create_table
2 is
3 begin
4 execute immediate 'create table tab2(col1 int)';
5* end;
sql> exec p_create_table;
begin p_create_table; end;
*
error at line 1:
ora-01031: insufficient privileges
ora-06512: at "gan.p_create_table", line 4
ora-06512: at line 1
============================
modify the proc_create_tab.sql file:
sql> @proc_create_tab.sql
procedure created.
sql> l
1 create or replace procedure p_create_table
2 authid current_user is
3 begin
4 execute immediate 'create table tab2(col1 int)';
5* end;
sql> exec p_create_table;
pl/sql procedure successfully completed.
learn from:
===============================
oracle document info:
authid current_user
specify current_user to indicate that the procedure executes with the privileges of current_user. this clause creates an invoker-rights procedure.
this clause also specifies that external names in queries, dml operations, and dynamic sql statements resolve in the schema of current_user. external names in all other statements resolve in the schema in which the procedure resides.
authid definer
specify definer to indicate that the procedure executes with the privileges of the owner of the schema in which the procedure resides, and that external names resolve in the schema where the procedure resides. this is the default and creates a definer-rights procedure.
banner
----------------------------------------------------------------
oracle database 10g enterprise edition release 10.2.0.1.0 - prod
pl/sql release 10.2.0.1.0 - production
core 10.2.0.1.0 production
tns for linux: version 10.2.0.1.0 - production
nlsrtl version 10.2.0.1.0 - production
sql> select * from dba_role_privs where grantee = 'gan';
grantee granted_role adm def
------------------------------ ------------------------------ --- ---
gan connect no yes
gan dba no yes
sql> @proc_create_tab.sql
procedure created.
sql> l
1 create or replace procedure p_create_table
2 is
3 begin
4 execute immediate 'create table tab2(col1 int)';
5* end;
sql> exec p_create_table;
begin p_create_table; end;
*
error at line 1:
ora-01031: insufficient privileges
ora-06512: at "gan.p_create_table", line 4
ora-06512: at line 1
============================
modify the proc_create_tab.sql file:
sql> @proc_create_tab.sql
procedure created.
sql> l
1 create or replace procedure p_create_table
2 authid current_user is
3 begin
4 execute immediate 'create table tab2(col1 int)';
5* end;
sql> exec p_create_table;
pl/sql procedure successfully completed.
learn from:
===============================
oracle document info:
authid current_user
specify current_user to indicate that the procedure executes with the privileges of current_user. this clause creates an invoker-rights procedure.
this clause also specifies that external names in queries, dml operations, and dynamic sql statements resolve in the schema of current_user. external names in all other statements resolve in the schema in which the procedure resides.
authid definer
specify definer to indicate that the procedure executes with the privileges of the owner of the schema in which the procedure resides, and that external names resolve in the schema where the procedure resides. this is the default and creates a definer-rights procedure.
给主人留下些什么吧！~~