ORACLE用户、角色、权限

用了ORACLE很久，但对用户、角色、权限还是很模糊，认真看书，查资料整理文档，做下记录，希望从日常使用的角度去看ORACLE的用户、角色与权限

理论性的知识再此省略，建议还是翻翻书透彻点

开工！

先放一张图，可以跳过图，读完文章再回头看看图

说明：双箭头表示用户与角色查那张表，单虚线箭头表示包含关系，角色除了系统自带的dba_roles里定义的以外还可以自己创建定义

 

正式开工：
创建一个表空间，命名为ts_urp指定空间为100M

创建一个用户urp密码urp，默认表空间ts_urp，临时表空间为temp

SQL> create user urp identified by urp default tablespace ts_urp temporary tablespace temp;User created.

创建后尝试使用该用户连接数据库

SQL> connect urp/urpERROR:ORA-01045: user URP lacks CREATE SESSION privilege; logon deniedWarning: You are no longer connected to ORACLE.

提示用户不具有CREATE SESSION权限，查看下此时用于与权限对应表，grantee为URP的记录为空

SQL> connect / as sysdbaConnected.SQL> select * from dba_sys_privs where grantee='URP';no rows selected

我们给用户urp赋予CREATE SESSION权限

SQL> grant CREATE SESSION to urp;Grant succeeded.

再查看一次用户与权限对应表，此时看到URP具有CREATE SESSION权限

SQL> select * from dba_sys_privs where grantee='URP';GRANTEE                        PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---URP                            CREATE SESSION                           NO

那我们试试用这个用户连接数据库

SQL> connect urp/urpConnected.

赋予权限后连接成功

创建表试试

SQL> create table emp(id number,username varchar2(30));create table emp(id number,username varchar2(30))*ERROR at line 1:ORA-01031: insufficient privileges

再次提示权限不足

我们来看看那些权限是于table有关系的

SQL>  select privilege from dba_sys_privs where privilege like '%TABLE%' group by privilege;PRIVILEGE----------------------------------------UNLIMITED TABLESPACECREATE TABLEALTER ANY TABLEDROP TABLESPACEUNDER ANY TABLECOMMENT ANY TABLEMANAGE TABLESPACEUPDATE ANY TABLEDELETE ANY TABLEBACKUP ANY TABLECREATE ANY TABLEPRIVILEGE----------------------------------------DROP ANY TABLEFLASHBACK ANY TABLEINSERT ANY TABLECREATE TABLESPACELOCK ANY TABLESELECT ANY TABLEALTER TABLESPACE18 rows selected.

给个权限后再建表看看

SQL> grant CREATE TABLE to urp;Grant succeeded.SQL> connect urp/urpConnected.SQL> create table emp(id number,username varchar(30));create table emp(id number,username varchar(30))*ERROR at line 1:ORA-01950: no privileges on tablespace 'TS_URP'

提示没有权限在表空间TS_URP上

老办法，回到dba看看tablespace有那些权限

SQL>  select privilege from dba_sys_privs where privilege like '%TABLESPACE%' group by privilege;PRIVILEGE----------------------------------------UNLIMITED TABLESPACEDROP TABLESPACEMANAGE TABLESPACECREATE TABLESPACEALTER TABLESPACE

看起来只能是UNLIMITED TABLESPACE，赋权看看

SQL> connect / as sysdbaConnected.SQL> grant UNLIMITED TABLESPACE to urp;Grant succeeded.SQL> connect urp/urpConnected.SQL> create table emp(id number,username varchar(30));Table created.

看起来成功了

也就是说如果一个用户需要连接数据库并且创建表必须有三个权限
CREATE SESSION            --连接数据库权限
UNLIMITED TABLESPACE    --表空间无限制权限（空间配额）
CREATE TABLE            --建立数据表权限

继续测试
有了数据表，我们插入、修改、删除等基本权限：

SQL> connect urp/urpConnected.SQL> create table emp(id number,username varchar(30));Table created.SQL> insert into emp values(1,'urp');1 row created.SQL> commit;            Commit complete.SQL> update emp set username='urp1' where id=1;1 row updated.SQL> commit;Commit complete.SQL> select * from emp;        ID USERNAME---------- ------------------------------         1 urp1SQL> delete from emp where id=1;1 row deleted.SQL> commit;Commit complete.

看起来没什么问题了

我们试试一些常用的操作：
非空约束

SQL> alter table emp modify (id constraints id_not_null NOT NULL);Table altered.

ok！

主键约束

SQL> alter table emp add constraint pk_id primary key (id);Table altered.

唯一约束

SQL> alter table emp add constraint uk_username unique(username);Table altered.

增加列

SQL> alter table emp add dep varchar(30);Table altered.

增加索引

SQL>  create index idx_emp_dep on emp(dep);Index created.

建立sequence

SQL> create sequence seq_emp_id  2  minvalue 1  3  maxvalue 9999999999  4  start with 5000  5  increment by 1  6  nocache;create sequence seq_emp_id*ERROR at line 1:ORA-01031: insufficient privileges

提示没有权限,继续老办法，回到dba用户执行

SQL> select privilege from dba_sys_privs where privilege like '%SEQUENCE%' group by privilege;PRIVILEGE----------------------------------------CREATE ANY SEQUENCEALTER ANY SEQUENCEDROP ANY SEQUENCESELECT ANY SEQUENCECREATE SEQUENCE

备注：CREATE SEQUENCE & CREATE ANY SEQUENCE 主要区别在于：
CREATE SEQUENCE：可以在当前用户下建立SEQUENCE
CREATE ANY SEQUENCE：可以在其他用户下建立SEQUENCE，权限表中其他有关ANY的权限也是这样。

