IBM Support

Creating read-only database permissions for a user

Question & Answer


Question

How do you create a read-only user for a given database?

Answer

This article describes the process used to create a user account that is able to do SELECTs and LISTs on tables/views for a given database.

1. As the admin user on the system database, create the user account by entering the following:

    create user <username> with password '<password>';

2. Connect to the user's home database.

3. As the admin user on the user's home database, grant limited permissions by entering the following:
    grant select on system table to <username>;
    grant select on system view to <username>;
    grant select on table to <username>;
    grant select on view to <username>;
    grant list on test to <username>;

4. As the newly created user, test the database connectivity.

The following is an example of creating user account ("reed") with read-only access to the "test" database:
    SYSTEM(ADMIN)=> create user reed with password 'read';
    CREATE USER
    SYSTEM(ADMIN)=> \c test
    You are now connected to database test.
    TEST(ADMIN)=> grant select on system table to reed;
    GRANT
    TEST(ADMIN)=> grant select on system view to reed;
    GRANT
    TEST(ADMIN)=> grant select on table to reed;
    GRANT
    TEST(ADMIN)=> grant select on view to reed;
    GRANT
    TEST(ADMIN)=> grant list on test to reed;
    GRANT

The following example shows a connectivity test for the reed user account:
    [nz@proddb1 bin]$ nzsql -u reed -pw read test

    Welcome to nzsql, the Netezza SQL interactive terminal.

    Type: \h for help with SQL commands
    \? for help on internal slash commands
    \g or terminate with semicolon to execute query
    \q to quit

    TEST(REED)=> \d
    List of relations
    Name | Type | OWNER
    ---------------------------+-------+-------
    ORDERS  | TABLE | ADMIN
    (5 rows)

    TEST(REED)=>\q

[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

NZ510172

Document Information

Modified date:
17 October 2019

UID

swg21574293

Page Feedback