Hi

i have a DB with 400 table , only one user ( admin ) , now.


now.... i need a db-user for open in readonly ALL tables

present now and in the future.


How i can to create a readonly user ? ( by code )


if i tomorrow add a new table in the catalog
i need to make any special delphi code for add this table in readonly ?


i should a global setting USER_RO := ReadOnly


tnx very much.

Mauro

Mauro


What you need to do depends on wether you genuinely want the tables to be opened read only or simply want to prevent the user from changing the tables.

If the former it depends on wether you use sql or table components. Table components are easy - just set the ReadOnly property. SQL you'll need to handle in your app.

If the latter then you need to start looking at  database privileges eg

1. create a user

CREATE USER "Mauro"
PASSWORD 'xxx'

This creates a user with no privileges at all so next

2. add select priviledges

GRANT SELECT
ON DATABASE "NLH"
TO "RLN"

That should effectively mean they can look but not touch.

Roy Lambert [Team Elevate]

A my client was buy ODBC version of EDB2 for make a personal Report with
ReportBuilder ( enduser editor )

i don't want this user modify any data in my DB, only ..look.

> 1. create a user
>
> CREATE USER "Mauro"
> PASSWORD 'xxx'
>
> This creates a user with no privileges at all so next

OK


> 2. add select priviledges
>
> GRANT SELECT
> ON DATABASE "NLH"
> TO "RLN"

GRANT SELECT ON DATABASE "OnlyReadUsers" TO "USER1_RO"

i need mark all 300 my tables ? or only this line  code for all table ?

Mauro


I'm not sure - sorry. I haven't worked much with privileges yet. But lets assume the worst and that you have to its not difficult to write a script to loop round all the tables in the database setting the privilege level for that user. It also acts as a nice learning exercise for Tim's script language.

eg


SCRIPT
BEGIN
DECLARE tblCursor CURSOR FOR tbls;
DECLARE tblName VARCHAR;

PREPARE tbls FROM 'SELECT * FROM Information.Tables';
OPEN tblCursor;

FETCH FIRST FROM tblCursor INTO tblName;
WHILE NOT EOF(tblCursor) DO
BEGIN
 EXECUTE IMMEDIATE 'GRANT SELECT ON TABLE "' + tblName+'" TO "Mauro"';
EXCEPTION
END;
FETCH NEXT FROM tblCursor INTO tblName;
END WHILE; /* end of tables */
END;

Roy Lambert

Tnx Thx very much Roy !




a question...

Why use a script ? Why don't convert in SQL command by TedbQuery ?

Mauro


It won't work in a query - for a starter they don't do loops.

Queries are designed to run one sql statement. The conversion essentially changes each semicolon separated line by surrounding it with quotes and adding EXECUTE IMMEDIATE and then goes down executing each statement in turn ie as near as you can get to how DBISAM did it.

Scripts are an additional programming language with many more constructs. Ignoring the UI aspect you could write an entire app in them. I wouldn't though since whilst nippy they are interpreted not compiled.

Roy Lambert [Team Elevate]

Roy,

<< Scripts are an additional programming language with many more constructs.
Ignoring the UI aspect you could write an entire app in them. I wouldn't
though since whilst nippy they are interpreted not compiled. >>

While the SQL/PSM is not compiled to machine language, it is indeed
compiled.  Only the SQL execution is not compiled until you PREPARE it,
while the flow control and variables, etc. are actually compiled ahead of
time.  However, you can choose to PREPARE an SQL statement in a script, and
then re-use it in compiled form.  Not in this case, though, since DDL
doesn't use parameters (obviously).

--
Tim Young
Elevate Software
www.elevatesoft.com

Mauro,

<< i need mark all 300 my tables ? or only this line  code for all table ?
>>

No, it is only for the database (see Roy's nice script for assigning to all
tables).  You can see a table of how the privileges affect what you can do
with each type of object (database, table, procedure, etc.) here:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=0&topic=8

under the Privileges section.

Also, you may want to do this for a Role instead of a User, and then assign
the Role to each User that you want to have read-only access to the database
tables.  That will allow you to easily add and remove users without having
to constantly mess with the actual privileges that are specified for the
Role.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I reserve the word compiled for when it goes through to machine code. I believe what you are doing is what I would call tokenization which is something many languages have done (eg PICK/DATABASIC). Its usually faster than interpreting from source code, but not as fast as machine code.

Sorry if I offend.

Roy Lambert

Roy,

<< I reserve the word compiled for when it goes through to machine code. I
believe what you are doing is what I would call tokenization which is
something many languages have done (eg PICK/DATABASIC). Its usually faster
than interpreting from source code, but not as fast as machine code.

Sorry if I offend. >>

None taken.  I just wanted to clarify that we're not constantly re-executing
code/data that is static, i.e. a constant is only evaluated once.

--
Tim Young
Elevate Software
www.elevatesoft.com