Oracle用户与对象权限与系统权限

1、1用户与模式用户：对数据库的访问，需要以适当用户身份通过验证，并具有相关权限来完成一系列动作SYS用户，缺省始终创建，且未被锁定，拥有数据字典及其关联的所有对象SYSTEM用户，缺省始终创建，且未被锁定，可以访问数据库内的所有对象模式(schema):是某个用户拥有所有对象的集合。具有创建对象权限并创建了对象的用户称为拥有某个模式注意：创建数据库对象(视图,表等)的任一用户都拥有一个以该用户名称开头的模式，且被视为模式用户2创建及修改用户条件：需要具有创建用户的权限，如sys,system,sysdba,dba role 等语法：CREATE USER userIDENTIFIED BY pa

2、ssword | EXTERNALLY | GLOBALLY AS external name DEFAULT TABLESPACE tablespace_ nameTEMPORARY TABLESPACE tablespace_nameQUOTA n K|M | UNLIMITED ON tablespace_ nameQUOTA n k|M | UNLIMITED ON tablespace_name .PASSWORD EXPIREACCOUNT LOCK | UNLOCK PROFILE profile, name | DEFAULT eg:CREATE USER robi nson

3、IDENTIFIED BY tiger;-省略了 DEFAULT TABLESPACE 和 TEMPORARY TABLESPACE 时，贝U 由 database_properties中对应的参数确定SQL> SELECT property, name,property_value FROMdatabase_propertiesWHERE property, name LIKE 'DEFAULT%'PROPERTY_NAMEPROPERTY_VALUEDEFAULT_TEMP_TABLESPACE TEMPDEFAULT_PERMANENT_TABLESPACE USE

4、RSDEFAULT_TBS_TYPESMALLFILE更多关于表空间的请参考：Oracle表空间与数据文件1. 修改用户修改用户的语法同创建用户，仅仅讲关键字create替换为alter，alter user可以修改除用户名之外的任一属性ALTER USER robin son ACCOUNT LOCK;2. 修改密码DBA可以创建用户和修改密码用户本人可以使用 ALTER USER语句修改密码SQL> ALTER robin son IDENTIFIED BY newpassword;3. 删除用户：DROP USER username CASCADECASECADE连同用户创建的对象

5、一并删除，如果该用户创建了 对象，要加CASCADE删除，否则删除不掉另外，不能删除当前正在与ORACLE服务器相连的用户。4. 改变用户在表空间上的配额：ALTER USER username QUOTA 0 ON system;ALTER USER scott QUOTA UNLIMITED ON USERS;ALTER USER dog QUOTA 30M ON system;5. 查看用户表空间配额(dba_ts_quotas):SQL> SELECT USERNAME,TABLESPACE_NAME,MAX_BYTES/1024/1024"Max MB"2

6、FROM dba_ts_quotas WHERE USERNAME='SCOTT：USERNAMETABLESPACE_NAME Max MBSCOTTSYSTEM306. 查看特定对象下用户所拥有的对象使用dba_objects 视图SQL> SELECT own er,object_ name, object_type FROM dba_objects WHEREown er= 'SCOTT'3 Oracle 权限系统权限：允许用户执行特定的数据库动作，如创建表、创建索引、连接实例等对象权限：允许用户操纵一些特定的对象，如读取视图，可更新某些列、执行存储过程3

7、.1系统权限超过一百多种有效的权限(SELECT * FROM SYSTEM_PRIVILEGE_MAP 查)数据库管理员具有高级权限以完成管理任务，例如：-创建新用户 -删除用户-删除表-备份表a常用的系统权限:CREATE SESSION创建会话CREATE SEQUENCE创建序列CREATE SYNONYM创建同名对象CREATE TABLE在用户模式中创建表CREATE ANY TABLE在任何模式中创建表DROP TABLE在用户模式中删除表DROP ANY TABLE在任何模式中删除表CREATE PROCEDURE创建存储过程EXECUTE ANY PROCEDURE执行任何模

