Grant issues

Grant issues

Post by Steve Crawfo » Sat, 14 Dec 2002 08:51:58



I have an automated application that creates and updates tables in a database
and I have a "readonly" group containing all the users who should be allowed
to query the data. I am now a bit stuck. Can I:

1. Grant to users in the readonly group permission to do unrestricted queries
on the database (I know I can give this permission on a table but the
application creates tables as needed and I just want to give blanket
read-only rights to the users who need to query the data)?

2. Grant permission to create/alter views and temporary tables but not to
create nor alter functions, triggers, rules, indexes, etc. in the database.

I'm setting up this database on 7.3. Will schemas be of any use here (ie. can
I set up read-only rights on the schema that the application uses but set up
users with their own schemas where they can do whatever they want)?

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

Grant issues

Post by Bruno Wolff I » Tue, 17 Dec 2002 13:09:20


On Thu, Dec 12, 2002 at 15:51:52 -0800,

Quote:> I have an automated application that creates and updates tables in a database
> and I have a "readonly" group containing all the users who should be allowed
> to query the data. I am now a bit stuck. Can I:

> 1. Grant to users in the readonly group permission to do unrestricted queries
> on the database (I know I can give this permission on a table but the
> application creates tables as needed and I just want to give blanket
> read-only rights to the users who need to query the data)?

When tables are created only the owner has access. You need to do something
to give people in the group select access. Options include a script that runs
periodicly that examines table permissions and changes them, modifying the
application to add grant commands when creating tables, and modifying
postgresql to create tables with different access rights.

Quote:> 2. Grant permission to create/alter views and temporary tables but not to
> create nor alter functions, triggers, rules, indexes, etc. in the database.

In 7.3 you can control being able to create temporary tables and objects in
schemas separately. However if you can create views, then you can create
other objects as well. You can restrict creating functions by removing
the usage rights for all languages.

Quote:> I'm setting up this database on 7.3. Will schemas be of any use here (ie. can
> I set up read-only rights on the schema that the application uses but set up
> users with their own schemas where they can do whatever they want)?

Before 7.3 there was no way to restrict who could create tables in a database
(for people allowed to access the database). In 7.3 restricting creation of
objects is done by restricting who can create schemas and who is allowed to
create objects in existing schemas.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 

1. Grant issues.

Hi friends,
Can some body help??

Case :-

  Platform :- Oracle Enterprise 8.0.4.2.1 on Aix 4.3.

  Role dvl for developers,
  granted all possible object level privileges
(select,insert,update,delete,alter)
  to dvl role for tables of other schemas.
  granted create table, create procedure rights to allow develper to create
thier
  own tables and/or procedures etc.

  connect as developer (This user has default role of dvl)
  create a procedure p1 trying to acces other_schema.table_name;
  While compiling p1, error message appears about lack of privileges.

  Now the problem is how do you give object level grants of other schemas to
  dvl role? so that p1 procedure can be compiled w/o. errors.

  Although the following works
       connect as other_schema
       grant select on table_name to developer with grant option
       grant insert on table_name to developer with grant option
       grant update  on table_name to developer with grant option
       grant delete on table_name to developer with grant option
  We do not want to give any grants directly to developer user (As far as
possible).
  The grants should be  given to dvl role who in turns has been granted dvl
role.

  I am not able to understand, why database procedure needs this special
treatment?
  What other alternates can be deviced to manage such granting issues?

 Any solutions/ suggestions/ advices would be highly appreciated. Also if I
can be pointed to some white paper which explains proper management of grant
related issues?

 Thanks in advance.

Kind regards,
Mehul Zaveri
Oracle DBA

Emirates Petroleum Products Company.
Dir:+9714-3031557,Brd:+9714-372131
Dubai.

2. SQL Hierarchies - A twist on Joe Celko's example

3. Grant Issues in 7.0

4. Passing value of strin variables to a report title

5. Issues with importing roles and grants

6. Cached Updates - How to find row in error

7. How Do I - Issue Grants and Revokes from inside app

8. LDAP-component

9. grant update with grant option

10. Grant access to procedure and DENY/GRANT to view

11. create proc grant execute with grant

12. direct grant vs. grant via role

13. Difference between grant to user and grant to role