赋予用户CREATE SEQUENCE权限

SQL> grant CREATE SEQUENCE to urp;Grant succeeded.SQL> connect urp/urpConnected.SQL> create sequence seq_emp_id  2  minvalue 1  3  maxvalue 500000  4  start with 5000  5  increment by 1  6  nocache;Sequence created.SQL> select seq_emp_id.nextval from dual;   NEXTVAL----------      5000SQL>  select seq_emp_id.nextval from dual;   NEXTVAL----------      5001SQL> select seq_emp_id.currval from dual;   CURRVAL----------      5001SQL> insert into emp values(seq_emp_id.nextval,'john','system');1 row created.SQL> commit;Commit complete.SQL> insert into emp values(seq_emp_id.nextval,'tom','hr');1 row created.SQL> commit;Commit complete.SQL> select * from emp;        ID USERNAME                       DEP---------- ------------------------------ ------------------------------      5004 tom                            hr      5002 john                           systemSQL>

存储过程测试（一样遇到问题，赋予权限后ok）

 

SQL> CREATE OR REPLACE PROCEDURE TEST  2  AS  3  BEGIN  4   NULL;  5  END;  6  /create or replace procedure TEST*ERROR at line 1:ORA-01031: insufficient privilegesSQL> connect / as sysdbaConnected.SQL> select privilege from dba_sys_privs where privilege like '%PROCEDURE%' group by privilege;PRIVILEGE----------------------------------------DROP ANY PROCEDUREEXECUTE ANY PROCEDUREALTER ANY PROCEDURECREATE ANY PROCEDURECREATE PROCEDUREDEBUG ANY PROCEDURE6 rows selected.SQL> grant CREATE PROCEDURE to urp;Grant succeeded.SQL> connect urp/urpConnected.SQL> CREATE OR REPLACE PROCEDURE TEST  2  AS  3  BEGIN  4   NULL;  5  END;  6  /Procedure created.SQL> exec test;PL/SQL procedure successfully completed.

触发器测试（继续遇到权限问题，同样方法解决）

SQL> alter table emp drop constraint pk_id;Table altered.SQL>  update emp set id=5003 ;2 rows updated.SQL> commit;Commit complete.SQL>CREATE OR REPLACE TRIGGER trg_del_emp_info  BEFORE DELETE  ON emp  FOR EACH ROW  DECLARE    -- local variables here  BEGIN    INSERT INTO emp1(id,username,dep)          VALUES(          seq_emp_id.NEXTVAL,          :OLD.username,          :OLD.dep);  END;Trigger created.SQL> select * from emp;        ID USERNAME                       DEP---------- ------------------------------ ------------------------------      5003 tom                            hr      5003 john                           systemSQL> select * from emp1;no rows selectedSQL>  delete from emp where id=5003;2 rows deleted.SQL> commit;Commit complete.SQL> select * from emp1;        ID USERNAME                       DEP---------- ------------------------------ ------------------------------      5005 tom                            hr      5006 john                           systemSQL> 

.....

基本是这些了把
那由此见得我们日常使用还需要
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
....等等

现在我们看看urp所具有的权限

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
URP                            CREATE SESSION                           NO
URP                            UNLIMITED TABLESPACE                     NO
URP                            CREATE SEQUENCE                          NO
URP                            CREATE TRIGGER                           NO
URP                            CREATE PROCEDURE                         NO
URP                            CREATE TABLE                             NO

 

折腾了那么多为了就是说明用户<->权限方式对oracle的管理非常麻烦

oracle还有一个角色管理可以方便的授权，角色是一组权限的集合（也可以是一组角色的组合，也就是角色可以有包含关系）

比如：

用户--角色1_____角色2
            |___权限1
 

给用户授予角色1这个role，用户就同时拥有了角色2及权限1的权限属性

之前看了很多文档，昏昏的，我自己觉得很抽象,一些常用的其他操作

出处：http://czmmiao.iteye.com/blog/1304934

查询用户拥有哪里权限：SQL> select * from dba_role_privs;SQL> select * from dba_sys_privs;SQL> select * from role_sys_privs;查自己拥有哪些系统权限SQL> select * from session_privs;删除用户SQL> drop user 用户名 cascade;  //加上cascade则将用户连同其创建的东西全部删除系统权限传递：增加WITH ADMIN OPTION选项，则得到的权限可以传递。SQL> grant connect, resorce to user50 with admin option;  //可以传递所获权限。系统权限回收：系统权限只能由DBA用户回收SQL> Revoke connect, resource from user50;

　　

查询用户拥有哪里权限：SQL> select * from dba_role_privs;SQL> select * from dba_sys_privs;SQL> select * from role_sys_privs;查自己拥有哪些系统权限SQL> select * from session_privs;删除用户SQL> drop user 用户名 cascade;  //加上cascade则将用户连同其创建的东西全部删除系统权限传递：增加WITH ADMIN OPTION选项，则得到的权限可以传递。SQL> grant connect, resorce to user50 with admin option;  //可以传递所获权限。系统权限回收：系统权限只能由DBA用户回收SQL> Revoke connect, resource from user50;

继续上个图，其实几个关于用户、权限、角色的表只是分布在了dba字典表，user字典表

role相关的管理角色，sys相关的管理系统权限

总结如图...往上，往上，回去上图看看用户、角色、权限的关系
关于oracle用户、角色、权限还有很多知识点，有时间再写写

完全原创，如有转载希望保留出处及作者

by cycsa