8、式的存储过程CREATE USER创建用户DROP USER删除用户CREATE VIEW创建视图b.授予用户系统权限GRANT privilege , privilege. TO user , user| role, PUBLIC. WITH ADMIN OPTION;PUBLIC所有用户WITH ADMIN OPTION使用户同样具有分配权限的权利，可将此权限授予别人SQL> GRANT CREATE SESSION,CREATE TABLE,CREATE USER TO scott;SQL> GRANT EXECUTE ANY PROCEDURE TO scott WITH

9、ADMIN OPTION;SQL> CONN scott; -scott 具有 WITH ADMIN OPTION ，故可以将EXECUTE ANY PROCEDURE 授予 robinsonEn ter password:Conn ected.SQL> GRANT EXECUTE ANY PROCEDURE TO robi nson;Grant succeeded.SQL> GRANT EXECUTE ANY PROCEDURE TO PUBLIC;- 将 EXECUTE ANYPROCEDURE授予所有用户Grant succeeded.SQL> CONN syst

10、em/redhat;- 使用 system 为 robin son授予 CREATETABLE、CREATE SESSION 权限Conn ected.SQL> GRANT CREATE TABLE,CREATE SESSION TO robi nson;Grant succeeded.c.使用系统权限-使用robinson 具有创建会话、创建表SQL> CREATE TABLE tb1 AS SELECT * FROM USER_TABLES;-有权限在users表空间创建对象CREATE TABLE tb1 AS SELECT * FROM USER_TABLES*ERROR

11、at line 1:ORA-01950: no privileges on tablespace 'USERS'SQL> CONN sys as sysdba;- 使用 sys 帐户登陆并为 robinson间指定配额后可以创建表 tb1En ter password:Conn ected.SQL> ALTER USER robi nson QUOTA 10M ON USERS;User altered.SQL> CONN robi nso n/lion;Conn ected.F面提示没在users表空SQL> CREATE TABLE tb1 AS S

12、ELECT * FROM USER_TABLES;T able created.d.查看系统权限dba_sys_privs -user_sys_privs -针对所有用户被授予的系统权限针对当前登陆用户被授予的系统权限SQL> SELECT gran tee,privilege,admin_optio n FROM dba_sys_privs2 WHERE gran tee IN ('SCOTT','ROBINSON')3 ORDER BY gran tee;GRANTEEPRIVILEGEADMROBINSONCREATE SESSIONNOROBINS

13、ONCREATE TABLENOROBINSONEXECUTE ANY PROCEDURENOSCOTTCREATE PUBLIC SYNONYMNOSCOTTCREATE SESSIONNOSCOTTCREATE SYNONYMNOSCOTTCREATE TABLENOSCOTTCREATE USERNOSCOTTCREATE VIEWNOSCOTTEXECUTE ANY PROCEDUREYESSCOTTUNLIMITED TABLESPACENOe.回收系统权限REVOKE privilege | role FROM user_ name | role_ name | PUBLIC-下面

14、的示例中并没有回收掉原来由scott授予给robisnon EXECUTE ANYPROCEDURE的权限SQL> REVOKE EXECUTE ANY PROCEDURE FROM scott;Revoke succeeded.SQL> select gran tee,privilege,adm in _optio n from dba_sys_privs2 where gran tee in ('SCOTT','ROBINSON') and privilege = 'EXECUTEANY PROCEDURE'3 order by

15、gran tee;GRANTEEPRIVILEGEADMROBINSONEXECUTE ANY PROCEDURENO注意：对于使用 with admin option为某个用户授予系统权限，那么对于被这个用户授予相同权限的所有用户来说，取消该用户的系统权限并不会级联取消这些用户的相同权限3.2对象权限不同的对象具有不同的对象权限对象的拥有者拥有所有权限对象的拥有者可以向外分配权限ORACLE 一共有种对象权限对象权限表视图序列过程修改(alter)VV删除(delete)V V执行(execute)V索引（index）V插入(insert)V V关联(references)V V选择(sel

16、ect)V VV更新(update)V Va. 对象授权GRANT object_priv | ALL (columns)ON objectTO user|role|PUBLICWITH GRANT OPTION;ALL :所有对象权限PUBLIC :授给所有的用户WITH GRANT OPTION:允许用户再次给其它用户授权b. 授予系统权限与授予对象权限的语法差异：授予对象权限时需要指定关键字ON，从而能够确定权限所应用的对象。对于表和视图可以指定特定的列来授权。-对象授权示例SQL> SHOW USER;USER is "SCOTT"SQL> GRANT

