Hi,
Just a question regarding ZEOSLIB. Currently I am using version 6.6.1 and Delphi 7 with Firebird 2.0.1. Having set up the database and defining the specific roles in the database the following question came to mind.
Does ZEOS cater for connection to a Firebird database with roles? If not, is there a way to programmatically set up a connection string and pass this string to the TZConnection component? Is Roles catered for in the TZConnection component?
Any help will be greatly appreciated,
Antz
PS: Thanks for a great suite of components!
Firebird Users and Roles
Moderators: gto, cipto_kh, EgonHugeist
Firebird Users and Roles
[font=Arial]Me again,
I have tried the following using the TZConnection component of ZEOSLIB 6.6.1 and this is regarding my previous post on users and roles. I will elaborate a little further to make things clearer:
Database setup (example):[/font]
[font=Courier New]
CREATE ROLE SALESADMIN;
CREATE ROLE PURCHASEADMIN;
GRANT ROLE SALESADMIN TO user1;
CREATE TABLE banks(
bank_code CHAR(5) NOT NULL,
bank_name VARCHAR(30) NOT NULL,
PRIMARY KEY(bank_code)
);
CREATE TABLE user_roles(
user_name CHAR(6) NOT NULL,
user_last_login TIMESTAMP DEFAULT ‘NOW’ NOT NULL,
user_role VARCHAR(20) NOT NULL,
PRIMARY KEY(user_name,user_role)
);
SET TERM ^ ;
CREATE PROCEDURE AddBank(
bankcode CHAR(5),
bankname VARCHAR30))
AS
BEGIN
BEGIN
INSERT INTO banks(bank_name,bank_code)
VALUES(:bankcode,:bankname);
END
SUSPEND;
END ^
SET TERM ; ^
GRANT EXECUTE PROCEDURE AddBank TO SALESADMIN;[/font]
As you can see, I have create two roles, granted one role to one user and granted execute rights to the role SALESADMIN. This means that user1 is assigned the role of SALESADIM and should therefore be able to execute the procedure AddBank.
In the Delphi application a connection is to be established twice, the first connection to see if user1 has login rights into the database, if so then table user_roles is read and the user_role is returned. After this I try to establish the connection again, this time using the user_role as well. The Delphi TZConnection looks like this:
TZConnection
Database: the same name used in the first connection
Name: cnxBanks
Password: the same password used in the first connection
Protocol: firebird-2.0
TransactionIsolationLevel: tiRepeatableRead
User: the same user used in the first connection
Properties.Add(‘ROLENAME = ‘ + user_role)
Login is successful and when querying the connection property by index value the correct ROLE is returned from the connection. The problem it seems is that the Property ROLENAME for some reason seems meaningless for the connection. Each time I attempt to execute the stored procesure AddBank the SQL error is -551 (No rights to execute the procedure).
If anybody has any other answer of suggestion please let me know. Is there any other way of connecting to a FireBird database with the role of a user using ZEOSLIB, am I missing something in the connection setup?
Many thanks,
I have tried the following using the TZConnection component of ZEOSLIB 6.6.1 and this is regarding my previous post on users and roles. I will elaborate a little further to make things clearer:
Database setup (example):[/font]
[font=Courier New]
CREATE ROLE SALESADMIN;
CREATE ROLE PURCHASEADMIN;
GRANT ROLE SALESADMIN TO user1;
CREATE TABLE banks(
bank_code CHAR(5) NOT NULL,
bank_name VARCHAR(30) NOT NULL,
PRIMARY KEY(bank_code)
);
CREATE TABLE user_roles(
user_name CHAR(6) NOT NULL,
user_last_login TIMESTAMP DEFAULT ‘NOW’ NOT NULL,
user_role VARCHAR(20) NOT NULL,
PRIMARY KEY(user_name,user_role)
);
SET TERM ^ ;
CREATE PROCEDURE AddBank(
bankcode CHAR(5),
bankname VARCHAR30))
AS
BEGIN
BEGIN
INSERT INTO banks(bank_name,bank_code)
VALUES(:bankcode,:bankname);
END
SUSPEND;
END ^
SET TERM ; ^
GRANT EXECUTE PROCEDURE AddBank TO SALESADMIN;[/font]
As you can see, I have create two roles, granted one role to one user and granted execute rights to the role SALESADMIN. This means that user1 is assigned the role of SALESADIM and should therefore be able to execute the procedure AddBank.
In the Delphi application a connection is to be established twice, the first connection to see if user1 has login rights into the database, if so then table user_roles is read and the user_role is returned. After this I try to establish the connection again, this time using the user_role as well. The Delphi TZConnection looks like this:
TZConnection
Database: the same name used in the first connection
Name: cnxBanks
Password: the same password used in the first connection
Protocol: firebird-2.0
TransactionIsolationLevel: tiRepeatableRead
User: the same user used in the first connection
Properties.Add(‘ROLENAME = ‘ + user_role)
Login is successful and when querying the connection property by index value the correct ROLE is returned from the connection. The problem it seems is that the Property ROLENAME for some reason seems meaningless for the connection. Each time I attempt to execute the stored procesure AddBank the SQL error is -551 (No rights to execute the procedure).
If anybody has any other answer of suggestion please let me know. Is there any other way of connecting to a FireBird database with the role of a user using ZEOSLIB, am I missing something in the connection setup?
Many thanks,
I just verified the situation you describe and I have no problem with it, it works correctly as it should. The only problem I see in your code is that you are putting blank spaces in the ROLENAME property, try it without the spaces.
Your code says:
Properties.Add(‘ROLENAME = ‘ + user_role)
It should say:
Properties.Add(‘ROLENAME=‘ + user_role)
Regards,
Jay
Your code says:
Properties.Add(‘ROLENAME = ‘ + user_role)
It should say:
Properties.Add(‘ROLENAME=‘ + user_role)
Regards,
Jay