Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2004
    Posts
    29

    Unanswered: How to make DB2 database Read-only?????

    Hi Folks,

    I need to make the currently running DB2 database as Read-Only so that no updates, inserts or deletes are allowed. Here's what I tried:

    There is a database MyDB accessed by the schema user MyUser. So, I logged in as the DB2 Instance owner and revoked the control,insert,update and delete privileges from the user "MyUser" for all the tables and granted only "select" privilege to that user. When I looked at the "syscat.tabauth" table, it does show that the tables have only select privileges for this user.

    But the catch is even after this I am able to update and insert into these tables without any problem. I am not able to figure out what I am doing wrong. Is there a better way of making the database read-only. Any help will be greatly appreciated.

    Thanks

    - Raj

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    You are probably logged in at some kind of administrative level which has full access by default.

  3. #3
    Join Date
    Oct 2004
    Posts
    29
    Here's How I am logging in as and it's authorizations. Do I need to make any changes to the authorizations??

    bash-2.05$ db2 connect to MyDB user MyUser using My$user

    Database Connection Information

    Database server = DB2/6000 7.2.9
    SQL authorization ID = MyUser
    Local database alias = MyDB

    bash-2.05$ db2 get authorizations

    Administrative Authorizations for Current User

    Direct SYSADM authority = NO
    Direct SYSCTRL authority = NO
    Direct SYSMAINT authority = NO
    Direct DBADM authority = YES
    Direct CREATETAB authority = YES
    Direct BINDADD authority = YES
    Direct CONNECT authority = YES
    Direct CREATE_NOT_FENC authority = YES
    Direct IMPLICIT_SCHEMA authority = YES
    Direct LOAD authority = YES

    Indirect SYSADM authority = NO
    Indirect SYSCTRL authority = NO
    Indirect SYSMAINT authority = NO
    Indirect DBADM authority = NO
    Indirect CREATETAB authority = YES
    Indirect BINDADD authority = YES
    Indirect CONNECT authority = YES
    Indirect CREATE_NOT_FENC authority = NO
    Indirect IMPLICIT_SCHEMA authority = YES
    Indirect LOAD authority = NO

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    The direct DBADM looks like the administrative authority you have which allows you to GRANT access and manipulate the data. Try logging in as a regular user. DBADM has SELECT, INSERT, UPDATE, DELETE authority by default. If you want to make the database read-only, you could try START DATABASE(database name) ACCESS(RO) -- not sure if it will work with a non-z/os db.
    Last edited by urquel; 12-06-04 at 15:40.

  5. #5
    Join Date
    Oct 2004
    Posts
    29
    There is only one user connecting to the database. Is there a way to revoke the DBAADM privileges for this user?

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    Did you try START DATABASE(database name) ACCESS(RO) ?

  7. #7
    Join Date
    Oct 2004
    Posts
    29
    I am using UDB 7.2 version on AIX. The command fails with syntax error;

    bash-2.05$ db2 "? start database"
    {START DATABASE MANAGER | DB2START} [PROFILE profile] [ NODENUM node-number [
    ADDNODE HOSTNAME hostname PORT logical-port [COMPUTER computer-name]
    [USER username] [PASSWORD password] [NETNAME netname]
    [LIKE NODE node-number | WITHOUT TABLESPACES]] | STANDALONE |
    RESTART [HOSTNAME hostname] [PORT logical-port] [NETNAME netname] ] ]

  8. #8
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Read Only.

    Hi.

    This is what I suggest should be an ideal approach which would help you do maintainence tasks as well as achieve what you want.

    Create a group lets say 'readusers'. Now add all the users whom you wish to have only read only access to the database can be added to this group. Just give them connect authority and select privileges.

    This way the user which you have already can be used as an adminstrator because I am hopeful that going ahead you will have to do maintainence tasks on the database and you can use this user as the admin.

    Let me know what you feel about this approach.
    HTH

    Nitin

    Ask the experienced rather than the learned

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by urquel
    Did you try START DATABASE(database name) ACCESS(RO) ?
    That is a DB2 for OS/390 command.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Aug 2004
    Posts
    330
    That's what I figured. And that's where my DB2 background is. I was hoping someone would step in and provide a similar command for the non z/os world.

  11. #11
    Join Date
    Mar 2004
    Posts
    448
    Priviledges are granted explicitly and implicitly.
    When you revoked the explicit priviledges, the user/group
    keeps the implicit priviledges.Also if someone grant you a priviledge
    with grant option and you grant it to other , then that person keeps the
    priviledge.You have to explicitly revoked it.
    So check if any the group , especially public if there are any priviledge
    that should be revoked.
    To my knowledge , there is no specific command to make it read only.
    Correct me if I am wrong

    regards

    Mujeeb

  12. #12
    Join Date
    Oct 2004
    Posts
    29
    I have revoked the IMPLICIT_SCHEMA, DBAADM and CREATETAB authorities too for that user but still no luck.

  13. #13
    Join Date
    Aug 2004
    Posts
    330
    It's not a good idea to have a single user that also has administrative priveleges. Even if you were able to REVOKE the priveliges somehow, they have the ability to GRANT at any time. It is a much better idea to have a separate user for read-only access.

  14. #14
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Read this.

    My earlier post suggested what uruquel says.

    I was talking about 2 distinct users in distinct groups. If there is a confusion.

    Group 1- Admin (For Admin User)
    Group 2- Readonly (For users with Select access).

    -------------------

    Quote Originally Posted by nitingm
    Hi.

    This is what I suggest should be an ideal approach which would help you do maintainence tasks as well as achieve what you want.

    Create a group lets say 'readusers'. Now add all the users whom you wish to have only read only access to the database can be added to this group. Just give them connect authority and select privileges.

    This way the user which you have already can be used as an adminstrator because I am hopeful that going ahead you will have to do maintainence tasks on the database and you can use this user as the admin.

    Let me know what you feel about this approach.
    Last edited by nitingm; 12-07-04 at 17:13.
    HTH

    Nitin

    Ask the experienced rather than the learned

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •