postgresql 赋权语句 grant的正确使用说明

grant select on all tables in schema public to username;

此句是有效的复制语句

PG中有schema的概念，

以下的语句就是不行

网上得来终觉浅，错误比较多，还是要自己试试才行。

补充：postgresql关于访问视图需要的权限

某个用户访问一个视图，这个用户需要具备这个视图的schema的usage和这个视图本身的select权限，如果视图的基表来自其他schema，可能还需要其他schema的usage权限(根据postgresql改造的redshift发现了此问题)，但是这个用户不需要视图对应基表的select权限

如果基表被修改了或重建了，需要把基表重新赋权给视图的owner，否则视图无法生效，但是不需要把基表授权给需要访问视图的用户，比如视图view1的owner是viewowner，一个用户user1有访问view1的权限，一旦view1的基本table1被重建了，需要把基表table1的权限重新赋给viewowner，但是不需要把table1的权限重新赋给uers1

案例1：

用户u2访问s2.view1,用户u2只需要s2的usage权限和s2.view1的select权限，而不需要s2.view1基表s1.table1的select权限和对应schema s1的usage权限

t1=# \c
You are now connected to database “t1” as user “postgres”
t1=# \dns
List of schemas
Name | Owner
--------±---------
public | postgres
s1 | postgres
s2 | postgres
(3 rows)
t1=# create user u1 password ‘123456';
CREATE ROLE
t1=# create user u2 password ‘123456';
CREATE ROLE
t1=# grant all privileges on schema s1 to u1;
GRANT
t1=# grant all privileges on schema s2 to u1;
GRANT
t1=# \c - u1
You are now connected to database “t1” as user “u1”.
t1=> create table s1.table1(hid int);
CREATE TABLE
t1=> create view s2.view1 as select * from s1.table1;
CREATE VIEW
t1=> \c - u2
You are now connected to database “t1” as user “u2”.
t1=> select * from s2.view1;
ERROR: permission denied for schema s2
LINE 1: select * from s2.view1;
t1=> \c - postgres
You are now connected to database “t1” as user “postgres”.
t1=# grant usage on schema s2 to u2;
GRANT
t1=# \c - u2
You are now connected to database “t1” as user “u2”.
t1=> select * from s2.view1;
ERROR: permission denied for view view1
t1=> \c - postgres
You are now connected to database “t1” as user “postgres”.
t1=# grant select on s2.view1 to u2;
t1=# \c - u2
You are now connected to database “t1” as user “u2”.
t1=> select * from s2.view1;
hid
(0 rows)

案例2：

redshift数据库，用户lukes访问cl_crm.v_account,用户lukes不仅需要cl_crm的usage权限和cl_crm.v_account的select权限，还需要cl_crm.v_account基表对应schema的usage权限，但是不需要基表raw_wam.ibdwsurvey、raw_c3.ibdwsurveyinfo的select权限

创建用户lukes,并授权用户拥有视图cl_crm.v_account对应schema的usage权限

create user lukes password ‘X123_x123'
grant usage on schema cl_crm to lukes

lukes用户查询cl_crm.v_account视图报错

select * from cl_crm.v_account limit 1
Amazon Invalid operation: permission denied for schema raw_wam;
1 statement failed.

对用户lukes授权usage在视图基表对应的schema上

grant usage on schema raw_wam to lukes
grant usage on schema raw_c3 to lukes

lukes用户查询cl_crm.v_account视图继续报错

Amazon Invalid operation: permission denied for relation v_account;

对用户lukes授权访问视图的select权限

grant select on table cl_crm.v_account to lukes

lukes用户查询cl_crm.v_account正常了

cl_crm.v_account视图语句是

CREATE or replace view cl_crm.v_account as with userids as (
select distinct su.ibsu_userid from raw_wam.ibdwsurvey su
left join raw_c3.ibdwsurveyinfo si …)

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

赞 (0)

