Oracle 用户、角色管理简介

                                         Oracle 用户、角色管理简介

创建用户

形式1：创建名为testacc2的用户

CREATEUSERtestacc2

  IDENTIFIEDBYabc1234

  DEFAULTTABLESPACEtesttbs001

  QUOTA10MONtesttbs001

  TEMPORARYTABLESPACEtesttbs002

  QUOTA5MONSYSTEM

  PASSWORDEXPIRE;

 

说明：

1）testacc2：用户名

 

2）abc1234：用户密码（大小写敏感

 

3）DEFAULT TABLESPACE testtbs001：指定在用户方案(schema)中创建的对象的默认表空间为testtbs001

说明：

1.如果不指定，则用户对象存储在数据库默认表空间中。如果没有为数据库指定默认表空间，那么用户对象将存储在SYSTEM表空间

2.限制：不能指定本地管理的临时表空间，包括undo表空间，或者字典管理的临时表空间作为用户默认表空间。

 

4）QUOTA 10M ON testtbs001：设置用户在指定表空间中testtbs001的可用配额

说明：

1.一个CREATE USER语句可有多个QUOTA子句。

2.QUOTA子句的限制：不能为临时表空间指定该子句

 

5）TEMPORARY TABLESPACE testtbs002：为用户临时段指定表空间或表空间组。这里指定临时表空间为testtbs002。

说明：

1.如果不指定该语句，那么用户临时段存储在数据库默认的临时表空间中，或者没有为数据库指定默认表空间，那么存储在SYSTEM表空间中。

2.如果指定的是表空间名，则表示用户的临时表空间

3.如果指定是表空间组名，则表示用户可以在由表空间组名指定的表空间组中的任意表空间中存储临时表空间段

4.限制：表空间必须为临时表空间，且具有标准块大小；表空间不能是UNDO表空间或有自动段空间管理的表空间

 

6）PASSWORD EXPIRE：设置用户密码过期。这样，当用户登录数据库时，必须重新设置密码。

如下：

SQL> conn testacc2

Enter password:

ERROR:

ORA-28001: the password has expired

 

 

Changing password for testacc2

New password:

Retype new password:

ERROR:

ORA-01045: user TESTACC2 lacks CREATE SESSION privilege; logon denied

 

 

Password changed

Warning: You are no longer connected to ORACLE.

SQL> conn testacc2

Enter password:

ERROR:

ORA-01045: user TESTACC2 lacks CREATE SESSION privilege; logon denied

说明：提示错误是因为此时没有授予用户权限

 

形式2：创建名为testacc2的用户，在默认表空间中的可用配额无限制

CREATEUSERtestacc2

  IDENTIFIEDBYabc1234

  DEFAULTTABLESPACEtesttbs001

  QUOTAUNLIMITEDONtesttbs001

  TEMPORARYTABLESPACEtesttbs002

  QUOTA5MONSYSTEM;

说明：

QUOTA UNLIMITED ON testtbs001：设置用户在指定表空间testtbs001中可用配额无限制

 

形式3：创建名为testacc2的用户，指定profile

CREATEUSERsidney

 IDENTIFIEDBYout_standing1

 DEFAULTTABLESPACEexample

 QUOTA10MONexample

 TEMPORARYTABLESPACEtemp

 QUOTA5MONsystem

 PROFILEapp_user

 PASSWORDEXPIRE;

 

说明：  PROFILE app_user，指定分配给用户的profile。profile限制用户可使用的数据库资源的数量。如果未指定，oracle会使用默认的profile.

注意：oracle推荐使用Database Resource Manager,而不是SQL profile来建立数据库资源限制。

参考连接：

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8003.htm#SQLRF01503

 

 

修改用户

形式1：修改用户口令为abc123

ALTERUSERtestacc2IDENTIFIEDBYabc123;

 

形式2：设置用户口令过期

 

ALTERUSERtestacc2PASSWORDEXPIRE;

 

形式3：修改用户默认表空间为testbs01

