用户
删除表空间
drop table table_name including contents and datafiles;
创建用户
create user username identified by password;
修改用户密码
alter user username identified by password";
查看所有用户
select user username from dba_users;
查看当前用户**
show userselect user from dual;
权限
connect role | resource role | dba role |
---|---|---|
连接角色 | 资源角色 | 数据库管理员角色 |
为用户授权角色 grant connect,resource,dba to user; grant create session to username;
给用户权限
grant 权限,权限 to user;
grant create session to user;
grant create table to user;
grant select on table_name to user;
权限包括:
-
create session 创建对话
-
create table 创建表
-
create sequence 创建序列
-
create view 创建视图
-
create procedure 创建存储过程
撤销授权
revoke connect,resource from user;
创建角色并赋权
create role manager;创建角色 grant create table,create view to manager;为角色赋予权限 grante manager to xiaoming,xiaoli;将角色赋予用户
查看用户所拥有的权限
-- 查看用户对象权限 select * from dba_tab_privs;select * from dba_tab_privs where grantee = "USER"; -- 查看用户系统权限 select * from dba_sys_privs;select * from dba_sys_privs where grantee = "USER"; -- 查看用户所拥有的角色 select * from dba_role_privs;select * from dba_role_privw where grantee = "USER"; -- 查看系统的所有角色 select * from dba_roles; -- 查看哪些用户被授予了 DBA 权限 select * from dba_role_privs where granted_role='DBA'; -- 查看哪些用户拥有sysdba或者sysoper系统权限 select * from v$pwfile_users; -- 查看已经登录的用户已经拥有的角色 select * from role_sys_privs;
对象权限
-
不同的对象具有不同的对象权限
-
对象的拥有者拥有所有的权限
-
对象的拥有者可以向外分配权限
grant | on | to |
---|---|---|
操作权限 | 拥有者(拥有者.表 or 表) | 分配者 |
eg.
# 分配表xiaoxiao的查询权限 grant all | select | update | delete on xiaoxiao to wuwu; # 分配表中各个列的更新权限 grant update (department_name, location_id) on departments to scott, manager;
分配对象的权限
with grant option
使得用户同样具有分配权限的权利
grant select,insert on departments to scott with grant option;
grant select on scott.departments to icss;
收回对象的权限
使用with grant option
子句所分配的权限同样被收回
revoke select,insert on departments from scott;