oracle对象权限.docx

对象权限：授权grant 对象权限 on 表名 to 用户名 with grant option;撤销权限：revoke 对象权限 on 表名 from 用户名;select * from tab;查询当前登录用户自己的表对象权限，如果把中间的一个用户的权限收回，那么这个用户传递下去的权限一并收回。了解：如何授权表的某一列权限update，insertdesc emp;查询表结构角色如何创建一个自定义角色create role 角色名称 identified by 密码;在给用户授予自定义角色时，如果把这个自定义角色给删除，那么这个角色授予的用户权限也删除。角色如何创建一个自定义角色create role角色名identified by 密码SQL create role u11;角色已创建。SQL conn u1/123已连接。SQL conn system/123456已连接。SQL revoke connect from u1;撤销成功。SQL conn u1/123ERROR:ORA-01045: user U1 lacks CREATE SESSION privilege; logon denied警告: 您不再连接到 ORACLE。SQL conn system/123456已连接。SQL grant create session to u11 ;授权成功。SQL show userUSER 为 SYSTEMSQL grant u11 to u1;授权成功。SQL conn u1/123已连接。SQL conn system/123456已连接。SQL drop role u11;角色已删除。SQLSQL conn / as sysdba;已连接。SQL drop user scott cascade;用户已删除。SQL create user SCOTT identified by TIGER;用户已创建。SQL grant connect,resource to SCOTT;SQL revoke connect from SCOTT;撤销成功。SQL conn SCOTT/TIGER;ERROR:ORA-01045: user SCOTT lacks CREATE SESSION privilege; logon denied警告: 您不再连接到 ORACLE。SQL grant connect to SCOTT;SP2-0640: 未连接SQL conn / as sysdba;已连接。SQL grant connect to SCOTT;授权成功。SQL conn SCOTT/TIGER;已连接。SQL show userUSER 为 SCOTTSQL grant connect to u1;grant connect to u1*第 1 行出现错误:ORA-01932: ADMIN 选项未授权给角色 CONNECTSQL conn / as sysdba;已连接。SQL grant connect to SCOTT with admin option;授权成功。SQL conn SCOTT/TIGER;已连接。SQL grant connect to u1;授权成功。SQL grant connect to u1 with admin option;授权成功。SQL create user u2 identified by 123;用户已创建。SQL conn u1/123;已连接。SQL grant connect to u2;授权成功。SQL conn / as sysdba;已连接。SQL revoke connect from u1;撤销成功。SQL conn u2/123;已连接。SQL conn SCOTT/TIGER;已连接。SQL select * from tab;未选定行SQL conn / as sysdba;已连接。SQL drop user SCOTT;用户已删除。SQL C:UsersacerDesktop实训第二次课scott.sqlSQL SELECT * from tab;TNAME TABTYPE CLUSTERID- - -BONUS TABLEDEPT TABLEEMP TABLESALGRADE TABLESQL select * from emp;SQL conn u2/123已连接。SQLSQL select * from tab;未选定行SQL select * from emp;select * from emp *第 1 行出现错误:ORA-00942: 表或视图不存在SQLSQL show userUSER 为 U2SQL select * from scott.emp;select * from scott.emp *第 1 行出现错误:ORA-00942: 表或视图不存在SQL conn SCOTT/TIGER;已连接。SQL select * from tab;TNAME TABTYPE CLUSTERID- - -BONUS TABLEDEPT TABLEEMP TABLESALGRADE TABLESQL select * from emp;SQL grant select on emp to u1;授权成功。SQL grant select on emp to u1 with grant option;授权成功。SQL grant create session to u1 ;grant create session to u1*第 1 行出现错误:ORA-01031: 权限不足SQL conn/ as sysdba已连接。SQL grant create session to u1 ;授权成功。SQL conn u1/123已连接。SQL select * from scott.emp;SQL grant select on emp to u1;授权成功。SQL grant select on emp to u1 with grant option;授权成功。SQL grant create session to u1 ;grant create session to u1*第 1 行出现错误:ORA-01031: 权限不足SQL conn/ as sysdba已连接。SQL grant create session to u1 ;授权成功。SQL conn u1/123已连接。SQL select * from scott.emp;SQL conn/ as sysdba已连接。SQL grant create session to u2;授权成功。SQL conn u2/123;已连接。SQL select * from scott.emp;select * from scott.emp *第 1 行出现错误:ORA-00942: 表或视图不存在SQL conn u1/123;已连接。SQL grant select on scott.emp to u2;授权成功。SQL conn u2/123;已连接。SQL select * from scott.emp;SQL conn SCOTT/TIGER已连接。SQL revoke select on scott.emp from u1;撤销成功。SQL conn u1/123;已连接。SQL select * from scott.emp;select * from scott.emp *第 1 行出现错误:ORA-00942: 表或视图不存在SQL conn u2/123;已连接。SQL select * from scott.emp;select * from scott.emp *第 1 行出现错误:ORA-00942: 表或视图不存在SQL show user;USER 为 U2SQL conn SCOTT/TIGER已连接。SQL grant select,update(empno,ename) on emp to u1;授权成功。SQL conn u1/123已连接。SQL select * from scott.emp;SQL update scott.emp set empno=123 where empno=7934;已更新 1 行。SQL select * from scott.emp;SQL update scott.emp set empno=111,ename=111 where empno=123;已更新 1 行。SQL select * from scott.emp;SQL conn SCOTT/TIGER已连接。SQL grant delete(empno) on emp to u1;grant delete(empno) on emp to u1 *第 1 行出现错误:ORA-00969: 缺失 ON 关键字SQL grant delete on emp to u1;授权成功。SQL revoke update(empno,ename) on emp from u1;revoke update(empno,ename) on emp from u1 *第 1 行出现错误:ORA-01750: UPDATE/REFERENCES 只能从整个表而不能按列 REVOKESQL revoke update on emp from u1;撤销成功。SQL update scott.emp set empno=123 where empno=111;update scott.emp set empno=123 where empno=111 *第 1 行出现错误:ORA-01031: 权限不足SQL conn / as sysdba已连接。SQL select * from dba_roles;ROLE PASSWORD AUTHENTICAT- - -CONNECT NO NONERESOURCE NO NONEDBA NO NONESQL create role u11;角色已创建。SQL conn u1/123;已连接。SQL conn/ as sysdba;已连接。SQL revoke create session from u1;撤销成功。SQL conn u1/123;ERROR:ORA-01045: user U1 lacks CREATE SESSION privilege; logon denied警告: 您不再连接到 ORACLE。SQL conn/ as sysdba;已连接。SQL grant create sess