ALTERUSERtestacc2DEFAULTTABLESPACEtesttbs01;

 

形式4：修改用户临时表空间为TEMP

ALTERUSERtestacc2TEMPORARYTABLESPACETEMP;

说明：如果要修改用户所在临时表空间组名，如下

ALTER USER username TEMPORARY TABLESPACE tablespace_group_name;

 

形式5：修改用户在表空间testtbs001中的配额为10M（注：如果无限制则设置QUOTA UNLIMITED

ALTERUSERtestacc2QUOTA10MONtesttbs001;

 

形式5：锁定用户、解锁用户

锁定

ALTERUSERtestacc2ACCOUNTLOCK;

 

解锁

ALTERUSERtestacc2ACCOUNTUNLOCK;

 

形式6：修改用户的默认角色

ALTER USER username DEFAULT ROLE option;

作用：指定登录时，授权给用户的默认角色。

选项可以是：

ALL:所有角色

ALL EXCEPT role_name：除角色role_name之外的所有角色

NONE：没任何角色

role_name：由role_name指定的角色

如下：

ALTERUSERtestacc2DEFAULTROLEALL;

 

ALTERUSERtestacc2DEFAULTROLEALL EXCEPTrole_name;

 

ALTERUSERtestacc2DEFAULTROLENONE;

说明：

以下情况不能使用上述语句：

a) 角色为未授权给用户的角色

b) 角色为通过其他角色授权给用户的角色

c) 角色为由外部服务（如操作系统）或Oracle Internet Directory管理的角色或者external service (such as the operating system), or by the

d) 由SET ROLE开启的角色，如密password-authenticated和secure application角色

 

形式7：修改用户的profile文件

ALTERUSERtestacc2PROFILEnew_profile;

 

参考连接：

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4003.htm#SQLRF01103

 

 

创建角色

形式1：创建角色，不使用密码

CREATEROLEtest_role;

说明：被授予test_role角色的用户将继承授权给test_role角色的所有权限。

 

形式2：创建角色，并使用密码

CREATEROLEtest_role2IDENTIFIEDBYabc1234;

说明：使用了IDENTIFIED BY，被授予角色（例中为test_role2）的用户必须使用SET ROLE来激活角色：SET ROLE roel_name IDENTIFIED BY role_password

参考连接：

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6012.htm#SQLRF01311

 

修改角色

CREATEROLEtest_roleIDENTIFIEDBYabc123;

形式1：修改角色为不需要验证激活

ALTERROLEtest_roleNOTIDENTIFIED;

 

形式2：修改角色验证密码

ALTERROLEtest_roleIDENTIFIEDBYabc1234;

注意：

如果NOT IDENTIFIED角色已经被授权给其它角色，则不能修改NOT IDENTIFIED为IDENTIFIED

 

参考链接：

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2010.htm#SQLRF00815

 

 

用户授权

1.授予用户连接权限

GRANTCONNECTTOtestacc2;

 

授予权限后，sqlplus连接

SQL> CONN testacc2

Enter password: #输入登录密码

Connected.

 

2.授予用户创建会话权限

GRANTCREATESESSIONTOtestacc2;

 

注意：用户如果需要登录oracle，必须有CONNECT、CREATE SESSION的权限

 

3.授予用户系统权限

GRANT system_privillege TO testacc2;

 

GRANTALLPRIVILEGESTOtestacc2;

 

说明：ALL PRIVILEGES：代表表Table 18-1中，除ANY DICTIONARY, ALTER DATABASE LINK,和ALTER PUBLIC DATABASE LINK权限外的所有系统权限

 

4.授予用户所有对象权限

GRANT object_privillege[(colument)] ON object TO testacc2;

 

GRANT ALL [PRIVILEGES] ON object TO username;

 

例：授予用户testacc2 test_table表上的所有权限

GRANTALLONtest_tableTOtestacc2;

或

GRANTALLPRIVILEGESONtest_tableTOtestacc2;

 

说明：

