How to Grant All Privileges on Views to Arbitrary User

How to grant all privileges on views to arbitrary user

The reason is that you need additional privileges to access a view or table. Privileges on the database do not cover access to all objects in it.

It is different with functions: EXECUTE privilege is granted to public by default. But the function is executed with the privileges of the current user. You may be interested in the SECURITY DEFINER modifier for CREATE FUNCTION. But normally it is enough to grant SELECT on involved tables.

Per documentation about default privileges:

Depending on the type of object, the initial default privileges might
include granting some privileges to PUBLIC. The default is no public
access for tables, columns, schemas, and tablespaces; CONNECT
privilege and TEMP table creation privilege for databases; EXECUTE
privilege for functions; and USAGE privilege for languages.

You may be interested in this DDL command (requires Postgres 9.0 or later):

GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;

While connected to the database in question, of course (see @marcel's comment below), and as a user with sufficient privileges. You may also be interested in the setting DEFAULT PRIVILEGES:

• Grant all on a specific schema in the db to a group role in PostgreSQL

More detailed answer how to manage privileges:

• How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

pgAdmin has a feature for more sophisticated bulk operations:

Or you can query the system catalogs to create DDL statements for bulk granting / revoking ...

grant all privileges on db.* to user?

how can I grant bob privileges at the schema level

You your looking for the option on all tables in schema

grant all privileges on all tables in schema public to bob;
grant all privileges on all tables in schema reporting to bob;

You probably also want to change the default privileges so that this is also applied for tables created in the future.

Grant access to views in postgresql

I agree it should work. With permissions GRANT ... ON ALL TABLES should include views too.

Did you create the view after granting the privileges to testuser? If so then it doesn't have the same privileges as the other tables. That's because GRANT ... ON ALL TABLES means "on all tables that currently exist". To include tables/views you create in the future, you can say:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO testuser;

Or if you want to give more than SELECT, you can say ALL PRIVILEGES instead.

I think this behavior of ON ALL TABLES is one of the most misunderstood bits about Postgres permissions, and it isn't really called out in the standard documentation, so I tried to emphasize it in my own Postgres permissions overview.

PostgreSQL: Give all permissions to a user on a PostgreSQL database

All commands must be executed while connected to the right database cluster. Make sure of it.

Roles are objects of the database cluster. All databases of the same cluster share the set of defined roles. Privileges are granted / revoked per database / schema / table etc.

A role needs access to the database, obviously. That's granted to PUBLIC by default. Else:

GRANT CONNECT ON DATABASE my_db TO my_user;

Basic privileges for Postgres 14 or later

Postgres 14 adds the predefined, non-login roles pg_read_all_data / pg_write_all_data.

They have SELECT / INSERT, UPDATE, DELETE privileges for all tables, views, and sequences. Plus USAGE on schemas. We can GRANT membership in these roles:

GRANT pg_read_all_data TO my_user;
GRANT pg_write_all_data TO my_user;

This covers all basic DML commands (but not DDL, and not some special commands like TRUNCATE or the EXECUTE privilege for functions!). The manual:

pg_read_all_data

Read all data (tables, views, sequences), as if having SELECT rights
on those objects, and USAGE rights on all schemas, even without
having it explicitly. This role does not have the role attribute
BYPASSRLS set. If RLS is being used, an administrator may wish to
set BYPASSRLS on roles which this role is GRANTed to.

pg_write_all_data

Write all data (tables, views, sequences), as if having INSERT,
UPDATE, and DELETE rights on those objects, and USAGE rights on
all schemas, even without having it explicitly. This role does not
have the role attribute BYPASSRLS set. If RLS is being used, an
administrator may wish to set BYPASSRLS on roles which this role is
GRANTed to.

All privileges without using predefined roles (any Postgres version)

Commands must be executed while connected to the right database. Make sure of it.

The role needs (at least) the USAGE privilege on the schema. Again, if that's granted to PUBLIC, you are covered. Else:

GRANT USAGE ON SCHEMA public TO my_user;

Or grant USAGE on all custom schemas:

DO
$$
BEGIN
   -- RAISE NOTICE '%', (  -- use instead of EXECUTE to see generated commands
   EXECUTE (
   SELECT string_agg(format('GRANT USAGE ON SCHEMA %I TO my_user', nspname), '; ')
   FROM   pg_namespace
   WHERE  nspname <> 'information_schema' -- exclude information schema and ...
   AND    nspname NOT LIKE 'pg\_%'        -- ... system schemas
   );
END
$$;

Then, all permissions for all tables (requires Postgres 9.0 or later).

And don't forget sequences (if any):

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user;

Alternatively, you could use the "Grant Wizard" of pgAdmin 4 to work with a GUI.

This covers privileges for existing objects. To also cover future objects, set DEFAULT PRIVILEGES. See:

• Grant privileges for a particular database in PostgreSQL
• How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

There are some other objects, the manual for GRANT has the complete list. As of Postgres 14:

privileges on a database object (table, column, view, foreign table, sequence, database, foreign-data wrapper, foreign server, function, procedure, procedural language, schema, or tablespace)

But the rest is rarely needed. More details:

• Grant privileges for a particular database in PostgreSQL
• How to grant all privileges on views to arbitrary user

Consider upgrading to a current version.

How to grant privileges to one user to access other users in Oracle?

You can't really do that. With many DDL privileges - as astentx pointed out - you either are constrained to what you own, or you can affect ANY table in the system, not just one other user. There's no middle ground unless you're also working with add-on enterprise products like Database Vault. If you're talking about DML (insert, update, delete of data), then grant the specific table privileges to a role and grant the role to DEMO1.

create role demo2_dml;
grant insert, update, delete on demo2.table_a to demo2_dml;
grant insert, update, delete on demo2.table_b to demo2_dml;
...
grant role demo2_dml to demo1;
alter user demo1 default role all;

Alternatively, if you must have DDL privileges as well, you could give DEMO1 proxy privileges to become DEMO2 and assume all of its privileges on its objects.

alter user demo2 grant connect through demo1;

Then connect using demo1[demo2] as the username, with demo1's password:

connect demo1[demo2]/demo1password@database_service

demo1 then becomes demo2 (without needing to know demo2's password) and can do anything demo2 would be able to do. demo1 would not have access to its own objects while doing this, however.

MySQL: Grant **all** privileges on database

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' WITH GRANT OPTION;

This is how I create my "Super User" privileges (although I would normally specify a host).

IMPORTANT NOTE

While this answer can solve the problem of access, WITH GRANT OPTION creates a MySQL user that can edit the permissions of other users.

The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.

For security reasons, you should not use this type of user account for any process that the public will have access to (i.e. a website). It is recommended that you create a user with only database privileges for that kind of use.

Grant select on views which use functions

You could create the function with owner who can select from the table users. Such a function should be created with SECURITY DEFINER clause, so it will be executed with the owner rights.

More information you can find here: http://www.postgresql.org/docs/9.0/interactive/sql-createfunction.html

You can also GRANT EXECUTE privileges on functions. See GRANT in the docs.

Grant a user permission to only view a MySQL view and nothing else

GRANT SELECT ON database1.view1 TO 'someuser'@'somehost';

---

Related Topics