Oracle用户角色权限等操作

Oracle用户角色权限等操作
oracle 赋予权限
转发评论
02月21日13:00
最基础的sqlplus username/password@[sid] 连接数据库
sqlplus username/password as sysdba 以管理员连接数据库
管理员连接后创建用户sqlplus system/manager@demo as sysdba create user abc identified by abc ; 创建用户abc密码为abc grant all privileges to abc; 给abc用户赋予所有的权限
给自己留个备份以备不时之需
oracle grant
授权语句--select * from dba_users; 查询数据库中的所有用户
--alter user USERNAME account lock; 锁住用户
--alter user USERNAME account unlock; 给用户解锁
--create user USERNAME identified by USERPASS; 建立用户一般创建用户后需要授予链接数据库权限
grant connect,resource to USERNAME;
--grant create tablespace to USERNAME; 授权创建表空间
--grant SELECT on TABLENAME to USERNAME; 授权查询
授权其他动作格式相同
如果要把所有表的查询权限分配给用户可以用这样的
grant select any table to USERNAME;
--grant execute on procedure1 to xujin 授权存储过程
--grant UPDATE on TABLENAME to USERNAME with grant option; 授权更新权限转移给xujin用户，许进用户可以继续授权；
--收回权限
--revoke select on table1 from xujin1; 收回查询select表的权限；
--revoke all on table1 from xujin;
/*grant connect to xujin;
revoke connect from xujin
grant select on xezf.cfg_alarm to xujin;
revoke select on xezf.cfg_alarm from xujin;*/
--select table_name,privilege from dba_tab_privs where grantee='xujin' 查询一个用户拥有的对象权限
--select * from dba_sys_privs where grantee='xujin' 查询一个用户拥有的系统权限
--select * from session_privs --当钱会话有效的系统权限
--角色
--create role xujin1;--建立xujin1角色
--grant insert on xezf.cfg_alarm to xujin1; 将插入表的信息
--revoke insert on xezf.cfg_alarm from xujin1; 收回xujin1角色的权限
--grant xujin1 to xujin ; 将角色的权限授权给xujin;
-- create role xujin2;
--grant xujin1 to xujin2; 将角色xujin1授权给xujin2;
--alter user xujin default xujin1,xujin2; 修改用户默认角色
-- DROP ROLE xujin1;删除角色1；
--select * from role_sys_privs where role=xujin1;
--查看许进1角色下有什么系统权限；
--select granted_role,admin_option from role_role_privs where role='xujin2'
--查看xujin1角色下面有什么角色权限
--select * from role_sys_privs where role='xujin2'
--select table_name,privilege from role_tab_privs where role='xujin1'
--select * from dba_role_privs where grantee='xujin' --查看用户下面有多少个角色；
======================详解
=========================
GRANT
名称GRANT -- 赋予访问权限
语法GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
ON SEQUENCE sequencename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE langname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schemaname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespacename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT role [, ...] TO username [, ...] [ WITH ADMIN OPTION ] 描述
GRANT命令有两个基本变种：一个变种是给数据库对象(表、视图、序列、数据库、函数、过程语言、模式、表空间)赋予权限；一个变种是赋予一个角色中的成员关系。

这些变种在很多方面都非常类似，但是它们之间的区别也有足够理由来分开描述。

到了PostgreSQL 8.1，用户和组的概念已经统一成一类对象，叫角色。

因此就没有必要使用关键字GROUP来标识一个授予者是用户还是组。

GROUP仍然可以在命令中使用，但只是一个多余的词而已。

在数据库对象上的GRANT
这个变种的GRANT命令在数据库对象上给一个或多个角色授予特定的权限。

这些权限追加到已经授予的权限上。

关键字PUBLIC表示该权限要赋予所有角色，包括那些以后可能创建的用户。

PUBLIC可以看做是一个隐含定义好的组，它总是包括所有角色。

任何特定的角色都将拥有直接赋予他/它的权限，加上他/它所处的任何组，以及再加上赋予PUBLIC的权限的总和。

如果声明了WITH GRANT OPTION，那么权限的接收者也可以将此权限赋予他人，否则就不能授权他人。

这个选项不能赋予PUBLIC 对于对象的所有者(通常就是创建者)而言，没有什么权限需要赋予，因为所有者缺省就持有所有权限。

不过，所有者出于安全考虑可以选择废弃一些他自己的权限。

删除一个对象的权力，或者是任意修改它的权力都不是可赋予的权限；它是创建者固有的，并且不能赋予或撤销。

所有者也隐含地拥有该对象的所有授权选项。

根据对象的不同，初始的缺省权限包括一些赋予PUBLIC的权限：对于表、模式、表空间没有公开访问权限；对于数据库有CONNECT 权限和创建TEMP表的权限；对于函数有EXECUTE权限；对于语言有
USAGE权限。