1.ALL [PRIVILEGES]，授予对象的所有权限给用户，授权者必须有该对象的GRANT OPTION权限.方案的所有者用户自动包含所有方案(schema)中所有对象的所有权限，且对每个对象都有GRANT OPTION权限。

2.被授权用户查询授权者用户方案下的对象时，必须加上方案名(通常为用户名)，如下，加方案名testacc

SQL> SELECT * FROM testacc.test_table;

no rows selected

否则，会提示ORA-00942: table or view does not exist的错误

注：此处，表test_table为testacc用户所创建的

 

5.授予用户在某个表或视图的数据列上的权限

GRANTUPDATE(id)ONtest_table TOtestacc2;

说明：仅在授予INSERT, REFERENCES,或UPDATE权限时才可指数据列

 

6.授予用户权限，且被授予者拥有把权限授予其他人、角色的权限

GRANTSELECT,DELETEONtest_tableTOtestacc2

 WITHGRANTOPTION;

 

说明：

授予用户testacc2对表test_table的查询，删除权限，WITH GRANT OPTION,使用户testacc2可把获取的权限再授予其他用户、角色

 

7．授予用户权限，被授权用户有管理权限

GRANTUPDATEONtest_tableTOtestacc2WITHGRANTOPTION;

 

注意：WITH GRANT OPTION仅适用于角色、对象权限

 

8.授予用户角色

形式1：授予用户角色，不指定WITH ADMIN OPTION;

GRANTtest_roleTOtestacc2;

 

形式2：授予用户角色，指定WITH ADMIN OPTION;

GRANTtest_role2TOtestacc2WITHADMINOPTION;

注意：

WITH ADMIN OPTION允许被授权用户:

1）授权角色、权限给其他角色、用户，GLOBAL角色除外

2）从其他用户、角色回收权限、角色

3）修改权限、角色

4）删除权限、角色

 

WITH ADMIN OPTION仅适用于角色，系统权限

 

9.授予所有用户权限

GRANTSELECT,UPDATEONtest_tableTOpublic;

说明：public,代表所有用户

 

参考连接：

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#SQLRF01603

 

 

回收权限、角色

形式1：回收某个、多个对象权限

REVOKEUPDATEONtest_tableFROMtestacc2;

 

REVOKEUPDATE,SELECTONtest_tableFROMtestacc2;

 

形式2：回收所有对象权限

REVOKEALLONtest_tableFROMtestacc2;

或者

REVOKEALLPRIVILEGES ONtest_tableFROMtestacc2;

 

形式3：回收所有授予用户的系统权限

REVOKEALLPRIVILEGESFROMtestacc2;

 

形式4：回收授予用户的某个、多个系统系统权限

REVOKECONNECTFROMtestacc2;

 

REVOKECREATEANYTABLE,CREATETABLEFROMtestacc2;

 

形式4：回收授权给用户的角色

REVOKEtest_roleFROMtestacc2;

 

形式5：级联回收对象权限

REVOKEREFERENCES ONtest_tableFROMtestacc2CASCADECONSTRAINTS;

说明：

CASCADE CONSTRAINTS,仅和移除REFERENCES或ALL [PRIVILEGES]对象权限相关.也就是说该语句的作用为：1.删除由testacc2所创建的，REFERENCES指定的所有参照完整性约束外键约束；2.回收testacc2用户创建外键的权限

 

形式6：从多用户回收权限

REVOKECREATESESSIONFROMtestacc2,testacc2

 

形式7：收回所有用户的权限

REVOKESELECT,UPDATEONtest_tableFROMpublic;

 

参考连接：

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9020.htm#SQLRF01609

 

 

删除用户

形式1：删除用户，不删除用户用户方案中包含的对象

DROPUSERtestacc2;

 

形式2：删除用户，并删除用户方案中包含的方案对象，比如表，视图等

DROPUSERtestacc2CASCADE;

 

参考连接：

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9008.htm#SQLRF01811

 

删除权限

DROPROLEtest_role;