How to have role permissions in Oracle stored procedures

We know that the role permissions of users are not available in the stored procedure. For 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.

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;

Then 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;

Try again:

SQL> exec p_create_table; PL/SQL procedure successfully completed

It can be executed.

(