对象所有者当然可以撤回这些权限。

出于最大安全性考虑，在创建该对象的同一个事务中发出REVOKE就不会打开给别的用户使用该对象的窗口。

可能的权限有：
SELECT
允许对声明的表、试图、序列SELECT任意字段。

还允许做COPY TO 的源。

对于序列而言，这个权限还允许使用de>currvalde> 函数。

INSERT
允许向声明的表INSERT一个新行。

同时还允许做COPY FROM
UPDATE
允许对声明的表中任意字段做UPDATE。

SELECT ... FOR UPDATE 和SELECT ... FOR SHARE也要求这个权限(除了SELECT权限之外)。

比如，这个权限允许使用
de>nextvalde> 和de>setvalde> 函数。

DELETE
允许从声明的表中DELETE行
REFERENCES
要创建一个外键约束，你必须在参考表和被参考表上都拥有这个权限。

TRIGGER
允许在声明表上创建触发器(参见CREATE TRIGGER语句)
CREATE
对于数据库，允许在该数据库里创建新的模式。

对于模式，允许在该模式中创建新的对象。

要重命名一个现有对象，你必需拥有该对象并且对包含该对象的模式拥有这个权限。

对于表空间，允许在其中创建表，以及允许创建数据库和模式的时候把该表空间指定为其缺省表空间。

请注意，撤销这个权限不会改变现有数据库和模式的存放位置。

CONNECT
允许用户连接到指定的数据库。

该权限将在连接启动时检查(除了
检查pg_hba.conf中的任何限制之外)。

TEMPORARY
TEMP
允许在使用该数据库的时候创建临时表
EXECUTE
允许使用指定的函数并且可以使用任何利用这些函数实现的操作符。

这是适用于函数的唯一权限。

该语法同样适用于聚集函数。

USAGE
对于过程语言，允许使用指定过程语言创建该语言的函数。

这是适用于过程语言的唯一权限。

对于模式，允许访问包含在指定模式中的对象(假设该对象的所有权要求同样也设置了)。

最终这些就允许了权限接受者"查询"模式中的对象。

没有这个权限仍然可以看见这些对象的名字(比如通过查询系统视图)。

同样，撤销该权限之后，现有的后端可能有在查找之前就执行了的语句，因此这不是一个很安全的限制对象访问的方法。

对于序列，该权限允许使用de>currvalde> 和de>nextvalde> 函数。

ALL PRIVILEGES
一次性给予所有可以赋予的权限。

PRIVILEGES关键字在PostgreSQL 里是可选的，但是严格的SQL 要求有这个关键字。

其它命令要求的权限都在相应的命令的参考页上列出。

角色上的GRANT
这个变种的GRANT命令把一个角色的成员关系赋予一个或多个其它角色。

角色里的成员关系很重要，因为它会将赋予该角色的权限传播给所有该角色的成员。

如果声明了WITH ADMIN OPTION，那么该成员随后就可以将角色的成员关系赋予其它角色，以及撤销其它角色的成员关系。

如果没有admin 选项，普通用户就不能这么做。

不过，数据库超级用户可以给任何人赋与或者撤销任何角色的任何成员关系。

拥有CREATEROLE 权限的角色可以赋予或者撤销任何非超级用户角色的成员关系。

与权限不同，角色的成员关系不能被赋予PUBLIC。

需要注意的是，这种形式的命令不允许使用无意义的GROUP关键字。

注意
REVOKE命令用于删除访问权限。

如果非对象所有者企图在对象上GRANT权限，而该用户没有该对象上指定的权限，那么命令将立即失败。

只要有某些可用的权限，该命令就会继续，但是它只授予那些该用户有授权选项的权限。

如果没有可用的授权选项，那么GRANT ALL PRIVILEGES形式将发出一个警告信息，其它命令形式将发出在命令中提到的、但是没有授权选项的那些权限相关的警告信息。

这些语句原则上也适用于对象所有者，但是因为所有者总是被认为拥有所有授权选项，所以这种情况永远不会发生在所有者身上。

要注意数据库超级用户可以访问所有对象，而不会受对象的权限设置影响。

这个特点类似Unix 系统的root的权限。

和root一样，除了必要的情况，总是以超级用户身份进行操作是不明智的做法。

如果一个超级用户选择发出一个GRANT或REVOKE命令，那么这条命令将是以被影响对象的所有者的形式执行的。

特别是，通过这种方法赋与的权限将显得好像是由对象所有者赋与的。

对于角色成员关系，成员关系的赋与就会像是通过包含角色自己赋与的一样。

GRANT和REVOKE也可以不由被影响对象的所有者来执行，而是由拥有该对象的角色的一个成员来执行，或者是一个在该对象上持有WITH GRANT OPTION权限的角色的成员。

