Board index » delphi » grant access to all tables in script

Bob Bedford

Delphi Developer
Tue, 13 Jan 2004 16:32:55 GMT

grant access to all tables in script

I'm now on IB6...

I do use a script for creating my database. I won't let the SYSDBA user
anymore, for securities purpose off course.

I would like, in my SQL script that create the DB, create an other user,
give it the same right than SYSDBA (so on every table), and drop SYSDBA. (or
rename SYSDBA and his password, or maybe only it password).

I would like also create an user with select access to all tables.

How can I do it with a simple statement ???

Thanks

Bob

 
Martijn Tonie

Delphi Developer
Wed, 14 Jan 2004 01:19:31 GMT

Re:grant access to all tables in script


You can create a 'grant all on tables' script like this:

select 'grant all on ' || rdb$relation_name || ' to user username;'
from rdb$relations
where rdb$view_source is null // ommit this for views also

Alternatively, InterBase Workbench offers you the concept of 'Grant
Templates'. Save grants to a template file, create a new user (or multiple),
load the template file and apply the same grants to several users without
issueing the statements again.

--
Martijn Tonies
InterBase Workbench - the developer tool for InterBase and Firebird
http://www.interbaseworkbench.com

Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

Quote
"Bob Bedford" <bedfo...@NOSPAMhotmail.com> wrote in message

news:3b612770_1@dnews...
Quote
> I'm now on IB6...

> I do use a script for creating my database. I won't let the SYSDBA user
> anymore, for securities purpose off course.

> I would like, in my SQL script that create the DB, create an other user,
> give it the same right than SYSDBA (so on every table), and drop SYSDBA.
(or
> rename SYSDBA and his password, or maybe only it password).

> I would like also create an user with select access to all tables.

> How can I do it with a simple statement ???

> Thanks

> Bob

Sergio Samayo

Delphi Developer
Wed, 14 Jan 2004 01:17:33 GMT

Re:grant access to all tables in script


You can't rename SYSDBA nor delete it. Also, you can't create users via SQL
(as you can on Oracle).

--
Sergio Samayoa
Lgica Software
http://www.geocities.com/logicasw/

Team

Delphi Developer
Wed, 14 Jan 2004 01:30:11 GMT

Re:grant access to all tables in script


Quote
Bob Bedford wrote:

> I would like also create an user with select access to all tables.

        First, you can't create a user in SQL.  Users are server based, not DB
based, so you need to use the API to create a user.  The easiest way is
to use TIBSecurityService from IBX, provided you're using a SuperServer
server.

Quote
> How can I do it with a simple statement ???

        The closest thing to "simple" that I know of is the post from Bill
Karwin below my .signature.

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

From:

12:00 AM
Subject: Re: Granting rights

Another option is this:

SELECT 'GRANT ALL ON ' || RDB$RELATION_NAME || ' TO VCEBAN;'
FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0;

Then save the output of that query and run it as a SQL script.
(Replace VCEBAN above with the user ID to whom you want to grant these
privileges.)

I'll leave the procedure case as an exercise for you.  :)

Bill Karwin

Quote
Victor Ceban wrote:

> What is the easiest way of granting all rights to a user to all tables,
> procedures and etc. I'm trying to create a new user on the server, but if
I
> add it I'll have to spend hours granting him the same rights to all tables
> and etc.

Bob Bedfor

Delphi Developer
Fri, 16 Jan 2004 22:42:06 GMT

Re:grant access to all tables in script


thanks Craig, but I still can't get the admin tab....

"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> a crit dans le
message news: 3B61A523.B93A3D3D@no_spam.vertexsoftware.com...

Quote

> Bob Bedford wrote:

> > I would like also create an user with select access to all tables.

> First, you can't create a user in SQL.  Users are server based, not DB
> based, so you need to use the API to create a user.  The easiest way is
> to use TIBSecurityService from IBX, provided you're using a SuperServer
> server.

> > How can I do it with a simple statement ???

> The closest thing to "simple" that I know of is the post from Bill
> Karwin below my .signature.

> -Craig

> --
> Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
> Delphi/InterBase weblog:   http://delphi.weblogs.com
> Use Borland servers; posts via others are not seen by TeamB.
> For more info, see http://www.borland.com/newsgroups/genl_faqs.html

> From:

> 12:00 AM
> Subject: Re: Granting rights

> Another option is this:

> SELECT 'GRANT ALL ON ' || RDB$RELATION_NAME || ' TO VCEBAN;'
> FROM RDB$RELATIONS
> WHERE RDB$SYSTEM_FLAG IS NULL OR RDB$SYSTEM_FLAG = 0;

> Then save the output of that query and run it as a SQL script.
> (Replace VCEBAN above with the user ID to whom you want to grant these
> privileges.)

> I'll leave the procedure case as an exercise for you.  :)

> Bill Karwin

> Victor Ceban wrote:

> > What is the easiest way of granting all rights to a user to all tables,
> > procedures and etc. I'm trying to create a new user on the server, but
if
> I
> > add it I'll have to spend hours granting him the same rights to all
tables
> > and etc.

Team

Delphi Developer
Sat, 17 Jan 2004 00:29:43 GMT

Re:grant access to all tables in script


Quote
Bob Bedford wrote:

> thanks Craig, but I still can't get the admin tab....

        This has nothing to do with the admin tab.  The admin tab doesn't
require rights to anything.

        If you're not seeing the admin tab, here's what I'd recommend.

o  Uninstall IB client.
o  Search your system for gds32.dll, and delete it wherever found.
o  Reinstall IB client.

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Bob Bedfor

Delphi Developer
Sat, 17 Jan 2004 14:11:57 GMT

Re:grant access to all tables in script


So, I didn't know, but the version of IBX shipped with Delphi 5 ent doesn't
seems to have those elements:
I reintalled Delphi, I did what you said but nothing came out...And I've
seen on thread that we can download
new IBX component from Borland, so is was I did, I installed and runned
Delphi again....and I have the tabs...

Thanks for help.

Bob

"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> a crit dans le
message news: 3B658B77.665A821E@no_spam.vertexsoftware.com...

Quote

> Bob Bedford wrote:

> > thanks Craig, but I still can't get the admin tab....

> This has nothing to do with the admin tab.  The admin tab doesn't
> require rights to anything.

> If you're not seeing the admin tab, here's what I'd recommend.

> o  Uninstall IB client.
> o  Search your system for gds32.dll, and delete it wherever found.
> o  Reinstall IB client.

> -Craig

> --
> Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
> Delphi/InterBase weblog:   http://delphi.weblogs.com
> Use Borland servers; posts via others are not seen by TeamB.
> For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Other Threads

1. GRANT EXECUTE WTIH GRANT OPTION ON IB6

2. chaining scripts (calling a script from a script)

3. How to - generate CREATE TABLE script based on existing table in Stored Procedure

4. Grant rights to all tables in a Interbase system

5. Grant/Deny access to Internet within a windows lan

6. Table ownership and granting rights.

7. Grant rights to maintain table

8. grant on all tables

9. grant privileges on all tables

10. Grant access to stored procedures?