| Subject | RE: [firebird-support] Trusted authentication for Standard users |
|---|---|
| Author | Hariharan, Sabu |
| Post date | 2016-02-28T20:38:18Z |
Hi Set,
Thank you for your answer.
For administrators with trusted authentication, firebird will automatically give the SYSDBA role.
Means during connecting to the DB (I am using Delphi) in the connection strings I don’t have to mention any role name.
But with standard users, we have to mention it (suggested by some users).
The problem is even if I mention that, firebird is not giving the user the SYSDBA role.
As you have mentioned, for standard users we have to create a user and grant the rights then only we can access the tables.
Thank you.
Kind regards,
Sabu Hariharan
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Sunday, 28 February 2016 12:37 p.m.
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Trusted authentication for Standard users
parameters.
I'm answering since no-one seems to have answered you.
I think this simply is the way roles work with Firebird. Think of it as
"What kind of access do I want for this connection?", rather than "What
are the sum of all access rights my user have been given through roles?".
Sometimes, I know that I simply want to read data and then it is safer
to connect using a role that have reading access only (then it doesn't
harm if I accidentally do a DELETE FROM <table>). Other times, I want to
update things, then I have to connect using a role that can update (but
need to be a bit more careful regarding which queries I throw at the
database).
If you want access rights that should always be available for a
particular person regardless of roles used with the connection, grant
the access rights directly to the user rather than go through a role.
You could even write a query that would get you the commands to execute
to give the user the same rights as a role (this is not tested by any
means and most likely overly simplified and only suitable for simple cases):
SELECT 'GRANT '||
trim(CASE RDB$PRIVILEGE
WHEN 'S' THEN 'SELECT'
WHEN 'I' THEN 'INSERT'
WHEN 'U' THEN 'UPDATE'
WHEN 'D' THEN 'DELETE'
WHEN 'R' THEN 'REFERENCES'
END) ||
IIF(RDB$FIELD_NAME > '', '('||TRIM(RDB$FIELD_NAME)||')', '')||
' ON ' ||trim(RDB$RELATION_NAME)||' TO MYUSER;'
FROM RDB$USER_PRIVILEGES
WHERE RDB$USER = 'MYROLE';
HTH,
Set
Please be advised that this email may contain confidential information. If you are not the intended recipient, please notify us by email by replying to the sender and delete this message. The sender disclaims that the content of this email constitutes an offer to enter into, or the acceptance of, any agreement; provided that the foregoing does not invalidate the binding effect of any digital or other electronic reproduction of a manual signature that is included in any attachment.
[Non-text portions of this message have been removed]
Thank you for your answer.
For administrators with trusted authentication, firebird will automatically give the SYSDBA role.
Means during connecting to the DB (I am using Delphi) in the connection strings I don’t have to mention any role name.
But with standard users, we have to mention it (suggested by some users).
The problem is even if I mention that, firebird is not giving the user the SYSDBA role.
As you have mentioned, for standard users we have to create a user and grant the rights then only we can access the tables.
Thank you.
Kind regards,
Sabu Hariharan
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Sunday, 28 February 2016 12:37 p.m.
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Trusted authentication for Standard users
>Hi,authentication, we need to mention the role name with the connection
>
> I noticed that for standard users with firebird trusted
parameters.
>Hi Sabu!
> Can somebody give more details about it.
>
> Thank you.
>
> Regards,
>
> Sabu Hariharan
I'm answering since no-one seems to have answered you.
I think this simply is the way roles work with Firebird. Think of it as
"What kind of access do I want for this connection?", rather than "What
are the sum of all access rights my user have been given through roles?".
Sometimes, I know that I simply want to read data and then it is safer
to connect using a role that have reading access only (then it doesn't
harm if I accidentally do a DELETE FROM <table>). Other times, I want to
update things, then I have to connect using a role that can update (but
need to be a bit more careful regarding which queries I throw at the
database).
If you want access rights that should always be available for a
particular person regardless of roles used with the connection, grant
the access rights directly to the user rather than go through a role.
You could even write a query that would get you the commands to execute
to give the user the same rights as a role (this is not tested by any
means and most likely overly simplified and only suitable for simple cases):
SELECT 'GRANT '||
trim(CASE RDB$PRIVILEGE
WHEN 'S' THEN 'SELECT'
WHEN 'I' THEN 'INSERT'
WHEN 'U' THEN 'UPDATE'
WHEN 'D' THEN 'DELETE'
WHEN 'R' THEN 'REFERENCES'
END) ||
IIF(RDB$FIELD_NAME > '', '('||TRIM(RDB$FIELD_NAME)||')', '')||
' ON ' ||trim(RDB$RELATION_NAME)||' TO MYUSER;'
FROM RDB$USER_PRIVILEGES
WHERE RDB$USER = 'MYROLE';
HTH,
Set
Please be advised that this email may contain confidential information. If you are not the intended recipient, please notify us by email by replying to the sender and delete this message. The sender disclaims that the content of this email constitutes an offer to enter into, or the acceptance of, any agreement; provided that the foregoing does not invalidate the binding effect of any digital or other electronic reproduction of a manual signature that is included in any attachment.
[Non-text portions of this message have been removed]