相关推荐

  • PostgreSQL用户、数据库及表的管理、操作与授权方式

    摘要 PostgreSQL的常用命令 1.登录数据库 /* 切换到数据库用户 */ su - postgres /* 登录 */ psql 登录成功显示如下: bash-4.2$ psql psql (9.3.17) Type "help" for help. postgres=> 2.切换数据库 /* 登录指定数据库 */ psql -U user -d dbname /* 列举数据库 */ \l /* 切换数据库 */ \c dbname 3.用户管理 /* 创建用户 */

  • postgresql限制某个用户仅连接某一个数据库的操作

    创建数据库bbb且owner为用户b: postgres9.6@[local]:5432 postgres# create database bbb owner b; CREATE DATABASE Time: 259.099 ms 默认情况下使用用户c也可以连接数据库bbb: postgres9.6@[local]:5432 postgres# \c bbb c You are now connected to database "bbb" as user "c"

  • Postgresql 赋予用户权限和撤销权限的实例

    1.对数据库授权 postgresql 授权某个数据库的权限给wang 账号 使该账号 只能操作指定DB 不能操作其他DB alter user wang set default_transaction_read_only=on; grant all on database test to wang; grant select on all database test to wang; grant select on all tables in schema public to wang; //

  • PostgreSQL 自定义自动类型转换操作(CAST)

    背景 PostgreSQL是一个强类型数据库,因此你输入的变量.常量是什么类型,是强绑定的,例如 在调用操作符时,需要通过操作符边上的数据类型,选择对应的操作符. 在调用函数时,需要根据输入的类型,选择对应的函数. 如果类型不匹配,就会报操作符不存在,或者函数不存在的错误. postgres=# select '1' + '1'; ERROR: operator is not unique: unknown + unknown LINE 1: select '1' + '1'; ^ HINT:

  • PostgreSQL 实现快速删除一个用户

    背景 在多租户场景或者其他场景下,很多时候需要主动清理一些用户,本文将介绍PostgreSQL 下如何快速删除一个用户(role). 具体方法 一般情况下直接执行 drop role xxx; 就可以把这个用户删除.但是很多时候会因为用户有依赖而报错. 权限依赖 postgres=# create role test with login; CREATE ROLE postgres=# grant all on database postgres to test; GRANT postgres=

  • PostgreSQL报错 解决操作符不存在的问题

    最近才接触到一个用PostgreSQL的项目,然后在开发的过程中发现了这样的一个问题. 错误: 操作符不存在: character = integer 反正还有很多报错的,原因都是类型的转换问题.在mysql中似乎对类型这个概念不是那么敏感,而在PostgreSql中Integer .Long.Date.String 等等之间转换都会存在操作符不存在的报错. 所以在使用非实体进行数据传输的时候,例如Map等等就需要手动设置数据类型. Long orgId = (maps.get("orgId&q

  • postgresql 赋权语句 grant的正确使用说明

    postgresql 赋权语句 grant的正确使用说明

    grant select on all tables in schema public to username; 此句是有效的复制语句 PG中有schema的概念, 以下的语句就是不行 网上得来终觉浅,错误比较多,还是要自己试试才行. 补充:postgresql关于访问视图需要的权限 某个用户访问一个视图,这个用户需要具备这个视图的schema的usage和这个视图本身的select权限,如果视图的基表来自其他schema,可能还需要其他schema的usage权限(根据postgresql改造

  • Mysql8创建用户及赋权操作实战记录

    Mysql8创建用户及赋权操作实战记录

    目录 1.进入mysql 2.创建用户 3.给用户赋权 4.查看用户权限 5.撤销用户权限 6.删除用户 7.查询所有用户及其权限 附:查看用户权限 总结 1.进入mysql mysql -uroot -p 2.创建用户 create user 'testuser1'@'%' identified by '123456'; 这里表示创建一个不限制ip登录的用户 testuser1 该用户的密码是 123456 %代表不限制ip登录 刷新权限,每一次权限更改后都刷新一下 flush privile

  • Oracle 添加用户并赋权，修改密码，解锁，删除用户的方法

    添加用户(随着用户的创建,自动产生与用户同名的schema) CREATE USER "TESTER" PROFILE "DEFAULT" IDENTIFIED BY "TESTER" DEFAULT TABLESPACE "TESTDATA" TEMPORARY TABLESPACE "TESTTEMP" ACCOUNT UNLOCK; 赋权(说实话,这些权限是开发中使用的权限,如果用户生产环境,请自行对

  • MySQL 8.0.18给数据库添加用户和赋权问题

    1. 首选使用root用户登录数据库  mysql -uroot -p 2. 使用mysql 数据库 USE mysql; 3. 为mysql创建用户:dev 密码为:dev12345 CREATE USER dev IDENTIFIED BY 'dev12345'; 4. 查看下用户dev的权限 SELECT * FROM user WHERE user='dev' ; SHOW GRANTS FOR dev; 5. 给用户dev在数据库名为dev_db上赋EXECUTE(执行存储过程),IN

  • postgresql 常用SQL语句小结

    目录 1.查询链接数 2.查看死锁状态 3.删除死锁进程 4.备份数据库 5.还原数据库 6.插入数据 插入单条数据 插入多条数据 从一张表查询到的数据插入到另一张表 7 .查询pg中单张表的大小(不包含索引) 8.查询数据库中所有表的大小 9.按顺序查看索引 10 .查询数据库的大小 11.查询被锁定的表 12 .查询一个Schema下面的所有表的总大小(单位MB,包括索引和数据) 14 .查询表大小按大小排序并分离data与index 1.查询链接数 SELECT sum(numbacken

  • Postgresql 查看SQL语句执行效率的操作

    Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看 SQL 语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句. Explain语法: explain select - from - [where ...] 例如: explain select * from dual; 这里有一个简单的例子,如下: EXPLAIN SELECT * FROM tenk1; QUERY PLAN ---------

  • Linux中利用sudo进行赋权的方法详解

    前言 学习怎么在保护 root 密码的安全性的同时,为可信用户赋予所管理的网络功能和特定服务的权限. 我最近写了一个简短的 Bash 程序来将 MP3 文件从一台网络主机的 USB 盘中拷贝到另一台网络主机上去.拷贝出来的文件存放在一台志愿者组织所属服务器的特定目录下,在那里,这些文件可以被下载和播放. 我的程序还会做些其他事情,比如为了自动在网页上根据日期排序,在拷贝文件之前会先对这些文件重命名.在验证拷贝完成后,还会删掉 USB 盘中的所有文件.这个小程序还有一些其他选项,比如 -h 会显示

  • java实用型-高并发下RestTemplate的正确使用说明

    目录 前言 一.RestTemplate是什么? 二.如何使用 1.创建一个bean 2.使用步骤 三.高并发下的RestTemplate使用 1.设置预热功能 2.合理设置maxtotal数量 总结 前言 如果java项目里有调用第三方的http接口,我们可以使用RestTemplate去远程访问.也支持配置连接超时和响应超时,还可以配置各种长连接策略,也可以支持长连接预热,在高并发下,合理的配置使用能够有效提高第三方接口响应时间. 一.RestTemplate是什么? RestTemplat

  • PostgreSQL数据库中跨库访问解决方案

    PostgreSQL跨库访问有3种方法:Schema,dblink,postgres_fdw. 方法A:在PG上建立不同SCHEMA,将数据和存储过程分别放到不同的schema上,经过权限管理后进行访问. 方法A的示例如下: 测试1(测试postgres超级用户对不同schema下对象的访问) 查看当前数据库中的schema postgres=# \dn List of schemas Name | Owner -------------------+--------- dbms_job_pro

  • Mysql复制表三种实现方法及grant解析

    如何快速的复制一张表 首先创建一张表db1.t,并且插入1000行数据,同时创建一个相同结构的表db2.t 假设,现在需要把db1.t里面的a>900的数据行导出来,插入到db2.t中 mysqldump方法 几个关键参数注释: –single-transaction的作用是,在导出数据的时候不需要对表db1.t加表锁,而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT的方法: –no-create-info的意思是,不需要导出表结构: –result

随机推荐