在这种情况下，该权限将被纪录为是由实际拥有该对象或者持有WITH GRANT OPTION 权限的对象赋与的。

比如，如果表t1被角色g1拥有，并且u1是g1的一个成员，然后u1可以把t1的权限赋予u2，但是这些权限将表现为是由g1直接赋予的。

任何g1角色的成员都可以在之后撤销这些权限。

如果执行GRANT的角色所持有的所需权限是通过角色成员关系间接获得的，那么究竟是那个角色将被纪录为赋予权限的角色就是未知的。

在这种情况下，最好的方法是使用SET ROLE成为你想执行
GRANT命令的指定角色。

在表上赋予的权限不会自动传播到该表使用的序列上，包括SERIAL字段上的序列。

必须单独设置序列的权限。

目前，PostgreSQL 不支持给一个表的独立字段进行权限赋予和撤销的操作，一个绕开的办法是创建一个拥有那几行的视图然后给那个视图赋予权限。

使用psql 的\z命令获取在现有对象上的与权限有关的信息。

=> \z mytable
Access privileges for database "lusitania"
Schema | Name | Type | Access privileges
--------+---------+-------+-----------------------------------------------------------
public | mytable | table | {miriam=arwdxt/miriam,=r/miriam,"group todos=arw/miriam"}
(1 row)
\z显示的条目解释如下：
=xxxx -- 赋予PUBLIC 的权限
uname=xxxx -- 赋予一个用户的权限
group gname=xxxx -- 赋予一个组的权限
r -- SELECT ("读")
w -- UPDATE ("写")
a -- INSERT ("追加")
d -- DELETE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdxt -- ALL PRIVILEGES (用于表)
* -- 给前面权限的授权选项
/yyyy -- 授出这个权限的用户
用户miriam在建完mytable表之后再做下面的语句，就可以得到上面例子的结果
GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO GROUP todos;
如果一个给定的对象的"Access privileges字段是空的，这意味着该对象有缺省权限(也就是
说，它的权限字段是NULL)。

缺省权限总是包括所有者的所有权限，以及根据对象的不同，
可能包含一些给PUBLIC的权限。

对象上第一个GRANT或REVOKE将实例化这个缺省权限(比如，产生{miriam=arwdxt/miriam}) 然后根据每次特定的需求修改它。

请注意所有者的隐含授权选项没有在显示出来的访问权限里标记出来。

只有在授权选项明确地授予某人之后，才会显示一个* 例子
把表films的插入权限赋予所有用户：
GRANT INSERT ON films TO PUBLIC;
赋予用户manuel对视图kinds的所有权限：
GRANT ALL PRIVILEGES ON kinds TO manuel;
请注意，如果上面的命令由超级用户或者kinds的所有者执行，那么它实际上会赋予所有权限，如果由其他人执行，那么它会赋予这个"其他人"拥有授权选项的所有权限。

把角色admins的成员关系赋与用户joe：
GRANT admins TO joe;
兼容性
根据SQL 标准，在ALL PRIVILEGES里的PRIVILEGES关键字是必须的。

SQL 标准不支持在一条命令里对多个表设置权限。

PostgreSQL 允许一个对象所有者撤销它自己的普通权限：比如，一个表所有者可以让自己对这个表是只读的，方法是撤销自己的INSERT, UPDATE, DELETE 权限。

根据SQL 标准，这是不可能的。

原因是PostgreSQL 把所有者的权限当作由所有者给自己赋予的；因此也可以撤销他们。

在SQL 标准里，所有者的权限是假设为"_SYSTEM"实体赋予的。

因为所有者不是"_SYSTEM"，所以他不能撤销这些权限。

SQL 标准允许在一个表里为独立的字段设置权限：
GRANT privileges
ON table [ ( column [, ...] ) ] [, ...]
TO { PUBLIC | username [, ...] } [ WITH GRANT OPTION ]
SQL 标准对其它类型的对象提供了一个USAGE权限：字符集、校勘、转换、域。

在数据库、表空间、模式、语言、序列上的权限是PostgreSQL 扩展。

又见
REVOKE
INSERT
名称INSERT -- 在表中创建新行
语法INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ RETURNING * | output_expression [ AS output_name ] [, ...] ] 描述
INSERT向表中插入新行。

可以一次插入用值表达式声明的一行或者一个查询结果表现出来的零行或多个行。

目标列表中的列/字段可以按任何顺序排列。

如果完全没有列出任何字段名，那么缺省是全部字段，顺序是按照表声明的时候的顺序；如果VALUES子句或者query里面只提供了N 个字段，那么就是头N 个字段。

VALUES子句或者query提供的数值是以从左到右的方式与明确或者隐含的字段列表关联的。

每个没有在明确或者隐含的字段列表中出现的字段都将填充缺省值，如果有声明的缺省值则用声明的那个，如果没有则用NULL 。

