SQL 21 天自学通学习笔记 5

1. 用户操作：

 

    创建：

 

 

CREATE USER user
IDENTIFIED {BY password | EXTERNALLY}
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {integer [K|M] | UNLIMITED} ON tablespace]
[PROFILE profile]

 

 

    修改：

 

 

ALTER USER user
[IDENTIFIED {BY password | EXTERNALLY}]
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {integer [K|M] | UNLIMITED} ON tablespace]
[PROFILE profile]
[DEFAULT ROLE { role [, role] ...
| ALL [EXCEPT role [, role] ...] | NONE}]

 

 

    删除：

 

 

DROP USER user_name [CASCADE]

 

 

    授权，撤销权限：

 

GRANT role TO user [WITH ADMIN OPTION]

REVOKE role FROM user

 

 

    系统允许 赋于整个系统的权限给用户，角色等：

 

 

GRANT system_privilege TO {user_name | role | PUBLIC}
[WITH ADMIN OPTION]

 

    如果使用了WITH ADMIN OPTION 选项就允许拥有该权限的人将该权限应用给其它

    的用户。

 

 

    你可以使用下边的GRANT 语句来对其它用户授权访问你的表

 

GRANT {object_priv | ALL [PRIVILEGES]} [ (column
[, column]...) ]
[, {object_priv | ALL [PRIVILEGES]} [ (column
[, column] ...) ] ] ...
ON [schema.]object
TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...
[WITH GRANT OPTION]

 

 

    如果你想取消对某个对象对于某人的授权你可以使用REVOKE 语句语法如下

 

REVOKE {object_priv | ALL [PRIVILEGES]}
[, {object_priv | ALL [PRIVILEGES]} ]
ON [schema.]object
FROM {user | role | PUBLIC} [, {user | role | PUBLIC}]
[CASCADE CONSTRAINTS]