4

I want to perform some dynamic DDL in a procedure owned by an admin user. I'd like to execute this procedure with a technical operational user with definer rights (operational user doesn't have the create table role).

The problem is the 'create table' permission is granted to the admin user through use of a role, which doesn't allow me to execute the DDL as it seems that roles don't count in named pl/sql blocks.

create or replace
PROCEDURE test_permissions AUTHID DEFINER AS
  v_query_string VARCHAR2(400 CHAR) := 'CREATE TABLE TEST(abcd VARCHAR2(200 CHAR))';
BEGIN

  EXECUTE IMMEDIATE v_query_string;

END;

What I tried:

  • Running the procedure from a function set to AUTHID DEFINER with AUTHID CURRENT_USER on the proc (hoping the function would cascade the definer somehow)
  • Putting an anonymous block inside the function to execute the DDL (as roles seem to count in anonymous block)

If I set the AUTHID to CURRENT_USER, I can execute the procedure correctly with the admin user.

Is there any way I can work around this without granting CREATE TABLE directly to the admin user?

JochenDB
  • 588
  • 10
  • 31
  • Can't you just create a Procedure/Function that does that in the admin's schema and grant execute on that Procedure/ Function to the user that you have in mind. That way you can achieve isolation + limit injections, if you intended on leaving the query open and just use execute immediate with whatever gets passed. – g00dy Jul 31 '17 at 09:20
  • 1
    Indeed that's what I intend to do, but the admin doesn't have direct grants to create tables, only through a role. This seems to make it impossible to execute the procedure with definer rights, even if it's executed by the admin user itself. – JochenDB Jul 31 '17 at 09:26
  • 1
    Why don't you want to grant CREATE TABLE to the admin user? – APC Jul 31 '17 at 09:26
  • I'm not sure if it's allowed by the company policy. First wanted to check if I'm missing a way to do it with permissions I already have. – JochenDB Jul 31 '17 at 09:27
  • Your user that calls this Procedure, should have grant execute on it, then `AUTHID DEFINER` is a must, as when it gets called, it should be executed with the user that owns it. AUTHID CURRENT_USER/DEFINER is the same, if you are executing it with the admin (that owns it). – g00dy Jul 31 '17 at 09:32
  • @g00dy The problem is that the create table permission is granted through the use of a role to the admin user. The role permissions are lost in a named procedure executed with definer rights. – JochenDB Jul 31 '17 at 09:35
  • 2
    If CREATE TABLE is not permitted by company policy then it should be also forbidden by role. Ask your DBA to grant this privilege to your user. – Wernfried Domscheit Jul 31 '17 at 09:37
  • You could use `'CREATE TABLE '||other_user||'.TEST(abcd VARCHAR2(200 CHAR))'` - but this requires even `CREATE ANY TABLE` which is much stronger and many DBA's may reject this. – Wernfried Domscheit Jul 31 '17 at 09:40
  • @WernfriedDomscheit Will having CREATE TABLE permission also allow me to ALTER and DROP the admin-owned tables? – JochenDB Jul 31 '17 at 10:02
  • 1
    Yes, CREATE TABLE confers ALTER and DROP privileges on the schema's objects. – APC Jul 31 '17 at 10:12

1 Answers1

2

You can only set a role within a PL/SQL stored procedure/function if it has Invoker's Rights (AUTHID CURRENT_USER)(see doc). Which means that you can't use ops_user to call admin_user's procedure and then access admin_user's roles. If your DBAs insist on using a role to control the CREATE TABLE privilege, here's the approach I've seen before:

create or replace package admin_user.role_test authid current_user is
    procedure test_permissions;
end role_test;
/
create or replace package body admin_user.role_test is
    procedure test_permissions is
        v_query_string VARCHAR2(400 CHAR) := 'begin 
dbms_output.put_line(''after'');
for r in (select role from session_roles) loop 
    dbms_output.put_line(r.role); 
end loop;
end;';
    begin
        dbms_output.put_line('before');
        for r in (select role from session_roles) loop
            dbms_output.put_line(r.role);
        end loop;
        DBMS_SESSION.SET_ROLE('CREATE_TABLE_ROLE IDENTIFIED BY "SECRET_PASSWORD"');
        execute immediate v_query_string;
        DBMS_SESSION.SET_ROLE('ALL EXCEPT CREATE_TABLE_ROLE'); -- restore defaults
    end;
end role_test;
/
grant execute on admin_user.role_test to ops_user;

This will temporarily grant the role to ops_user just to execute your code. By default the ops_user should not be able to view the admin_user's package body source. You could probably wrap the package body to further protect the password. But password security aside, my biggest concern with this approach is that Oracle doesn't provide a nice way to disable a single role, so if ops_user has other password-protected roles, this code might raise an ORA-01979 when it tries to restore them.

So, there's an answer, but I'd still recommend doing what the other commenters suggested and granting CREATE TABLE to your admin user.

kfinity
  • 8,581
  • 1
  • 13
  • 20
  • Thanks for the alternative. As you suggested as well, I will try first to get a direct CREATE TABLE grant for my admin user. – JochenDB Aug 01 '17 at 09:08