ORACLE中的default role/set role

发表于 2011 年 5 月 3 日 由 惜分飞

oracle权限体系中有个default role，比较难以理解。下面用实例说明一下作用。
我们可以给某个用户分配一些角色，比如role r1,r2,r3,r4，而其中可以将某些角色比如r1设置为default role，其他的不设置成default role，这样，当该用户登录时，自动具有default role中所包含的权限，其他的角色所具有的权限要通过set role 角色来获得。
下面我们举个例子：
（1）sys用户作为sysdba登录，创建4个角色：
create role r1;
create role r2 identified by r2;
create role r3 identified by r3;
create role r4 identified by r4;
（2）sys用户赋予这四个角色对应的权限：
grant create session to r1;
grant select on hr.test to r2;（这里hr.test是我新创建的一个表，里面有ID和name两列）
grant update(name) on hr.test to r3;
grant insert on hr.test to r3;
grant delete on hr.test to r4;
（3）sys用户创建一个用户u3
create user u3 identified by u3;
（4）将角色r1,r2,r3,r4赋予用户u3
grant r1,r2,r3,r4 to u3;
在修改用户u3的默认角色前，r1,r2,r3,r4 角色均为u3的 default role，以u3用户登录，查询、增删改hr.test，都没有问题。
（5）现在sys用户修改用户u3的default role，仅将r1作为u3的默认角色：
alter user u3 default role r1; –此时将覆盖原来的设置，u3 的default role =r1，仅仅有登录权限。
（6）用户u3 log off ，然后再log on，进去后发现，
查询、增删改hr.test都不能进行。
（7） 用户自己打开role权限
set role r2 identified by r2;
这时执行 select * from hr.test,发现没有问题。增删改不行。
将对应的角色打开：
set role r3 identified by r3;
此时修改和插入记录没有问题，但是select * from hr.test 确发现不行了。证明此时用户所属的角色仅仅是默认角色r1，和刚刚打开的角色r3，而r2被set role r3 identified by r3;覆盖掉了。
那要同时有r2，r3，r4的权限怎么办呢？
set role r2 identified by r2,r3 identified by r3,r4 identified by r4。此时就同时对hr.test可以进行查询，增删改了。
不过set role 的效果是临时的，只是当前session有效，其他的session无效，当结束当前session后再登录，又只有default role 的权限了。

发表在 Oracle | 一条评论

MySql分区表管理

发表于 2011 年 5 月 2 日 由 惜分飞

1、RANGE和LIST分区的管理
1.1）删除分区
ALTER TABLE table_name DROP PARTITION partition_name;

1.2）添加分区
ALTER TABLE table_name ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
Note:
1）分区的范围或者值不能重复；
2）在范围分区中，不能添加比当前范围最大值小的分区

1.3）重新定义分区
ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions);
其中，tbl_name 是分区表的名称，partition_list 是通过逗号分开的、一个或多个将要被改变的现有分区的列表。partition_definitions 是一个是通过逗号分开的、新分区定义的列表，它遵循与用在“CREATE TABLE”中的partition_definitions 相同的规则。应当注意到，在把多少个分区合并到一个分区或把一个分区拆分成多少个分区方面，没有限制。例如，可以重新组织成员表的四个分区成两个分区，具体实现如下：
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
PARTITION m0 VALUES LESS THAN (1980),
PARTITION m1 VALUES LESS THAN (2000)
);

2、HASH和KEY分区的管理
2.1）合并HASH或KEY分区
ALTER TABLE … COALESCE PARTITION
Note：对于LINEAR HASH和LINEAR KEY分区同样有效

2.2）添加分区
ALTER TABLE table_name ADD PARTITION PARTITIONS 18;–18比原来值大

3、分区维护
3.1） 重建分区
ALTER TABLE table_name REBUILD PARTITION (p0, p1);

3.2）优化分区
ALTER TABLE table_name OPTIMIZE PARTITION (p0, p1);

3.3）分析分区
ALTER TABLE table_name ANALYZE PARTITION (p0, p1);

3.4）检查分区
ALTER TABLE table_name CHECK PARTITION (p0, p1);

发表在 MySQL | 评论关闭

MySql创建分区表

发表于 2011 年 5 月 2 日 由 惜分飞

一、前沿知识
1、分区表类型
RANGE分区、LIST分区、HASH分区、KEY分区、子分区

2、查询是否支持分区表
mysql> SHOW VARIABLES LIKE ‘%partition%’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| have_partition_engine | YES |
+———————–+——-+
1 row in set (0.00 sec)

3、MySQL分区处理NULL值的方式
MySQL中的分区在禁止空值（NULL）上没有进行处理，如果出现NULL，MySQL 把NULL视为0

二、分区表的创建
1、RANGE分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

2、 LIST分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id)
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

Note：LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

3、 HASH分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(YEAR(hired))
PARTITIONS 4;

3.1、LINEAR HASH分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH(YEAR(hired))
PARTITIONS 4;

Note：线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数

4、KEY分区

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
) 
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

Note：
1）按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。

2）“CREATE TABLE … PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

5、子分区

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
    (
        PARTITION p0 VALUES LESS THAN (1990)
        (
            SUBPARTITION s0 
                DATA DIRECTORY = '/disk0/data' 
                INDEX DIRECTORY = '/disk0/idx',
            SUBPARTITION s1 
                DATA DIRECTORY = '/disk1/data' 
                INDEX DIRECTORY = '/disk1/idx'
        ),
        PARTITION p1 VALUES LESS THAN (2000)
        (
            SUBPARTITION s0 
                DATA DIRECTORY = '/disk2/data' 
                INDEX DIRECTORY = '/disk2/idx',
            SUBPARTITION s1 
                DATA DIRECTORY = '/disk3/data' 
                INDEX DIRECTORY = '/disk3/idx'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE
        (
            SUBPARTITION s0 
                DATA DIRECTORY = '/disk4/data' 
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s1 
                DATA DIRECTORY = '/disk5/data' 
                INDEX DIRECTORY = '/disk5/idx'
        )
    );

Note:子分区是分区表中每个分区的再次分割，DATA DIRECTORY/INDEX DIRECTORY确定数据和索引的存储位置

发表在 MySQL | 一条评论