Running EDB v2.03 B12 on D2007 with all updates.

I have read the "Privileges" portion of the SQL manual to the point
that I can recite it by heart (well...almost) but I am having trouble
with setting TABLE privileges. I am attempting to understand all I can
about setting privileges so I can create my own security module in my
applicaiton.

I have no problem with creating roles, assigning roles and even setting
privileges on databases...but I am having a problem with setting
privileges on a *TABLE* level.

If I run the following statement: SQL.Text:='GRANT Select ON DATABASE '
+ 'CDCollector' + ' TO Managers' it runs with no problem and the
security is set as designed.

Yet, if I run the following statement: SQL.Text:='GRANT Select ON TABLE
' + 'Albums' + ' TO Managers' I receive the following error...

"ElevateDB error #403. The database Configuration is read-only and this
operation cannot be performed (GRANT Select ON Albums TO Managers)"

I also have a problem if I try to set more than one permission at a
time, even for a database. I get errors if I try something like,
SQL.Text:='GRANT Select, Insert, Update ON DATABASE ' + 'CDCollector' +
' TO Managers';

It appears in the SQL manual that permissions can be set on other
database objects other than the database itself. It also shows ehere
more than one permission can be assigned in a statement. What am I
doing wrong in these cases? My ultimate goal is to be able to custom
design a security system for my application(s) where a given role might
have different privileges in different tables. I don't want the whole
database to be "read-only" or "all privileges", etc. I want to
diversify and set what is needed based on the role and situation.

Any and all help will be greatly appreciated.

--
Jan Ferguson

Jan


I don't use the ElevateDB privileges system (I rolled my own - different granularity and focus) so I'm guessing here.

One of the things I've had a bit of difficulty getting my head round is where you need to be in the system to do various things. Some need to be done at the session level and some at the database level. Is it possible that you're at the wrong level to carry out the operation you want?

The other thing I do a lot is use EDBManager to carry out the operation to start with then copy the statement from Explorer | SQL History to make sure its right, checking whereabouts I am from the info in the right hand part of the status bar.

Hope "I'm not teaching granny to suck eggs"

Roy Lambert

Roy,

> I don't use the ElevateDB privileges system (I rolled my own -
> different granularity and focus) so I'm guessing here

Yeah...I have used the TMS Security set of components for years now and
they really are awesome. The only trouble with them is that usually you
would set the session username and password properties to a "generic"
full acess user and then in either the EDBManager or the DBISAM Server
Administrator you would see multiple instances of that "generic" user
name. Not great for knowing who is accessing the system. With the
security functionality in EDB, I really want to get to know it inside
and out to use it effectively.

> One of the things I've had a bit of difficulty getting my head round
> is where you need to be in the system to do various things.

I definitely agree with your assessent of knowing where you are in the
system. I've done fairly well in getting my head around that. This is
why this, in particular, is frustrating me. It should by all rights (no
pun intended) work correctly, at least in my mind. I have checked these
NGs as well and they helped tremendously except no one showed any code
similar to what I am experiencing. ALl had to do with permissions at
the database level.

> The other thing I do a lot is use EDBManager to carry out the
> operation to start with then copy the statement from Explorer | SQL
> History to make sure its right, checking whereabouts I am from the
> info in the right hand part of the status bar

Yup...I have used that funtionality extensively in the past. It's great
stuff. The same with reverse engineering.

> Hope "I'm not teaching granny to suck eggs"

Not at all. I appreciate your comments. Hopefully Tim or someone else
can show me the error of my ways. Otherwise I'll shoot an email to
support tomorrow. Thanks again...

--
Jan

Jan,

<< Yet, if I run the following statement: SQL.Text:='GRANT Select ON TABLE '
+ 'Albums' + ' TO Managers' I receive the following error...

"ElevateDB error #403. The database Configuration is read-only and this
operation cannot be performed (GRANT Select ON Albums TO Managers)" >>

You're executing the GRANT from the context of the Configuration database,
in which there doesn't exist an Albums table.  You have to execute the GRANT
from the context of the database in which the Albums table resides,
otherwise ElevateDB has no idea which database you want to use for the
GRANT.

The easiest way to make sure that this works correctly, is to simply use the
TEDBDatabase.Execute method for database-level DDL:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=d&version=7&topic=Creating_Database_Objects

and use the TEDBSession.Execute method for session-level DDL:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=d&version=7&topic=Creating_Configuration_Objects

<< I also have a problem if I try to set more than one permission at a time,
even for a database. I get errors if I try something like,
SQL.Text:='GRANT Select, Insert, Update ON DATABASE ' + 'CDCollector' + '
TO Managers'; >>

INSERT and UPDATE are not valid permissions for databases.  The valid
permissions for each object are detailed here:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=User_Security

under "Privileges".

I hope that helps clear things up.  If you have any other questions, feel
free to ask.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Many thanks for the help. It now works as it should and I understand
where I made the errors. I was spot on with regard to my Configuration
changes but had the problem with the privileges. It makes perfect sense
to me now that you can't work with object "B" when you are referencing
object "A". Plain as the nose on my face today but wasn't even a blip
on my radar yesterday.

> INSERT and UPDATE are not valid permissions for databases.

Duh! I don't know how I missed that. Probably too many hours at the
keyboard so I couldn't see the forest through the trees.

Thanks once again Tim. EDB is becoming more powerful to me everyday I
use it (and learn it's hidden talents!)
--
Jan


Tim Young [Elevate Software] wrote:

> Jan,
> "ElevateDB error #403. The database Configuration is read-only and
> this operation cannot be performed (GRANT Select ON Albums TO
> Managers)" >>
>
> You're executing the GRANT from the context of the Configuration
> database, in which there doesn't exist an Albums table.  You have to
> execute the GRANT from the context of the database in which the
> Albums table resides, otherwise ElevateDB has no idea which database
> you want to use for the GRANT.

> << I also have a problem if I try to set more than one permission at
> a time, even for a database. I get errors if I try something like,
> SQL.Text:='GRANT Select, Insert, Update ON DATABASE ' + 'CDCollector'
> + ' TO Managers'; >>
>
> INSERT and UPDATE are not valid permissions for databases.

Jan,

<< Thanks once again Tim. EDB is becoming more powerful to me everyday I use
it (and learn it's hidden talents!) >>

Well, if you do find any hidden talents, don't mention them like Roy does
( ) - they're most likely being hidden until the next minor release.

--
Tim Young
Elevate Software
www.elevatesoft.com

LOL!!
--

Tim Young [Elevate Software] wrote:

> Jan,
>
> << Thanks once again Tim. EDB is becoming more powerful to me
> everyday I use it (and learn it's hidden talents!) >>
>
> Well, if you do find any hidden talents, don't mention them like Roy
> does ( ) - they're most likely being hidden until the next minor
> release.