"Role" permission in Oracle database Stored Procedures

Many people know that the role permissions of users are not available in the stored procedure. Example:

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

The user SUK owns the DBA role.

Next we will continue to create a test stored procedure:

Create or replace procedure p_create_table

Is

Begin

Execute Immediate 'create table create_table (id int )';

End p_create_table;

Test:

SQL> exec p_create_table;

Begin p_create_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 DBA role, you cannot create a table. Role is not available in the stored procedure.

In this case, we generally need to explicitly grant system permissions, such as grant create table to suk;

However, this method is too troublesome. Sometimes it may take a lot of authorization to execute the stored procedure.

In fact, Oracle provides us with a method to use the role permission in the stored procedure:

Modify the stored procedure. You can use the role permission when adding Authid Current_User to the stored procedure.

Create or replace procedure p_create_table

Authid Current_User is

Begin

Execute Immediate 'create table create_table (id int )';

End p_create_table;

Then proceed with the execution:

SQL> exec p_create_table;

PL/SQL procedure successfully completed

So far, the task can be executed.