Oracle 基础篇 --- 用户权限

2019独角兽企业重金招聘Python工程师标准>>>

##第二. 用户权限

###2.1 权限

• 系统：每种系统权限都允许用户执行一个特定的数据库操作或一类数据库操作。例如，创建表空间的权限就是一种系统权限。系统权限可由管理员授予，或者由被显式授权其管理权限的用户授予。
• 对象：用户可以使用对象权限对特定对象（如表、视图、序列、过程、函数或程序包）执行特定的操作。在没有特定权限的情况下，用户只能访问他们自己拥有的对象。对象权限可以由对象的所有者或管理员授予，也可以由被显式授予了权限，可以为其他人员分配对某个对象的权限的人员授予。

---

###2.2 系统权限

语法：

GRANT <system_privilege> TO <grantee clause> [WITH ADMIN OPTION]

####2.2.1 TABLES:

• CREATE TABLE: Create a table in the grantee's schema.

SQL> grant create table to jeff;

Grant succeeded.

SQL> create table test01(id number(2), name varchar(10));

Table created.

SQL> create table hr.test01(id number(2), name varchar(10));
create table hr.test01(id number(2), name varchar(10))
*
ERROR at line 1:
ORA-01031: insufficient privileges

• CREATE ANY TABLE: Create a table in any schema. The owner of the schema containing the table must have space quota on the tablespace to contain the table.

SQL> grant create any table to jeff;

Grant succeeded.

SQL> create table hr.test01(id number(2), name varchar(10));

Table created.

• DROP ANY TABLE: Drop or truncate tables or table partitions in any schema.

SQL> grant drop any table to jeff;

Grant succeeded.

SQL> drop table hr.test01;

Table dropped.

---

####2.2.2 SESSION

• CREATE SESSION: Connect to the database.

[oracle@hzvscmdb ~]$ sqlplus jeff/pass

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 23 08:05:15 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01045: user JEFF lacks CREATE SESSION privilege; logon denied


Enter user-name:

SQL> grant create session to jeff;

Grant succeeded.

####2.2.3 TABLESPACE

• UNLIMITED TABLESPACE: Use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If you revoke this privilege from a user, then the user's schema objects remain but further tablespace allocation is denied unless authorized by specific tablespace quotas. You cannot grant this system privilege to roles.

SQL> grant unlimited tablespace to jeff;

---

###2.3 对象权限

语法：

GRANT <object_privilege> ON <object> TO <grantee clause> [WITH GRANT OPTION]

####2.3.1 DIRECTORY PRIVILEGES

• READ
• WRITE
• EXECUTE

create directory DATABAK as '/dbbak';
grant read, write on directory DATABAK to public;

####2.3.2 OLAP PRIVILEGES

• INSERT
• ALTER
• DELETE
• SELECT
• UPDATE

GRANT SELECT ON SYS.DUAL_VIEW TO TEST;
GRANT DELETE, INSERT, SELECT, UPDATE ON TEST.WBXCLIENTTYPE TO SPLEX2112

转载于:https://my.oschina.net/wangbinbin0326/blog/470809