Playing with the built in security can someone explain the relationship between Roles & Privileges, which overrules which, do they apply equally to tables and queries etc?

eg In EDBManager set role to Administrator but only set the database privilege to select and was able to edit data in a table.

Roy Lambert

Roy,

<< Playing with the built in security can someone explain the relationship
between Roles & Privileges, which overrules which, do they apply equally to
tables and queries etc?>>

Roles are just groups of users. The way to indicate that user U1 belongs to
group R1 is to grant the role R1 to user U1. Roles can only be granted to users,
not to database objects.
Privileges can be granted to users or roles. Once a privilege is granted to a
role it is implicitly granted to all users with that role.

<<eg In EDBManager set role to Administrator but only set the database privilege
to select and was able to edit data in a table.>>

The Administrator user is automatically granted the "Administrators" role, so it
has all privileges on all databases no matter what other roles you grant to him,
unless you explicitly revoke them. Also, since you are logged as
"Administrator", for the newly granted or revoked privileges to take effect you
must logout and login again.

--
Fernando Dias
[Team Elevate]

Fernando


Fine but which, if either, takes precedence, and more importantly will it always be that way? From my tests I'm somewhat confused but it looks as though Roles and Privileges are OR'd but I'm not sure.

I'd also be interested in finding out when and at what point these are checked and what performance impact there is for different strategies eg if alter permission for a table is checked before every insert/edit/delete and you're looping through several thousand records altering a column what happens?

Roy Lambert

Roy,

<< Fine but which, if either, takes precedence, and more importantly will it
always be that way? From my tests I'm somewhat confused but it looks as
though Roles and Privileges are OR'd but I'm not sure. >>

Yes, they are OR'd.  For a normal user, you should be able see the
"effective" privileges in the EDB Manager by just clicking on the Privileges
task option for any given object (database, table, etc).  Unfortunately, you
cannot do so for Administrators at this time, due to the fact that they need
to see the actual privileges set, not the effective privileges.

<< I'd also be interested in finding out when and at what point these are
checked and what performance impact there is for different strategies eg if
alter permission for a table is checked before every insert/edit/delete and
you're looping through several thousand records altering a column what
happens? >>

Permissions are checked before any operation that requires such a check,
i.e. if the operation being executed requires a certain privilege, then it
will involve a privilege check.  The checks are very quick, though, so you
shouldn't see a difference in performance.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Yes, they are OR'd. For a normal user, you should be able see the
>"effective" privileges in the EDB Manager by just clicking on the Privileges
>task option for any given object (database, table, etc). Unfortunately, you
>cannot do so for Administrators at this time, due to the fact that they need
>to see the actual privileges set, not the effective privileges.

Thanks, that's what I thought was happening, but testing it was doing my head in.

>Permissions are checked before any operation that requires such a check,

Do you share the same scriptwriter as Sir Humphrey Appleby <vbg>

>The checks are very quick, though, so you
>shouldn't see a difference in performance.

I think I understand what you're saying but <tongue in cheek>different to what?</tongue in cheek>

Roy Lambert

Roy,

<< Do you share the same scriptwriter as Sir Humphrey Appleby <vbg> >>

I don't know who that is, but I suspect that it involves satire.

<< I think I understand what you're saying but <tongue in cheek>different to
what?</tongue in cheek> >>

Different than if you weren't using any privileges at all, i.e. running as
an administrator.  I assumed that you were starting to introduce roles and
privileges to your application, and I was pointing out that there isn't
really any overhead in doing so.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< Do you share the same scriptwriter as Sir Humphrey Appleby <vbg> >>
>
>I don't know who that is, but I suspect that it involves satire.

British sitcom Yes Minister and Yes Prime Minister. Sir Humphrey was his Departmental Secretary (ie the guy who ran the department not someone who typed letters). Definitely worth buying the DVDs

Roy Lambert

Roy,

<< British sitcom Yes Minister and Yes Prime Minister. Sir Humphrey was his
Departmental Secretary (ie the guy who ran the department not someone who
typed letters). Definitely worth buying the DVDs >>

I'll have to check it out.  I'm pretty fond of the British humour (spelled
it right .

--
Tim Young
Elevate Software
www.elevatesoft.com

Seconded!
I am sure the humour will safely cross the pond.

--

Tim

>(spelled
>it right .

Congratulations - now get to work on colour.

Roy Lambert