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

You are probably logged in at some kind of administrative level which has full access by default.

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

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.

There is only one user connecting to the database. Is there a way to revoke the DBAADM privileges for this user?

Did you try START DATABASE(database name) ACCESS(RO) ?

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] ] ]

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.

That is a DB2 for OS/390 command.

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.

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

I have revoked the IMPLICIT_SCHEMA, DBAADM and CREATETAB authorities too for that user but still no luck.

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.

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).

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