How to limit a user access DB in readonly
Hello everyone!
I am liaomike.
I am using IDS 9.4 UC2.
I try to create a user named "ifxuser" who just can dbaccess a DB in readonly.
The "grant" SQL seem to can't just focus on DB object,but only table object
can .
The SQL like that:
grant connect to "ifxuser";
grant select on "informix".* to "ifxuser" as "informix";
The word "*" must be a exist table name.
Is there a way to focus a whole DB,and informix(DBA) still work fine.
Any help will be a great appreciate.
thank u.
liaomike
No. To do what you want you have to revoke all permissions from user PUBLIC as
those are the default perms for users (or grant PUBLIC only SELECT privs on all
tables) then grant only SELECT PRIVS to user ifxuser on each table
individually.
You can script this, my package utils4_ak is a collection of awk scripts as
examples of automating applying something to all of the tables in a database
using dbschema or myschema and awk. Another approach is to use SQL, literal
strings and concatenation to build the privs script. Like:
output to privs.sql
SELECT 'GRANT SELECT ON '||tabname||' TO ifxuser'
FROM systables where tabid >= 100;
Then if you just edit the privs.sql to do a bit of cleanup and run it.
Art S. Kagel
----- Original Message -----
From: Liao Mike <ids@iiug.org>
At: 2/04 22:29:47
Hello everyone!
I am liaomike.
I am using IDS 9.4 UC2.
I try to create a user named "ifxuser" who just can dbaccess a DB in readonly.
The "grant" SQL seem to can't just focus on DB object,but only table object
can .
The SQL like that:
grant connect to "ifxuser";
grant select on "informix".* to "ifxuser" as "informix";
The word "*" must be a exist table name.
Is there a way to focus a whole DB,and informix(DBA) still work fine.
Any help will be a great appreciate.
thank u.
liaomike
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
You can also do something like this as long as you know that the
column width will not more then a total of 80 which means you have
about 66 useable charachers because of the folowing (I would guess);
(expression) Grant SELECT ON "product".z_exclude_stats TO ifxuser as "product"
;
OUTPUT TO PIPE 'dbaccess DATABASE_NAME - ' WITHOUT HEADINGS
SELECT 'GRANT SELECT ON ' ||TRIM(tabname)
||' TO ifxuser' ||' as "' ||TRIM(owner) ||'";'
FROM systables where tabid >= 100;
But in the end the best is to do as Art suggested and edit the file
and then run it. Also note that the above will return a NULL line if
any fields are NULL. Since I haven't seen a NULL owner or tablename
field in systables ever I don't expect this to be a problem.
Eric B. Rowell
Please always test and know what something should do before using it.
On 2/5/07, ART KAGEL, BLOOMBERG/ 731 LEXIN <kagel@bloomberg.net> wrote:
> No. To do what you want you have to revoke all permissions from user PUBLIC
as
> those are the default perms for users (or grant PUBLIC only SELECT privs on
> all
> tables) then grant only SELECT PRIVS to user ifxuser on each table
> individually.
> You can script this, my package utils4_ak is a collection of awk scripts as
> examples of automating applying something to all of the tables in a database
> using dbschema or myschema and awk. Another approach is to use SQL, literal
> strings and concatenation to build the privs script. Like:
>
> output to privs.sql
> SELECT 'GRANT SELECT ON '||tabname||' TO ifxuser'
> FROM systables where tabid >= 100;
>
> Then if you just edit the privs.sql to do a bit of cleanup and run it.
>
> Art S. Kagel
>
> ----- Original Message -----
> From: Liao Mike <ids@iiug.org>
> At: 2/04 22:29:47
>
> Hello everyone!
> I am liaomike.
>
> I am using IDS 9.4 UC2.
> I try to create a user named "ifxuser" who just can dbaccess a DB in
readonly.
> The "grant" SQL seem to can't just focus on DB object,but only table object
> can .
>
> The SQL like that:
> grant connect to "ifxuser";>
> grant select on "informix".* to "ifxuser" as "informix";>
> The word "*" must be a exist table name.
>
> Is there a way to focus a whole DB,and informix(DBA) still work fine.
>
> Any help will be a great appreciate.
>
> thank u.
>
> liaomike
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Thank everyone's help.
I used Art S. Kagel's approach.
It is a clear method.
the sql is below like that:
--clear PUBLIC privilege
unload to "clrpub.sql"delimiter ";"
SELECT 'REVOKE ALL ON '||tabname||' FROM PUBLIC'
FROM systables
where tabid >= 100;
190 row(s) unloaded.
[informix@hp511 privs]$ dbaccess mydb clrpub.sql
--set select privilege to PUBLIC
unload to "setpub.sql"delimiter ";"
SELECT 'GRANT SELECT ON '||tabname||' TO PUBLIC'
FROM systables
where tabid >= 100;
190 row(s) unloaded.
[informix@hp511 privs]$ dbaccess mydb setpub.sql
all ok!
--grant connect privilege to "ifxuser"
grant connect to "ifxuser";Permission granted.
unload to "setifxusr.sql"delimiter ";"
SELECT 'GRANT SELECT ON '||tabname||' TO ifxuser'
FROM systables
where tabid >= 100;
190 row(s) unloaded.
[informix@hp511 privs]$ dbaccess mydb setifxusr.sql
all ok!
--create a role "readonlyusr" and assign ifxuser's privilege to it
create role readonlyusr;
Role created.
grant readonlyusr to ifxuser;Permission granted.
set role readonlyusr;
Role set.
--now you can create self readonly account without do the same thing like
before
grant connect to "liaomike";Permission granted.
grant readonlyusr to liaomike;Permission granted.
===============
verified procedure as below:(login as ifxuser or liaomike)
select *
from test_actual_fee_adjok!
insert into test_actual_fee_adj
select *
from his_actual_fee_adj
275: No INSERT permission.
delete
from test_actual_fee_adj
274: No DELETE permission.
update test_actual_fee_adj
set n_feemat=0
273: No UPDATE permission.
drop table his_actual_fee_adj
313: Not owner of table.
drop view view_rlt_org_weight
313: Not owner of table.
-----------------------------
finally thanks again.
liaomike