pg mysql oracle 中的schema

这篇具有很好参考价值的文章主要介绍了pg mysql oracle 中的schema。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

 1、schema。

pg中的schema表示当前db中数据库对象的命名空间(namespace),数据库对象包括但不限于表、函数、视图、索引等。

对于熟悉mysql的人来说,在第一次看到pg中的schema的概念时,可能会疑惑,schema不是表示database的吗?

注:mysql中schema和database是一个概念。create database 和create schema的效果是相同的。

Oracle 中的schema的和用户名相同,schema用于 存放对象包括但不限于表、函数、视图、索引等。

schama 在PG中概念最小,在mysql中概念最大

需要注意的是PostgreSQL中的用户(和角色)是全局对象,不是在数据库中定义的,而是在实例级别定义的。(oracle cdb的root# )schema由用户在特定数据库中创建,并包含数据库对象。 

Oracle CDB之前一个实例或者多个实例(RAC)对应一个数据库,其他数据库都是默认一个实例多个数据库。这样用户就是数据库专用的,CDB后和其他数据库对齐。

做个测试:

pg mysql oracle 中的schema

但是在pg中schema并不等同于database。pg中一个db可以有一个或多个模式,不同模式可以有具有相同名称的各种对象。如下图所示:

pg mysql oracle 中的schema

图片来源:

https://blog.dbi-services.com/a-schema-and-a-user-are-not-the-same-in-postgresql/

2、怎么列出当前db中的所有模式?

psql 中使用\dn+

postgres=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
(1 row)

在pg中数据库对象都是指向特定的schema的。默认情况下每个pg的数据库都有一个名为public的schema,如果create语句没有指定模式,新对象将默认属于该模式。 类似sqlserver的dbo

postgres=# create table t1(id int);
CREATE TABLE
postgres=# \dt+
                                   List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+------+-------+----------+-------------+---------------+---------+-------------
 public | t1   | table | postgres | permanent   | heap          | 0 bytes |
(1 row)

可以看到t1表的模式是public。也可以通过查询pg_tables表的schemaname字段来查看表的模式。

postgres=# select schemaname from pg_tables where tablename = 't1';
 schemaname
------------
 public
(1 row)

3、怎么删除一个模式?

我们可以删除public模式吗?

可以。

试一下:

postgres=# drop schema public cascade;
NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to function add(integer,integer)
drop cascades to function add1(integer,integer)
drop cascades to function add2(integer,integer)
drop cascades to table t1
DROP SCHEMA

因为我们删除了public,所以此时portgres数据库中没有任何的模式了,这时候我们再create table看看会发生什么。

postgres=# \dn+
                List of schemas
 Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
(0 rows)

postgres=# create table t1(id int);
ERROR:  no schema has been selected to create in
LINE 1: create table t1(id int);
                     ^

可以看到提示:ERROR:  no schema has been selected to create in。这也说明了数据库对象必须指向一个schema。

4、怎么创建模式?

postgres=# create schema my_schema;
CREATE SCHEMA
postgres=# \dn+
                    List of schemas
   Name    |  Owner   | Access privileges | Description
-----------+----------+-------------------+-------------
 my_schema | postgres |                   |
(1 row)

这时候我们再创建表t1,就可以指定schema。

create table my_schema.t1 ( a int );

此时我们使用\d命令列出所有表。会提示Did not find any relations.原因是search_path中没有设置my_schema。

postgres=# \d
Did not find any relations.

将my_schema添加到search_path中,即可显示出来。

pg mysql oracle 中的schema

5、什么是search_path?

search_path类似于linux的path环境变量。它是一个模式名列表,当我们不使用数据库对象的限定名时,pg会检查这些模式名。例如,当我们执行select * from mytable; 没有指定模式。此时pg会在search_path中列出的模式中查找这个表。它选择它找到的第一个匹配项。默认是$user,public。

怎么修改search_path?

set search_path = "$user", public, my_schema

6、pg中的user。

默认的pg安装后会包含一个postgres的超级用户。我们如果需要创建新用户,需要以postgres用户连接到pg, 然后使用create user(或create role)创建其他用户。

postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

用户和角色的区别是什么?官网上的描述是:

别名

CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE.

也就是说user和role是相同的概念,唯一的区别是create user默认有login权限,而create role没有。

postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 u1        |                                                            | {}        |
 u2        | Cannot login                                               | {}        |

需要注意的是PostgreSQL中的用户(和角色)是全局对象,不是在数据库中定义的,而是在实例级别定义的。模式由用户在特定数据库中创建,并包含数据库对象。

我们创建了新用户后,如果想使用它, 使用u1用户连接数据库。

/usr/local/postgresql/bin/psql -U u1  -h 127.0.01 -p 5432 -d postgres

登陆成功后,我们创建一个名为testdb的数据库。

postgres=> create database testdb;
ERROR:  permission denied to create database
postgres=>

提示没有权限。如果想要有create database的权限应该怎么做?

使用postgres用户连接数据库,执行alter user u1 with createdb;

postgres=# alter user u1 with createdb;
ALTER ROLE
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 u1        | Create DB                                                  | {}        |
 u2        | Cannot login                                               | {}        |

然后重新使用u1连接数据库。

postgres=> create database testdb;
CREATE DATABASE
postgres=> \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8673 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8417 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8417 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
 testdb    | u1       | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8417 kB | pg_default |
(4 rows)

你会发现这个testdb的Owner的是u1。这里需要注意这个owner的概念。

删除一个对象或以任何方式改变其定义的权利不被视为可授予的特权,它是owner所固有的。不能被授予或撤销。

怎么理解这句话?

举个例子,我们以另外一个非superuser用户u2来连接数据库,尝试去删除testdb,看看会发生什么。

[ops@test ~]$ /usr/local/postgresql/bin/psql -U u2 -h 127.0.01 -p 5432 -d postgres
psql (14beta1)
Type "help" for help.

postgres=> \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8673 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8417 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8417 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
 testdb    | u1       | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8417 kB | pg_default |
(4 rows)

postgres=> drop database testdb;
ERROR:  must be owner of database testdb

如果我们把testdb的所有权限赋予给u2这个用户呢?

postgres=# GRANT ALL PRIVILEGES ON DATABASE "testdb" to u2;
GRANT

重新用u2来连接数据库,并删除testdb。

postgres=> drop database testdb;
ERROR:  must be owner of database testdb

依然报错。

也就是说如果我们想删除一个数据库对象,必须是该对象的owner才行。

7、user的privilges。

在上面的例子中,我使用u1创建了testdb,并使用

GRANT ALL PRIVILEGES ON DATABASE "testdb" to u2;

将testdb的权限授予给了u2。

但是这个时候,u2真正拥有了这个testdb的所有权限了吗?

使用u1连接数据库,列出所有的表。

testdb=> \c testdb
You are now connected to database "testdb" as user "u2".
testdb=> \d+
                                  List of relations
 Schema | Name | Type  | Owner | Persistence | Access method |  Size   | Description
--------+------+-------+-------+-------------+---------------+---------+-------------
 public | t1   | table | u2    | permanent   | heap          | 0 bytes |
 public | t2   | table | u1    | permanent   | heap          | 0 bytes |
(2 rows)

发现其中表t1的owner是u2,表t2的Owenr的是u1,我们看下u2能否访问1、t2表。

[ops@test~]$ /usr/local/postgresql/bin/psql -U u2 -h 127.0.01 -p 5432 -d postgres
psql (14beta1)
Type "help" for help.

postgres=> \c testdb
You are now connected to database "testdb" as user "u2".
testdb=> select * from t1;
 id
----
(0 rows)

testdb=> select * from t2;
ERROR:  permission denied for table t2

可以发现,u2可以查询t1,但是不能查询t2。这个时候要怎么办?

postgres用户连接数据库testdb。然后执行:GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO u2;

[ops@test ~]$ usr/local/postgresql/bin/psql -U postgres  -h 127.0.01 -p 5432 -d postgres
psql (14beta1)
Type "help" for help.

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO u2;
GRANT

这时候再使用t2连接到testdb。

[ops@testdb ~]$ usr/local/postgresql/bin/psql -U u2 -h 127.0.01 -p 5432 -d postgres
psql (14beta1)
Type "help" for help.

postgres=> \c testdb
You are now connected to database "testdb" as user "u2".
testdb=> select * from t1;
 id
----
(0 rows)

testdb=> select * from t2;
 id
----
(0 rows)

发现t2表可以访问了。

在创建新角色并授予它们访问各种数据库对象的权限的过程中,权限必须在

数据库、模式和模式对象

GRANT ALL PRIVILEGES ON DATABASE "testdb" to u2;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO u2;

---------------------其实着一个数据库就相当于早期的一个oracle实例,建了多个用户,对应多个schema,各个用户访问对方的表要授权一个道理。   只是这个库有一个专用的用户,之前oracle的库没有专有用户。 专用用户对这个库什么都能干。别人可以借用库,但是访问就不行。 

那反过来 库是u1的,u1是不是随便访问u2呢? 下面的结果发现也是不能的,所以数据库的owner是谁不重要,owner只能删库,但是不是查看表。

我使用u1创建了testdb,并使用 将testdb的权限授予给了u2。
表t1的owner是u2,表t2的Owenr的是u1,我们看下u2能否访问1、t2表。
u2可以查询t1,但是不能查询t2

反之也然,对方只是借用了你的库,但是你没权限访问

[postgres@pg-192-134 ~]$ psql -U u1 -d testdb
psql (14.7)
Type "help" for help.

testdb=> \l
                                   List of databases
    Name    |   Owner    | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+------------+----------+-------------+-------------+-----------------------
 ecology    | ecology    | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 keepalived | keepalived | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 postgres   | postgres   | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0  | postgres   | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
            |            |          |             |             | postgres=CTc/postgres
 template1  | postgres   | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
            |            |          |             |             | postgres=CTc/postgres
 testdb     | u1         | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/u1               +
            |            |          |             |             | u1=CTc/u1            +
            |            |          |             |             | u2=CTc/u1
(6 rows)

testdb=> \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | t1   | table | u1
 public | t2   | table | u2
(2 rows)

testdb=> select *from t1;
 id 
----
(0 rows)

testdb=> select * from t2;
ERROR:  permission denied for table t2
testdb=> exit
[postgres@pg-192-134 ~]$ psql -U u2 -d testdb
psql (14.7)
Type "help" for help.

testdb=> \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | t1   | table | u1
 public | t2   | table | u2
(2 rows)

testdb=> \l
                                   List of databases
    Name    |   Owner    | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+------------+----------+-------------+-------------+-----------------------
 ecology    | ecology    | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 keepalived | keepalived | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 postgres   | postgres   | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0  | postgres   | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
            |            |          |             |             | postgres=CTc/postgres
 template1  | postgres   | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
            |            |          |             |             | postgres=CTc/postgres
 testdb     | u1         | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/u1               +
            |            |          |             |             | u1=CTc/u1            +
            |            |          |             |             | u2=CTc/u1
(6 rows)

testdb=> select * from t2;
 id 
----
(0 rows)

testdb=> select * from t1;
ERROR:  permission denied for table t1
testdb=> 

级别分别授予。例如,如果需要授予对表的访问权,还必须确保角色对表所在的数据库和模式具有访问权。如果缺少任何权限,则该角色无法访问表。文章来源地址https://www.toymoban.com/news/detail-473866.html

到了这里,关于pg mysql oracle 中的schema的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL必知必会:MySQL中的Schema与DataBase

    涉及到数据库的模式有很多疑惑,问题经常出现在模式和数据库之间是否有区别,如果有,区别在哪里。 取决于数据库供应商 对schema(模式)产生疑惑的一部分原因是数据库系统倾向于以自己的方式处理模式 (1)MySQL的文档中指出,在物理上,模式与数据库是同义的,所以

    2023年04月27日
    浏览(44)
  • Oracle/MySQL/PG/SQL Server关系数据库中NULL与空字符串的区别

    在Oracle数据库中,\\\'\\\'(空字符串)与null是什么关系呢? \\\'\\\'(空字符串)是否会等同(或者说等价于)于null值呢?\\\'\\\'跟\\\' \\\'(长度为零的空字符串或包含一个或多个空格的空字符串)是否又等价?下面我们测试一下 如上所示,插入\\\'\\\'时,Oracle数据库确实将其等同与null,但是, 像\\\'

    2024年02月16日
    浏览(65)
  • 查询Oracle和MySQL数据库中当前所有连接信息

    查询Oracle当前所有连接信息: 查询MySQL当前所有连接信息: 在这两个查询中,我为每个字段添加了中文别名,以提高查询结果的可读性

    2024年02月12日
    浏览(72)
  • MySQL、Oracle 获取当前系统时间、年份、季度、月份、日期、天数、周数

    1.1.1 获取当前系统时间:NOW() NOW() : 获取当前系统时间, 返回的字符串格式为 YYYY-MM-DD HH:MM:SS。 参考案例: 1.1.2 获取当前日期:CURDATE() 参考案例: 1.1.3 获取当前时间:CURTIME() 参考案例: 1.2.1 获取当前年份:YEAR(NOW()) 参考案例: 1.2.2 获取当前季度:QUARTER(NOW()) 参考案例:

    2024年01月16日
    浏览(61)
  • 基于ora2pg迁移Oracle19C到postgreSQL14

    📢📢📢📣📣📣 哈喽!大家好,我是【IT邦德】,江湖人称jeames007,10余年DBA及大数据工作经验 一位上进心十足的【大数据领域博主】!😜😜😜 中国DBA联盟(ACDU)成员,目前服务于工业互联网 擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发,备份恢复,安装迁移,性能优

    2024年02月05日
    浏览(43)
  • MySQL Performance Schema

    1.  Performance Schema Lock Tables MySQL安装以后,我们会看到有这么两个数据库:information_schema 和 performance_schema ,它们对于排查问题是非常有用的。 Performance Schema 是一种存储引擎,默认情况下,它是启用的。 performance_schema数据库的名称是小写的,其中的表的名称也是小写的。查

    2024年02月05日
    浏览(42)
  • xml schema中的all元素

    xml schema中的all元素表示其中的子元素可以按照任何顺序出现,每个元素可以出现0次或者1次。 https://www.w3.org/TR/xmlschema-1/#element-all maxOccurs的默认值是1,minOccurs 的默认值是1。

    2024年02月07日
    浏览(41)
  • 数据库将一张表中的数据更新到另一张表(Oracle、MySQL)

            方式一(推荐)         方式二         方式一比方式二效率快很多

    2024年02月07日
    浏览(70)
  • 【MySQL】- 06 Schema与数据类型优化

    当一个资源变得效率低下的时候,应该了解一下为什么会这样。有如下可能原因: 1.资源被过度使用,余量已经不足以正常工作。 2.资源没有被正确配置 3.资源已经损坏或者失灵 因为慢查询,太多查询的时间过长而导致堆积在逻辑上。 慢查询到底是原因还是结果?在深入调

    2024年02月09日
    浏览(45)
  • MySQL实践——sys schema介绍及使用

    sys schema介绍 说到诊断MySQL的性能问题,都知道从performance_schema去获取想要的数据,但是其复杂程度让使用人员使用起来很不方便。在MySQL5.7中,performance_schema已经有80多张表,每张表都是各种统计信息的罗列。另外这些表和information_schema中的部分表也有关联,导致使用人员使

    2024年02月14日
    浏览(56)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包