#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    19

    Problem with ROLES - again...


    Hi all,

    this must be a simple one, i just have thought too much and now cannot see the forest from the tree.


    environment:

    - i have a server with some databases
    - there are 3 users created on the server (user, internal, admin)
    - in my database called TEST there are 2 roles (user, admin)


    see this:

    # fbsql -u admin -p password -r admin
    Use CONNECT or CREATE DATABASE to specify a database
    SQL> connect test;
    Database: test, User: admin, Role: ADMIN
    SQL> select current_role from rdb$database;

    ROLE
    ===============================
    NONE

    SQL> select RDB$ROLE_NAME from rdb$roles;

    RDB$ROLE_NAME
    ===============================
    ADMIN
    USER

    the question:
    why does it not recognize my current_role???
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    19
    finally, after a couple of white nights i found out why roles are not working for me!

    it was the client software i use IBExpert doesn't handle users/grants correctly.

    as a last chance i learnt how gsec and isql (fbsql) works and i said let's give it a try manually.

    entered in gsec i even observed that user named "USER" does not even exist in the security database. (the one i used to connect to my databases)

    so the steps are these simple:

    #gsec -user ... -password ...
    PHP Code:
    <GSEC>add <user> -pw <password>
    <
    GSEC>quit 
    Attention here my username was USER, so i'll have another trouble at giving grants at fbsql, you'll see immediately;

    #fbsql -u <user> -p <password>
    PHP Code:
    <SQL>connect test;
    <
    CON>create role adm;
    <
    CON>create role usr;
    <
    CON>grant usr to [COLOR=Red][B]"USER"[/B][/COLOR]; 
    And here you go, the second mistake IBExpert does. It simply generated a "grant usr to user" command....and that's incorrect since "user" is a reserved word; But on the grantor interface of IBExpert you can see no error messages, so i had no idea of the cause of the problem.

    so, now it works purrfecly!

    #fbsql -u user -p password -r usr
    Code:
    Use CONNECT or CREATE DATABASE to specify a database
    SQL> connect test;
    Database:  test, User: user, Role: USR
    SQL> select current_role from rdb$database;
    
    ROLE                                                                            
    ===============================
    USR


    maybe this info will save some other users' wasted time

    Comments on this post

    • pabloj agrees : Thanks for posting this

IMN logo majestic logo threadwatch logo seochat tools logo