Shulou(Shulou.com)06/01 Report--

Create or replace procedure p_create_table

Authid Current_User is

Begin

Execute Immediate 'create table create_table (id int)'

End p_create_table

Red font is the key to enable users to use permissions to execute execute

We know that the role permissions that users have are not available in stored procedures. In this case, we generally need to explicitly carry out system permissions, such as grant create table to suk;, but this method is too troublesome, and sometimes it may take a lot of authorization to execute stored procedures. In fact, oracle provides us with a way to use role permissions in stored procedures: modify stored procedures and use role permissions when adding Authid Current_User.

We know that the role permissions that users have are not available in stored procedures. Such as:

SQL > select * from dba_role_privs where grantee='SUK'

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

--

SUK DBA NO YES

SUK CONNECT NO YES

SUK RESOURCE NO YES

User SUK owns the role of DBA.

Create another test stored procedure:

Create or replace procedure p_create_table

Is

Begin

Execute Immediate 'create table create_table (id int)'

End p_create_table

And then test.

SQL > exec p_create_table

Begin created table; end

ORA-01031: insufficient permissions

ORA-06512: in "SUK.P_CREATE_TABLE", line 3

ORA-06512: in line 1

As you can see, even if you have a DBA role, you cannot create a table. Role is not available in stored procedures. In this case, we generally need to explicitly implement system permissions, such as grant create table to suk

But this approach is too cumbersome, and sometimes it may require a lot of authorization to execute the stored procedure. In fact, oracle gives us a way to use role permissions in stored procedures:

Modify the stored procedure so that it can use role permissions when joining Authid Current_User.

Create or replace procedure p_create_table

Authid Current_User is

Begin

Execute Immediate 'create table create_table (id int)'

End p_create_table

Try to execute again:

SQL > exec p_create_table

PL/SQL procedure successfully completed

It's ready to be executed.

=

By default, the stored procedure is called under the identity of the definer definer. If AUTHID CURRENT_USER is added, the caller of the procedure is called with the user rights currently logged in. If the caller of the procedure (rather than the definer) is granted the system permission execute any procedure or authorized by the definer grant execute on of the procedure, the caller can still use the procedure without using this AUTHID CURRENT_ user clause.

In addition, in the storage process of Oracle, if it involves operating objects under different schema, you can write the same procedure under different schema, but the problem is to maintain and synchronize the procedure, and you can add authid current_user to the procedure to prove that the object in procedure is the object used before the connection, not the object under the procedure.

-- USER01

USER01@HUIYI > create table t

2 (

3 col1 varchar2 (10)

4)

5 /

Table created.

USER01@HUIYI > insert into t values (user)

1 row created.

USER01@HUIYI > commit

Commit complete.

USER01@HUIYI > select * from t

COL1

-

USER01

-- USER02

USER02@HUIYI > create table t

2 (

3 col1 varchar2 (10)

4)

5 /

Table created.

USER02@HUIYI > insert into t values (user)

1 row created.

USER02@HUIYI > commit

Commit complete.

USER02@HUIYI > select * from t

COL1

-

USER02

USER02@HUIYI > create or replace procedure pro_01

2 is

3 l_col1 varchar2 (10)

4 begin

5 select col1 into l_col1 from t

6 dbms_output.put_line (l_col1)

7 end

8 /

Procedure created.

USER02@HUIYI > create or replace procedure pro_02

2 authid current_user

3 is

4 l_col1 varchar2 (10)

5 begin

6 select col1 into l_col1 from t

7 dbms_output.put_line (l_col1)

8 end

9 /

Procedure created.

USER02@HUIYI > grant all on pro_01 to public

Grant succeeded.

USER02@HUIYI > grant all on pro_02 to public

Grant succeeded.

USER02@HUIYI > call pro_01 ()

USER02

Call completed.

USER02@HUIYI > call pro_02 ()

USER02

Call completed.

-- USER01

USER01@HUIYI > select * from t

COL1

-

USER01

USER01@HUIYI > call user02.pro_01 ()

USER02

Call completed.

USER01@HUIYI > call user02.pro_02 ()

USER01

Call completed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.