17、SELECT ON emp TO robi nson;Grant succeeded.SQL> GRANT UPDATE(sal,mgr) ON emp TO robin son WITH GRANT OPTION;Grant succeeded.-新创建一个用户 joh n，使用robin son 账户授予更新 scott.emp(sal,mgr) 的权限SQL> CREATE USER joh n IDENTIFIED BY joh n;User created.SQL> GRANT CREATE SESSION TO joh n;Grant succeeded.SQL&

18、gt; CONN ROBINSON/LIONConn ected.SQL> GRANT UPDATE(sal,mgr) ON scott.emp TO joh n;-scott.emp(sal,mgr)的更新权限Grant succeeded.SQL> UPDATE scott.emp SET sal = sal + 100 WHERE en ame = 'SCOTT'-成功更新1 row updated.-向数据库中所有用户分配权限SQL> GRANT SELECT ON dept TO PUBLIC;Grant succeeded.c. 查询权限分配情况数

19、据字典视图描述ROLE_SYS_PRIVS角色拥有的系统权限ROLE_TAB_PRIVS角色拥有的对象权限USER_TAB_PRIVS_MADE查询授出去的对象权限（通常是属主自己查）USER_TAB_PRIVS_RECD用户拥有的对象权限USER_COL_PRIVS_MADE用户分配出去的列的对象权限USER_COL_PRIVS_RECD用户拥有的关于列的对象权限USER_SYS_PRIVS用户拥有的系统权限USER TAB PRIVS用户拥有的对象权限USER_ROLE_PRIVS用户拥有的角色-查询已授予的对象权限（即某个用户对哪些表对哪些用户开放了对象权限）F面是scott用户开放的对

20、象SQL> SELECT * FROM user_tab_privs_made;-权限PRIVILEGEGRANTEETABLE_NAMEGRANTORGRA HIEPUBLICDEPTSCOTTSELECTNONOROBINSONEMPSCOTTSELECTNO NO-查询列上开放的对象权限SQL> SELECT * FROM user_col_privs_made;GRANTEETABLE_NAMECOLUMN_NAMEGRANTORPRIVILEGE GRAROBINSONEMPSALSCOTTUPDATEYESJOHNEMPMGRROBINSONUPDATENOROBINS

21、ONEMPMGRSCOTTUPDATEYESJOHNEMPSALROBINSONUPDATENO-查询已接受的对象特权（即某个用户被授予了哪些表上的哪些对象特权）SQL> SELECT * FROM user_tab_privs_recd;OWNERTABLE_NAMEGRANTORPRIVILEGEGRA HIENOSCOTTEMPSCOTTSELECTNO-查询用户已接受列的对象权限SQL> SELECT * FROM user_col_privs_recd;OWNERTABLE_NAME COLUMN_NAME GRANTORPRIVILEGE GRASCOTTEMPYESS

22、COTTEMPYESd.收回对象权限使用REVOKE语句收回权限使用 WITH GRANT OPTIONMGRSCOTTUPDATESALSCOTTUPDATE子句所分配的权限同样被收回REVOKE privilege , privilege.|ALLON objectFROM user, user.|role|PUBLICCASCADE CONSTRAINTS;CASCADE CONSTRAINTS为处理引用完整性时需要-收回权限示例SQL> conn scott/tiger;Conn ected.SQL> REVOKE SELECT ON emp FROM robi nson;

23、Revoke succeeded.SQL> REVOKE UPDATE(sal,mgr) ON emp FROM robi nso n;-注意此处的提示revoke的是整个表，而非列REVOKE UPDATE(sal,mgr) ON emp FROM robi nson*ERROR at line 1:ORA-01750: UPDATE/REFERENCES mayon lybe REVOKEd from thewhole table, not by colu mnSQL> REVOKE UPDATE ON emp FROM robi nson;Revoke succeeded.-用户robinson 的update 权限被revoke,曾级联赋予john的权限也被收回,-如下提示表、视图不存在， user_col_privs_recd中无记录SQL> CONN joh n/joh n;Conn ected.SQL> UPDATE scott.emp SET