如果每行的表达式不是正确的数据类型，系统将试图进行自动的类型转换。

可选的RETURNING子句将导致INSERT返回实际插入的行，它主要用于获取缺省的计算值(比如序列值)，不过，任何使用该表字段的表达式都是允许的。

RETURNING列表的语法都与SELECT的输出列表相同。

要想向表中插入数据，你必须有INSERT权限；要使用RETURNING的话还得有SELECT 权限；如果你使用了query子句插入来自查询里的数据行，你还需要拥有在查询里使用的表的SELECT权限。

参数
table
现存表的名称(可以有模式修饰)
column
表table中的字段名。

必要时，字段名可以有子字段名或者数组下标修饰。

向一个复合类型中的某些字段插入数据的话，其它字段将是NULL 。

DEFAULT VALUES
所有字段都会用它们的缺省值填充
expression
赋予对应的column的一个有效表达式或值
DEFAULT
对应的column将被它的缺省值填充
query
一个查询(SELECT语句)，它提供插入的数据行。

请参考SELECT 语句获取语法描述。

output_expression
INSERT命令在每一行都被插入之后用于计算输出结果的表达式。

该表达式可以使用table 的任意字段。

可以使用*返回被插入行的所有
字段。

output_name
一个字段的输出名称
输出
成功完成后，一条INSERT命令返回一个下面形式的命令标签
INSERT oidcount
count是插入的行数。

如果count正好是一，并且目标表有OID ，那么oid是赋予插入行的OID 。

否则oid是零。

如果INSERT命令包含RETURNING子句，其结果将和一个SELECT语句相同，包含那些基于该命令实际插入的行计算的、定义在RETURNING列表中的字段的值。

例子
向表films里插入单独一行：
INSERT INTO films VALUES
('UA502', 'Bananas', 105, -07-13', 'Comedy', ? minutes');
在这个例子里面省略了字段len，因此在它里面将存储缺省值：
INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, -06-16', 'Drama');
在这个例子里，用DEFAULT子句作为日期字段，而不是声明一个数值：
INSERT INTO films VALUES
('UA502', 'Bananas', 105, DEFAULT, 'Comedy', ? minutes');
INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
插入一行完全由缺省值组成的数据行：
INSERT INTO films DEFAULT VALUES;
使用多行VALUES语法插入多行：
INSERT INTO films (code, title, did, date_prod, kind) VALUES ('B6717', 'Tampopo', 110, -02-10', 'Comedy'),
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
从表tmp_films中插入几行到表films中，字段布局与films相同：INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < -05-07'
插入数组：
-- 创建一个空的3x3游戏板来玩圈-和-叉游戏(这些查询创建相同的板属性)
INSERT INTO tictactoe (game, board[1:3][1:3])
VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
-- 上述例子中的下标并非真正必须
INSERT INTO tictactoe (game, board)
VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
向distributors表插入一个单独的行，并返回由DEFAULT子句生成的序列值：
INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') RETURNING did;
兼容性
INSERT语句与SQL 标准兼容。

但RETURNING子句是PostgreSQL 扩展。

同样，省
略字段名列表，但是并非所有字段都从VALUES子句或者query 填充的这种用法是标准不允许的。

可能碰到的关于query子句特性的限制在SELECT语句中有相关文档。

LISTEN
名称LISTEN -- 监听一个通知
语法LISTEN name
描述
LISTEN将当前会话注册为通知条件name的监听器。

如果当前会话已经被注册为该通知
条件的监听器，那么什么也不做。

当执行了命令NOTIFY name后，不管是此会话还是其它连接到
同一数据库的会话，所有
正在监听此通知条件的会话都将收到通知，并且接下来每个会话将通知与其相连的前端应用。

请参考NOTIFY获取更多信息。

使用UNLISTEN命令可以将一个会话内已注册的通知条件删除。

同样，会话退出时自动删除该会话正在监听的已注册通知条件。

前端应用检测通知事件的方法取决于PostgreSQL 应用使用的编程接口。

如果使用基本的libpq 库，那么应用将LISTEN当作普通SQL 命令使用，而且必须周期地调用
de>PQnotifiesde> 过程来检测是否有通知到达。

其它像libpgtcl 接口提供了更高级的控制通知事件的方法；实际上，使用libpgtcl 的应用程序员不应该直接使用LISTEN或UNLISTEN。

请参考你使用的接口的文档获取更多细节。

NOTIFY的手册页包含更广泛的关于LISTEN和NOTIFY的使用的讨论。

参数
name
通知条件的名字，可以是任意标识符。

例子
在psql 里配制和执行一个监听/通知序列：
LISTEN virtual;
NOTIFY virtual;
Asynchronous notification "virtual" received from server process with PID 8448.
兼容性SQL 标准里没有LISTEN语句
分类：Oracle学习。