Oracle用户+角色+权限

Oracle用户分为两大类：系统创建用户和手动创建用户；

Oracle角色分为两大类：系统创建角色和手动创建角色；
Oracle权限主要分为系统权限和对象权限，其中对象权限又分为表/视图级别对象权限和列级对象权限；

--系统用户查询所有select * from dba_users;        --用户列表select * from dba_roles;        --角色列表（独有）select * from dba_role_privs;   --所有用户角色权限select * from dba_sys_privs;    --所有用户和角色被赋于的系统权限select * from dba_tab_privs;    --所有用户和角色被赋于的表/视图级对象权限select * from dba_col_privs;    --所有用户和角色被赋于的列级对象权限--系统角色相关权限select * from role_tab_privs;   --角色对象权限select * from role_sys_privs ;  --角色系统权限select * from role_role_privs;  --角色与角色权限--当前登录用户查询用户select * from user_users ;      --当前用户select * from user_role_privs;  --当前用户角色权限select * from user_sys_privs;   --当前用户系统权限select * from user_tab_privs;   --当前用户表/视图级对象权限select * from user_col_privs;   --当前用户列级对象权限

Grant用法大全

Grant 表格操作权限

GRANT privilege-type ON { table-Name | view-Name } TO grantees;其中：privilege-typesALL PRIVILEGES |privilege-listprivilege-listtable-privilege {, table-privilege }*table-privilegeDELETE |INSERT |REFERENCES [column list] |SELECT [column list] |TRIGGER |UPDATE [column list]column list( column-identifier {, column-identifier}* )grantees表示被授权用户或角色名称列表,多个以英文逗号隔开,以下类似。Example:GRANT update(a1) on test.a8 to lis;GRANT SELECT ON  test.a8 to PUBLIC;GRANT UPDATE ON test.a8 TO PUBLIC,lis ;

Grant 执行权限

GRANT EXECUTE ON  routine-designator TO grantees;其中：routine-designator表示函数或存储过程名Example:GRANT EXECUTE ON p TO george ;

Grant 使用序列权限(11G测试失败)

GRANT USE ON SEQUENCE [ schemaName. ] SQL92Identifier TO grantees;Example:GRANT USAGE ON SEQUENCE order_id TO sales_role;

Grant 使用自定义类型权限(11G测试失败)

GRANT USAGE ON TYPE [ schemaName. ] SQL92Identifier TO grantees;Example:GRANT USAGE ON TYPE price TO finance_role;

Grant 角色权限

GRANT roleName [ {, roleName }* ] TO grantees;Example:GRANT purchases_reader_role TO george,maria;

Revoke用法大全

Revoke 表格操作权限

REVOKE  privilege-type ON { table-Name | view-Name } FROM  grantees;其中：privilege-typesALL PRIVILEGES |privilege-listprivilege-listtable-privilege {, table-privilege }*table-privilegeDELETE |INSERT |REFERENCES [column list] |SELECT [column list] |TRIGGER |UPDATE [column list]column list( column-identifier {, column-identifier}* )grantees表示被授权用户或角色名称列表,多个以英文逗号隔开,以下类似。Example:REVOKE update(a1) on test.a8 FROM  lis;REVOKE SELECT ON  test.a8 FROM PUBLIC;REVOKE UPDATE ON test.a8 FROM PUBLIC,lis ;

Revoke 执行权限

REVOKE EXECUTE ON  routine-designator FROM  grantees RESTRICT;其中：routine-designator表示函数或存储过程名Example:REVOKE EXECUTE ON p FROM  george ;

Revoke 使用序列权限(11G测试失败)

REVOKE USAGE ON SEQUENCE [ schemaName. ] SQL92Identifier FROM  grantees RESTRICT;Example:REVOKE USAGE ON SEQUENCE order_id FROM  sales_role;

Revoke 使用自定义类型权限(11G测试失败)

REVOKE USAGE ON TYPE [ schemaName. ] SQL92Identifier FROM  grantees RESTRICT;Example:REVOKE USAGE ON TYPE price FROM  finance_role;

Revoke 角色权限

REVOKE roleName [ {, roleName }* ] FROM  grantees;Example:REVOKE purchases_reader_role FROM  george,maria;