oracle的权限

对象权限

书上说， owner grant 对象权限1 给 A with grant option，然后A grant 给B。 A不能 revoke from B。只能ower(或者有grant any objectprivilege的user) 去 rovke from B。但是我在11g,12c里面测试，结论如下

对象权限，只能grant出去的的人去回收，就算owner或者sys都回收不回来，会报【ORA-01927: 付与していない権限にはREVOKEを実行できません。】错误，上面的B的权限只能A去revoke。当然，owner可以通过级联来回收B的权限，比如上面的owner收回A的，B的也就没了。

特殊的情况是，sys可以revoke owner分发出去的权限，比如上面sys可以回收A的权限，但是不能回收由A分发出去的B的权限。

真的是比较奇怪。

audit by session & by access

11g和12c里面，对ddl只能进行by access了，执行by session会32595错。
但是，仍然可以对权限进行by session，
比如 audit select any table by zyz by session，不会报错。可是它的效果仍然是by access（一个session会产生多条记录）。 这和以前的版本是完全一些样，语句通过了，效果仍然是by access。

目前，在orcale9的文档里才有这个说明，说ddl的只能用by access。

http://docs.oracle.com/cd/B10500_01/server.920/a96524/c25audit.htm

The AUDIT statement lets you specify either BY SESSION or BY ACCESS. However, several audit options can be set only BY ACCESS, including:

• All statement audit options that audit DDL statements
• All privilege audit options that audit DDL statements

For all other audit options, BY SESSION is used by default.

11g
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4007.htm#SQLRF53733
12c
http://docs.oracle.com/database/121/SQLRF/statements_4007.htm#SQLRF55571
BY SESSION
In earlier releases, BY SESSION caused the database to write a single record for all SQL statements or operations of the same type executed on the same schema objects in the same session. Beginning with this release of Oracle Database, both BY SESSION and BY ACCESS cause Oracle Database to write one audit record for each audited statement and operation. BY SESSION continues to populate different values to the audit trail compared with BY ACCESS. Oracle recommends that you include the BY ACCESS clause for all AUDIT statements, which results in a more detailed audit record. If you specify neither clause, then BY ACCESS is the default.

Note:

This change applies only to schema object audit options, statement options and system privileges that audit SQL statements other than data definition language (DDL) statements. The database has always audited BY ACCESS all SQL statements and system privileges that audit a DDL statement.

BY ACCESS

Specify BY ACCESS if you want Oracle Database to write one record for each audited statement and operation.

Note:

If you specify either a SQL statement shortcut or a system privilege that audits a data definition language (DDL) statement, then the database always audits by access. In all other cases, the database honors the BY SESSION or BY ACCESS specification.

For statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY ACCESS is the default.

就算给了用户connect的role，仍然连接不上ORA-01045

10g以后的connect role里面就只有一个CREATE SESSION，照理如果给了用户这个role，就能连接不上，后来知道光
GRANT "CONNECT" TO user
不行
还得将这个role设置为user对的default role，也就是

alter user user default role connect

至于为啥要弄成deafult role，得看oracle的文档了

用户虽然有这个role，但是没enable，需要切换到这个role才能行使这个role的权限。

http://dba-tips.blogspot.jp/2012/03/about-default-roles.html