Results 1 to 7 of 7

Thread: new user

  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Unanswered: new user

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

  2. #2
    Join Date
    Jan 2009
    Posts
    5
    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).

  3. #3
    Join Date
    Aug 2005
    Posts
    140
    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.

  4. #4
    Join Date
    Jan 2009
    Posts
    5

    new user

    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

  5. #5
    Join Date
    Aug 2005
    Posts
    140
    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.

  6. #6
    Join Date
    Jan 2009
    Posts
    5

    error in procedure

    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.

  7. #7
    Join Date
    Aug 2005
    Posts
    140
    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.

Posting Permissions

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