本文通过实践演示了Oracle数据库中用户、角色与权限的具体管理方法，包括创建用户、分配不同类型的权限、进行常见数据库操作等。
用了ORACLE很久，但对用户、角色、权限还是很模糊，认真看书，查资料整理文档，做下记录，希望从日常使用的角度去看ORACLE的用户、角色与权限
先放一张图，可以跳过图，读完文章再回头看看图
说明：双箭头表示用户与角色查那张表，单虚线箭头表示包含关系，角色除了系统自带的dba_roles里定义的以外还可以自己创建定义
连接Oracle 数据库
$sqlplus / as sysdba
启动数据库
SQL>startup
创建一个表空间，命名为ts_urp指定空间为100M
SQL>create tablespace TS_URP datafile 'f:\ts_urp\data.dbf' size 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/urp
ERROR:
ORA-01045: user URP lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.提示用户不具有CREATE SESSION权限，查看下此时用于与权限对应表，grantee为URP的记录为空
SQL> connect / as sysdba
Connected.
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/urp
Connected.赋予权限后连接成功
创建表试试
SQL> create table emp(id number,username varchar(30));
create table emp(id number,username varchar(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 TABLESPACE
CREATE TABLE
ALTER ANY TABLE
DROP TABLESPACE
UNDER ANY TABLE
COMMENT ANY TABLE
MANAGE TABLESPACE
UPDATE ANY TABLE
DELETE ANY TABLE
BACKUP ANY TABLE
CREATE ANY TABLE
PRIVILEGE
----------------------------------------
DROP ANY TABLE
FLASHBACK ANY TABLE
INSERT ANY TABLE
CREATE TABLESPACE
LOCK ANY TABLE
SELECT ANY TABLE
ALTER TABLESPACE
18 rows selected.给个权限后再建表看看
SQL> grant CREATE TABLE to urp;
Grant succeeded.
SQL> connect urp/urp
Connected.
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 TABLESPACE
DROP TABLESPACE
MANAGE TABLESPACE
CREATE TABLESPACE
ALTER TABLESPACE看起来只能是UNLIMITED TABLESPACE，赋权看看
SQL> connect / as sysdba
Connected.
SQL> grant UNLIMITED TABLESPACE to urp;
Grant succeeded.
SQL> connect urp/urp
Connected.
SQL> create table emp(id number,username varchar(30));
Table created.看起来成功了
也就是说如果一个用户需要连接数据库并且创建表必须有三个权限
CREATE SESSION –连接数据库权限
UNLIMITED TABLESPACE –表空间无限制权限（空间配额）
CREATE TABLE –建立数据表权限
继续测试
有了数据表，我们插入、修改、删除等基本权限：
SQL> connect urp/urp
Connected.
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 urp1
SQL> 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 SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE 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/urp
Connected.
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
----------
5000
SQL> select seq_emp_id.nextval from dual;
NEXTVAL
----------
5001
SQL> select seq_emp_id.currval from dual;
CURRVAL
----------
5001
SQL> 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 system
SQL>存储过程测试（一样遇到问题，赋予权限后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 privileges
SQL> connect / as sysdba
Connected.
SQL> select privilege from dba_sys_privs where privilege like '%PROCEDURE%' group by privilege;
PRIVILEGE
----------------------------------------
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
ALTER ANY PROCEDURE
CREATE ANY PROCEDURE
CREATE PROCEDURE
DEBUG ANY PROCEDURE
6 rows selected.
SQL> grant CREATE PROCEDURE to urp;
Grant succeeded.
SQL> connect urp/urp
Connected.
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 system
SQL> select * from emp1;
no rows selected
SQL> 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 system
SQL> ……
基本是这些了把
那由此见得我们日常使用还需要
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相关的管理系统权限
学习记录.很多知识点有时间在写写吵吵~哈哈
学习素材来源
http://www.cnblogs.com/cycsa/archive/2013/05/21/3091199.html
扫一扫
363
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
