Does a user have usage on a schema?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Does a user have usage on a schema?

Walker, Jed S
Does a user have usage on a schema?

How can I query the database to determine if a user has usage on a particular schema, and how can I query the database to find out all the  schemas a user has usage on?

Thanks in advance,


      Jed S. Walker





Reply | Threaded
Open this post in threaded view
|

Re: [despammed] Does a user have usage on a schema?

Andreas Kretschmer
am  29.06.2005, um  8:57:32 -0600 mailte Walker, Jed S folgendes:
> How can I query the database to determine if a user has usage on a
> particular schema, and how can I query the database to find out all the
> schemas a user has usage on?

start psql with -E and type "\n" to see all schemas and the underlaying
select. Now you can modify this select to select all schemas for a user.


Regards, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: Does a user have usage on a schema?

Bruno Wolff III
In reply to this post by Walker, Jed S
On Wed, Jun 29, 2005 at 08:57:32 -0600,
  "Walker, Jed S" <[hidden email]> wrote:
> How can I query the database to determine if a user has usage on a
> particular schema, and how can I query the database to find out all the
> schemas a user has usage on?

The has_schema_privilege function will allow you to check if a user
has access to a schema. You can use pg_users to get a list of all
users and pg_namespace can be used to get a list of all schemas.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: Does a user have usage on a schema?

Walker, Jed S
In reply to this post by Walker, Jed S
Thanks Bruno. I found the definitions for the has_schema_privilege and
it appears it'll work.

Also, how can I query against pg_namespace to find out what schemas a
user has. I've tried something like

Select * from pg_namespace where 'username' = any(nspacl);

I'd like to get a listing of rows such as

Schema1
Schema2
schema7

But that (and some other similar attempts) aren't working. How can I do
this?

Thanks again,

Jed.

-----Original Message-----
From: Bruno Wolff III [mailto:[hidden email]]
Sent: Wednesday, June 29, 2005 9:37 AM
To: Walker, Jed S
Cc: [hidden email]
Subject: Re: Does a user have usage on a schema?

On Wed, Jun 29, 2005 at 08:57:32 -0600,
  "Walker, Jed S" <[hidden email]> wrote:
> How can I query the database to determine if a user has usage on a
> particular schema, and how can I query the database to find out all
> the schemas a user has usage on?

The has_schema_privilege function will allow you to check if a user has
access to a schema. You can use pg_users to get a list of all users and
pg_namespace can be used to get a list of all schemas.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Does a user have usage on a schema?

Bruno Wolff III
On Wed, Jun 29, 2005 at 09:59:06 -0600,
  "Walker, Jed S" <[hidden email]> wrote:
> Thanks Bruno. I found the definitions for the has_schema_privilege and
> it appears it'll work.
>
> Also, how can I query against pg_namespace to find out what schemas a
> user has. I've tried something like
>
> Select * from pg_namespace where 'username' = any(nspacl);

You would do something like:

SELECT nspname
  FROM pg_namespace
  WHERE
    has_schema_privilege('bruno', nspname, 'usage')
;

For example:
area=> select nspname from pg_namespace where has_schema_privilege(
area(> 'bruno', nspname, 'usage');
      nspname
--------------------
 pg_catalog
 information_schema
 public
(3 rows)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])