GAUSSDB-权限管理

## 示例

扩展：
        在GaussDB DWS中，ORIENTATION参数用于指定表的存储方向，即数据在存储时是按行存储还是按列存储。这个参数的取值包括ROW(行存储)和COLUMN(列存储)。
        1. ORIENTATION = ROW：
        当使用ROW存储时，数据按行存储在磁盘上。这意味着每一行数据都会被连续地存储在磁盘上，适合于OLTP(联机事务处理)场景，因为它更适合对少量行的随机读写操作。
        2. ORIENTATION = COLUMN：
        当使用COLUMN存储时，数据按列存储在磁盘上。这意味着每一列数据会被连续地存储在磁盘上，适合于OLAP(联机分析处理)场景，因为它更适合对大量列的扫描和聚合操作。

    
    
    查询表的模式名和表名
    select c.relname,n.nspname
    from pg_class c
    join pg_namespace n 
    on c.relnamespace = n.oid
    where c.relname = 'p_table';
    
    
    查询表的所在节点nodeoids信息
    select
    	t1.relname,
    	t2.nodeoids
    from
    	pg_class t1,
    	pgxc_class t2,
    	pg_namespace t3
    where
    	t1.oid = t2.pcrelid
    	and t1.relnamespace = t3.oid
    	and t1.relname = 'p_table'
    	and t3.nspname = 'gaussdb';
      
     
    查询表所在的节点实例信息
    select * from pgxc_node
    where
    	oid in 
    (
    	select
    		(unnest(string_to_array(xc.nodeoids::varchar(100),' ')))::oid
    	from
    		pg_class c
    	inner join pgxc_class xc on xc.pcrelid = c.oid
    	inner join pg_namespace ns on ns.oid = c.relnamespace
    	where relname = 'p_table' and nspname = 'gaussdb'
    );

    查询指定表的全部索引，显示tablename，indexname，indexdef
    select tablename, indexname, indexdef from pg_indexes where schemaname='gaussdb' and tablename ='test';
    
    
   查询指定表的名称，显示表名称，存储类型，存储节点
    select t1.relname, t2.pclocatortype, t2.nodeoids
    from pg_class t1 join pgxc_class t2
    	on t1.oid = t2.pcrelid
    where t1.relname ='test';
    
    
    查询此表所在的节点和节点存储数据量
    SELECT a.count,b.node_name FROM 
    (SELECT count(*) AS count,xc_node_id FROM test  
    GROUP BY xc_node_id) a, pgxc_node b 
    WHERE a.xc_node_id=b.node_id ORDER BY a.count desc;
    
    
    在上面的表中添加地点字段，并且不能为空
    alter table if exists test add column location varchar(200) not null;
    
    
    重建上面表的主键索引(使用1个SQL语句)
    查看指定表中的主键名称
    SELECT
        conname AS constraint_name,
        a.attname AS primary_key_column
    FROM
        pg_constraint AS c
    JOIN
        pg_attribute AS a ON a.attnum = ANY(c.conkey)
        AND a.attrelid = c.conrelid
    WHERE
        c.contype = 'p'
    AND c.conrelid = 'test'::regclass;
    
    重建主键
    reindex index test_pkey;
    
    
    删除上面表的主键索引
    查看指定表中的主键名称
    SELECT
        conname AS constraint_name,
        a.attname AS primary_key_column
    FROM
        pg_constraint AS c
    JOIN
        pg_attribute AS a ON a.attnum = ANY(c.conkey)
        AND a.attrelid = c.conrelid
    WHERE
        c.contype = 'p'
    AND c.conrelid = 'test'::regclass;
    
    
    删除指定表中的主键
    ALTER TABLE test DROP CONSTRAINT test_pkey;
    
    
   为上面表的id列添加约束，值必须大于100并且小于10000
    alter table test add constraint test_id_check check(id > 100 and id < 10000);
    
    插入数据测试
    insert into test values(10,'kemu1',100,100,'beiji');
    insert into test values(20000,'kemu1',100,100,'beiji');
    insert into test values(123,'kemu1',100,100,'beiji');
    
    
    建表时指定多个约束例子
    CREATE TABLE students (
        student_id int NOT NULL,
        student_name VARCHAR(100) NOT NULL,
        age INT CHECK (age >= 0),
        CONSTRAINT pk_student_id PRIMARY KEY (student_id)
    );
    
    创建用户sjh111
    create user sjh111 password 'Huawei@123';
    
    
    将表sjh_test表的读取，删除权限授权给sjh111用户
    --普通用户只有public模式的权限，需要将当前schema使用权赋予给用
    grant usage on schema gaussdb to sjh111;
    --赋权
    grant select,delete on table gaussdb.sjh_test to sjh111;
    
    
    为用户sjh111授权在sjh_test表的a、b列上的查询、添加和更新权限
    grant select(a,b),insert(a,b),update(a,b) on table gaussdb.sjh_test to sjh111; 
    
    
    将用户sjh111授权在sjh_test表的a列上的查询、添加和更新权限收回
    revoke select(a),insert(a),update(a) on table gaussdb.sjh_test from sjh111; 
    
    
    创建角色sjh_audit，此角色拥有审计权限
    create role sjh_audti with auditadmin password 'Huawei@123';
    
    
    将sjh112角色权限授予给用户sjh111
    grant sjh112 to sjh111;
    
    
    将sjh112角色权限授予给用户sjh111,并允许用户将此权限再授权给其他用户或角色
    grant sjh112 to sjh111 with admin option;
    
    
    创建新用户sjh113，设置使用有效期”2023-01-28”至”2026-01-01”
    create user sjh113 password 'Huawei@123' valid begin '2023-01-28' valid until '2026-01-01';
    
    
    创建用户user1
    create user user1 password 'Huawei@123';
    grant all privilege to user1;
    
    create database user1_db owner user1;
    
    
    查看用户user1和数据库的相关权限，要求显示数据库名、用户名、数据库的权限
    select a.datname db_name,b.rolname user_name,string_agg(a.pri_t,',') privilege_list 
    from 
    (select datname,
    (aclexplode(nvl(datacl, acldefault('d'::"char",datdba)))).grantee as grantee,
    (aclexplode(nvl(datacl, acldefault('d'::"char", datdba)))).privilege_type as pri_t 
    from pg_database 
    where datname not like 'template%'
    ) a,pg_roles b 
    where (a.grantee=b.oid or a.grantee=0) 
    and b.rolname='user1' 
    group by a.datname,b.rolname;
    
    
    将表tab_sp1的select和alter权限赋给user1；
    grant select,alter on gaussdb.tab_sp1 to user1;
    
    
    查询tab_sp1的owner，要求显示表名和owner
    select tablename,tableowner from pg_tables where schemaname='gaussdb' and tablename='tab_sp1'
    
    
    查询tab_sp1的表权限，要求显示schema名、表名、用户名
    SELECT distinct table_schema, table_name, grantee 
    FROM information_schema.table_privileges  
    WHERE table_name = 'tab_sp1';
    
    
    查询对表tab_sp1有操作权限的用户，要求显示2列：用户名、操作权限
    select grantee as user ,privilege_type
    from information_schema.table_privileges
    where is_grantable = 'YES' and table_name = 'tab_sp1';
    
    使用两个查询语句，查看“postgres”数据库的最大连接数和已使用连接数
    --数据库的最大连接数
    select datname,datconnlimit from pg_database where datname='postgres';
    --数据库已使用连接数 
    select count(*) from  pg_stat_activity where datname='postgres';
    
    
    创建用户user_test，指定该用户具有创建数据库和创建角色的权限
    CREATE USER user_test WITH CREATEDB CREATEROLE PASSWORD 'Test@123';
    
    
   创建表table_test，此表中包含一个名为col_test的列，为用户user_test授权在table_test表的col_test列上的查询、更新权限
    create table table_test(col_test int);
    grant usage on schema gaussdb to user_test;
    grant select(col_test),update(col_test) on table_test to user_test;
    
    
    收回用户user_test在table_test表在col_test列上的更新权限
    revoke update(col_test) on table_test from user_test;
    
    
    创建角色role_test，此角色拥有审计权限
    create role role_test with auditadmin password 'Huawei@123';
    
    
    将角色role_test的权限授权给用户user_test，并允许用户将此权限再授权给其他用户或角色
    grant role_test to user_test with admin option;
    
    
    用户user_test账号被盗，请手动锁定此账号
    alter user user_test account lock;
    
    
   级联删除用户user_test，并重新创建，将账号设置为在2023年国庆节期间有效
    --级联删除用户
    drop user if exists user_test cascade;
    --创建用户
    create user user_test password 'Test@123' valid begin '2023-10-01 00:00:00' valid until '2023-10-07 23:59:59';
    
    查看全局最大连接数
    show max_connections;
    
    
    创建数据库指定最大连接数，指定最大连接为100000，并使用SQL查看展示数据库名称，最大连接数
    --创建数据库，并指定最大连接数
    create database test_db connection limit 100000;
    --查看数据库、最大连接数
    select datname,datconnlimit from pg_database where datname='test_db';
    
    
    创建用户并指定最大连接数，指定最大连接为20000，并使用SQL查看展示用户名称，最大连接数
    --创建用户，并指定最大连接数
    create user sjh_max password 'Huawei@123' connection limit 20000;
    --查看用户名、最大连接数
    select rolname,rolconnlimit from pg_roles where rolname='sjh_max';
    
    
    修改数据库的最大连接数，将最大连接数修改为200000
    alter database test_db connection limit 200000;
    
    
    修改用户最大连接数，将最大连接数修改为1000
    alter user sjh_max connection limit 10000;
    
    创建user2用户，user2用户需要具备创建数据库的权限
    create user user2 createdb password 'Huawei@123';
    
    
    查看用户的连接数上限
    select rolname,rolconnlimit from pg_roles where rolname='user2';
    
    
    设置user2用连接数为100
    alter user user2 connection limit 100;
    
    
    查询gaussdb用户连接数上限，显示库，上限数量
    select d.datname,d.datconnlimit
    from pg_catalog.pg_database d
    join pg_catalog.pg_roles r on r.oid = d.datdba 
    where r.rolname='gaussdb'
    
    
    查询gaussdb用户已使用的会话连接数
    select usename,count(*) 
    from pg_catalog.pg_stat_activity 
    where usename='gaussdb'
    group by usename;



    查看所有用户已使用的会话连接数
    select usename,count(*) 
    from pg_catalog.pg_stat_activity 
    group by usename;


    查询库最大连接数
    show max_connections;


    查询会话状态，显示datid、pid、state
    select datid,pid,state from pg_stat_activity;