Showing posts with label Roles. Show all posts
Showing posts with label Roles. Show all posts

Thursday, June 5, 2025

create user in postgres - basic syntax

I will create

  • a group role called common_users
  • a user called ro_user1
  • in a database called db01

    The read-only user ro_user1 should be able perform queries against all tables owned by the schema schema1.

    First, create the role common_users by logging onto the postgres (default) database
    psql
    
    CREATE ROLE common_users WITH
      NOLOGIN
      NOSUPERUSER
      INHERIT
      NOCREATEDB
      NOCREATEROLE
      NOREPLICATION
      NOBYPASSRLS;
    
    GRANT pg_read_all_stats TO common_users;
    
    Then, create the user ro_user1:
    create user ro_user1 password 'mysecretpassword';
    grant common_users to ro_user1;
    grant connect on database db01 to ro_user1;
    
    Log into the database db01 and revoke and grant some privileges:
    psql
    \connect db01
    revoke all on schema schema1 from ro_user1;
    grant usage on schema schema1 to ro_user1;
    grant select on all tables in schema schema1 to ro_user1;
    
    Confirm the privileges:
    \connect postgres
    select database_privs('ro_user1');
    
  • Wednesday, September 4, 2019

    How to create a proxy authenticated user in Oracle



    Create a general user for authentication:
    CREATE USER APP_POOL
    IDENTIFIED BY app_pool_users123
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    PROFILE APP_USERS
    ACCOUNT UNLOCK;

    GRANT CREATE SESSION TO APP_POOL;

    To illustrate how proxy authentication can be used efficiently, I create two roles:
    create role app_pool_role1 not identified;
    create role app_pool_role2 not identified;

    Grant object privileges on two different tables to the two new roles:
    grant select on SCOTT.DEPT to app_pool_role1;
    grant select on SCOTT.EMP to app_pool_role2;

    Create a user:
    CREATE USER VEGARD
    IDENTIFIED BY vegard123
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    PROFILE APP_USERS
    ACCOUNT UNLOCK;

    Grant the ability to create sessions, and both the previously created roles to the new user, and enable them by default:
    GRANT CREATE SESSION TO VEGARD;
    GRANT APP_POOL_ROLE1 TO VEGARD;
    GRANT APP_POOL_ROLE2 TO VEGARD;
    ALTER USER VEGARD DEFAULT ROLE ALL;

    Change the user so it connects through a proxy user.
    Make sure that only the role APP_POOL_ROLE1 is enabled whenever the user connects through the proxy:
    ALTER USER VEGARD
    GRANT CONNECT THROUGH APP_POOL
    WITH ROLE APP_POOL_ROLE1;

    Let's connect to the database:
    sqlplus app_pool[vegard]/app_pool_users123@pdb01

    Verify the connection and session settings by using sys_context:
    select sys_context('USERENV','AUTHENTICATED_IDENTITY') "AuthIdentity used", 
           decode(sys_context('USERENV', 'AUTHENTICATION_METHOD'), 'NONE', 'proxy') "auth method",
           sys_context('USERENV','CURRENT_SCHEMA') "current schema",
           sys_context('USERENV','CURRENT_USER') "grantee of privileges used",
           decode(sys_context('USERENV','IDENTIFICATION_TYPE'), 'LOCAL', 'Password') "identification type",
           sys_context('USERENV','PROXY_USER') "proxy user"
    from dual;
    

    Results shows that user VEGARD is authenticated by proxy, the current schema is VEGARD, the privileges for the session belongs to user VEGARD, the identification type is password and the proxy user is APP_POOL:
    AuthIdentity used auth method current schema grantee of privileges used identification type proxy user
    VEGARD proxy VEGARD VEGARD Password APP_POOL

    Which roles are enabled?
    select 'APP_POOL_ROLE1: ' || sys_context('sys_session_roles','APP_POOL_ROLE1') "Role granted?"
    from dual
    union
    select 'APP_POOL_ROLE2: ' || sys_context('sys_session_roles','APP_POOL_ROLE2')
    from dual
    ;

    Result shows that only APP_POOL_ROLE1 is enabled:
    Role granted?
    APP_POOL_ROLE1: TRUE
    APP_POOL_ROLE2: FALSE

    Let's verify that the roles are actually working.
    Logged in as user VEGARD, I now expect to be able to query the table scott.dept and nothing else:
    VEGARD@pdb01 SQL> select count(*) from scott.emp;
    select count(*) from scott.emp
                               *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    VEGARD@pdb01 SQL> select count(*) from scott.dept;
    
      COUNT(*)
    ----------
             4
    

    There is nothing preventing the user VEGARD from connecting directly to the database:
    sqlplus vegard/vegard123@pdb01
    VEGARD@pdb01 SQL> show user
    USER is "VEGARD"
    

    When bypassing the proxy user, the users have access to both roles APP_POOL_ROLE1 as well as APP_POOL_ROLE2, and may now access both scott.emp and scott.dept:
    VEGARD@vegdb01 SQL> select count(*) from scott.emp;
    
      COUNT(*)
    ----------
            14
    
    

    Some advantages with using proxy connections:

    * No more password administration for individual users
    * Proxy authentication allows for role based access control
    * Identity preservation of the real user behind the proxy user is automatically set up

    Keep in mind that you still have to create and provision the end user accounts.

    Wednesday, November 28, 2018

    Changes in privilege "SELECT ANY DICTIONARY" in Oracle 12c


    From version 12.1 and onwards, Oracle has introduced some changes to enhance security when granting the system privilege "SELECT ANY DICTIONARY".

    In the New Features guide for version 12.1, the authors explain:

    The SELECT ANY DICTIONARY privilege no longer permits access to security sensitive data dictionary tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS.

    This change increases the default security of the database by not allowing access to a subset of data dictionary tables through the SELECT ANY DICTIONARY privilege.

    The access to USER$ has also been excempt when granting the system privilege SELECT ANY TABLE and the role SELECT_CATALOG_ROLE, but I have not yet found the documentation that verifies this.

    Tuesday, July 12, 2016

    Overview of role privileges

    The following UNION can be handy to get an overview of what system privileges, object privileges and other roles a specific role may contain:
    Select  Role || ' contains role:' "role privilege type", Granted_Role "privilege"   From Role_Role_Privs Where Role='ONLINE_USERS'
    Union
    Select Role || ' contains system priv:', Privilege      From Role_Sys_Privs  Where Role='ONLINE_USERS'
    Union
    Select Role || ' contains object priv:',  Privilege || ' ON ' || owner || '.' || table_name From Role_Tab_Privs Where Role='ONLINE_USERS'
    order by 1;
    

    Output:


    role privilege type privilege
    ONLINE_USERS contains object priv: UPDATE ON SCOTT.EMP
    ONLINE_USERS contains role: RESOURCE
    ONLINE_USERS contains system priv: UPDATE ANY TABLE

    How does Oracle decide which roles should be enabled in a session?

    Oracle uses the concept of default roles to decide whether or not a role should be enabled when a user establishes a session.

    CREATE USER ERIC
    IDENTIFIED BY pass1w0rd
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP;
    
    ALTER USER ERIC QUOTA UNLIMITED ON USERS;
    
    GRANT CONNECT, RESOURCE TO ERIC; 
    

    Oracle states in its documentation

    "When you first create a user, the default user role setting is ALL, which causes all roles subsequently granted to the user to be default roles."

    So we should have two default roles enabled for our user ERIC, confirmed below:
    SQL> connect eric/pass1w0rd
    
    Connected.
    
    SELECT * FROM SESSION_ROLES;
    
    ROLE
    -------
    CONNECT
    RESOURCE
    


    Let's create a new role:
    CREATE ROLE online_users NOT IDENTIFIED;
    
    Role created.
    
    Because the user ERIC was created with ALL roles enabled by default, all subsequent sessions established by ERIC will now have access to the role online_users:
    GRANT online_users to ERIC; 
    
    Grant succeeded.
    
    connect eric/Pass1w0rd
    
    Connected.
    
    SELECT * FROM SESSION_ROLES;
    
    ROLE
    -------
    CONNECT
    RESOURCE
    ONLINE_USERS
    

    If you want to prevent this behavior, you can alter the user with a limited default setting:
    ALTER USER ERIC DEFAULT ROLE CONNECT;
    
    User altered.
    
    connect eric/Pass1w0rd
    
    Connected.
    
    SELECT * FROM SESSION_ROLES;
    
    ROLE
    -------
    CONNECT
    

    User ERIC must now explicitly enable the required roles from within his session:
    set role connect, online_users;
    
    Role set.
    
    SELECT * FROM SESSION_ROLES;
    
    ROLE
    -------
    CONNECT
    ONLINE_USERS
    

    These features can be incorporated into a security-oriented application setup, where specific people should only be granted specific privileges through database roles.

    Monday, January 12, 2015

    Getting ORA-01031: insufficient privileges when data dictionary table is being used in a view

    I must admit I have been consulted in these situations before, but since then I had forgotten how it worked and failed to take notes on how to solve it.

    So here it is: a user is getting a run-time error ORA-01031: insufficient privileges when accessing his view.
    The view is based on his own objects and a lookup to the dynamic performance view V$DATABASE.

    Example:

    connect scott/tiger
    
    CREATE VIEW MYVIEW AS
    SELECT 
    FROM MYTABLE MT,
         V$DATABASE DB
    WHERE....
    AND... ;
    

    If the user has only SELECT ANY TABLE, Oracle will return runtime error ORA-01031 when the view is compiled.

    However, if you give user scott the SELECT privilege on the table directly:

    GRANT SELECT ON V_$DATABASE TO SCOTT;
    

    then Oracles rules for object creation is honored and the runtime error will disappear.

    Wednesday, October 15, 2014

    Why aren't other schemas' procedures showing in TOADs schema browser?

    Problem: User SCOTT has been granted SELECT on a number of tables belonging to another schema, OBM.
    They all show up neatly in TOADs schema browser.

    However, procedures and sequences are not visible. Why?

    Answer: lack of privileges.

    Solution: Try granting the following as user sys:

    GRANT SELECT ON OBM.sequence1 to SCOTT;
    GRANT DEBUG ON OBM.proceure1 to SCOTT;
    

    Refresh the schema browser - voila!

    To generate a list of objects with grant statements:

    SELECT 'GRANT DEBUG ON ' || OWNER || '.' || OBJECT_NAME || ' TO SCOTT;' 
    FROM DBA_PROCEDURES 
    WHERE OWNER = 'OBM' 
    AND OBJECT_TYPE='PROCEDURE';
    
    SELECT 'GRANT SELECT ON ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME || ' TO SCOTT;' 
    FROM DBA_SEQUENCES 
    WHERE SEQUENCE_OWNER = 'OBM';
    


    Alternatively, if acceptable in your environment you could grant select and debug on all sequences and procedures, respectively:
    GRANT SELECT ANY SEQUENCE TO SCOTT;
    GRANT DEBUG ANY PROCEDURE TO SCOTT;
    

    Friday, November 1, 2013

    Can you revoke all object privileges from a role in one go?

    Answer: No.

    The closest shortcut you get is to revoke all privileges per object from a role.

    Example:

    REVOKE ALL ON SCOTT.T1 FROM TEST_ROLE;