SET ROLE

SET ROLE
{ role_name [ IDENTIFIED BY password ][, role_name [ IDENTIFIED BY password ] ]...
| ALL [ EXCEPT role_name [, role_name ]... ]
| NONE
}

-- Role types
role_name:
    'role_name'     -- String form
  | name            -- Identifier form
  | DBA            -- Built-in role
  | RESOURCE
  | CONNECT
  | PUBLIC

-- 1. Create roles and tables
obclient> CREATE ROLE manager_role;
obclient> CREATE ROLE analyst_role;
obclient> CREATE TABLE employees(id INT, name VARCHAR(20));

-- 2. Grant role privileges
obclient> GRANT SELECT ON employees TO analyst_role;
obclient> GRANT ALL ON employees TO manager_role;

-- 3. Grant roles to users
obclient> CREATE USER john IDENTIFIED BY "Password123!";
obclient> GRANT manager_role, analyst_role TO john;

-- 4. Switch roles for the user
obclient> CONNECT john/password
obclient> SET ROLE manager_role;                     -- Use the administrator role
obclient> SET ROLE analyst_role;                     -- Switch to the analyst role
obclient> SET ROLE ALL;                             -- Enable all roles
obclient> SET ROLE ALL EXCEPT analyst_role;         -- Enable all roles except the analyst role
obclient> SET ROLE NONE;                            -- Disable all roles