Resource Pool and Rols



I want to creat emultiple roles for sifferent type of users.

In addition i want to assign each role to a different resource pool.


I couldnt find an option to acomplish this. i can assign a user to a deafult resource pool but than i will have to manage each user (very bad for me)


How can i assign a role to a specific resource pool?


Thank you


  • Hi,


    From my understanding, you wouldn't "assign each role to a different resource pool", you'd "assign (i.e. grant usage on) resource pools to roles ":


    dbadmin=> create role role1;

    dbadmin=> create resource pool pool1;

    dbadmin=> grant usage on resource pool pool1 to role1;

    dbadmin=> create user user1;

    dbadmin=> grant role1 to user1;

    dbadmin=> alter user user1 default role all;

    But doing that only gives the user the usage privilege to the resource pool. It does not change the his/her's default resource pool. Roles do not do that.


    You are still either going to have to change the user's default resource pool with the ALTER USER command, or use the SET SESSION RESOURCE POOL command.


    dbadmin=> \c dbadmin user1;
    You are now connected to database "dbadmin" as user "user1".
    dbadmin=> show resource_pool;
    name | setting
    resource_pool | general
    (1 row)

    dbadmin=> set session resource pool pool1;
    dbadmin=> show resource_pool;
    name | setting
    resource_pool | pool1
    (1 row)



  • Hi


    This is what i already done.

    I wanted to avoid dealing with specific users and manage all the permissions via rols.

    The world is not perfect... :)

  •  You cannot add a Resource Pool to a Role. 

     Roles are used with a different purpouse in DB admin, they offer the flexibility of assigning permissions to groups of users, instead of having to assign permissions individually. 

     I think that having Resource pools attached to a db role would make your life harder when doing maintainance.

  • Thnak you for your reply.

    If this is by design of the product i guess i have no choice but assigning each user a default resource pool.

  •  To be honest i don`t know any database engine that will allow you to attach a resource pool to a database role. 

     They are two different parts of a database engine, one is a logical object(role) and the other is fisical resource/memory structure(resource pool)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file