Hi I am new in informix DB,i have only informix user that have any access permission, Now i want ot create a new user with limited permission. (Those not have DBA access permissions). Please help me for creating new user (let me know create statements).

Hi I am new in informix DB,i have only informix user that have any access permission, Now i want ot create a new user with limited permission. (Those not have DBA access permissions). Please help me for creating new user (let me know create statements).

You have to create user on the operating system level (the way you can use depends on the OS you are using).

Then use GRANT statement with which you can assign privileges and roles to the users or REVOKE statement for canceling access privileges.

See documentation for details of using grant and revoke statements.

Thanks stanislav,

I have created a newuser testone and assign permissin with below command

GRANT resource to 'testone'

But i need to create a user that have only view/table/procedure/funcation select, insert, update and edit permissions only.

I amtrying ot create a role with below command. please correct me.

create role one_test
grant select, insert, update on all table to one_test;

but i receive and error in this query

You dont need resource privilege...connect privilege is sufficient for your purpose(if you want only query and update data)

The error you are receiving means you have wrong syntax in your statement.
You can't use "on all table" keyword...informix has no support for it. Instead you have to grant privileges individually for every table you want...with shell script for example.
Find out solution to this problem on this forum...it was already solved.

Again Thanks,

Now I an trying to create a function,

I have a table
CREATE TABLE teste_one
( one DECIMAL(10,0),
two DECIMAL(10,0)
)

I have single row and row value is one=3 and two=4 in this table.

CREATE PROCEDURE informix.test
( ones numeric(10,0))

returning numeric(10,0);
BEGIN

DEFINE PSQL_ERROR INTEGER;
DEFINE PISAM_ERROR INTEGER;
DEFINE PERROR_TEXT CHAR(72);
DEFINE PPROC_NAME CHAR(18);
DEFINE PERROR_REMARK VARCHAR(20);
DEFINE PERROR_RET INTEGER;
DEFINE LReturnStatus INTEGER;
DEFINE LDebtorID INTEGER;
DEFINE LAccountID SMALLINT;
DEFINE LPartyType CHAR(1);
DEFINE LPartyID SMALLINT;
DEFINE LPhone1 numeric(10,0);

DEFINE Conditions CHAR(120);


-- Exception block.
ON EXCEPTION SET PSQL_ERROR, PISAM_ERROR, PERROR_TEXT
-- Log into error table
LET PERROR_REMARK = " Database operation failed." ||
" SQLCODE = " || PSQL_ERROR ||
" ISAM_ERROR = " || PISAM_ERROR ||
" ERROR_TEXT = " || PERROR_TEXT;
-- Ignore DebtorID, AccountID in the error log table

RAISE EXCEPTION PSQL_ERROR, PISAM_ERROR, PERROR_TEXT;
END EXCEPTION

SET DEBUG FILE TO "c:/updvbsphonelist.dbg";
TRACE ON;

let LPhone1=0;

if ones=3 then
LET Conditions = " and one= 3 ";
else
LET Conditions = "and one= 5 ";
end if

SELECT two INTO LPhone1 from teste_one where 1=1|| conditions;


return LPhone1;

END;
END PROCEDURE;


it created successfully. But when i use this in sql query I found on error

" 10:55:06 [SELECT - 0 row(s), 0.000 secs] [Error Code: -1213, SQL State: IX000] Character to numeric conversion error
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]"

Could you please let me know is it possible in informix. if not then please let me know any alternate of this.

Previously I was working in ORACLE and it was possible in ORACLE.

Informix is not Oracle
And AFAIK the way you wrote your sql is not possible in oracle too.(just simple concatenate two strings without appropriate function- e.g. execute immediate or other method)

What you are trying to do is called dynamic sql...which Informix supports only in version 11.5 (the newest version).
This feature had no support in earlier versions in built-in SQL or SPL. Instead you can use 4GL or ESQL for this purpose. Another way is use Exec datablade(provides equivalent to oracle 'execute immediate'), but this datablade is "out of the box" -open source and has no support or warranty from IBM.