一、用户管理
1、查看当前数据库系统有哪些用户：
SYS@myh>desc dba_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
SYS@myh>select username,user_id,account_status from dba_users;
2、创建一个新用户
SYS@myh>create user test identified by oracle;
User created.
SYS@myh>alter user test identified by oracle; #修改密码但是当通过此用户连接数据库时，会提示错误（没有创建会话的权限）
SYS@myh>conn test/oracle
ERROR:
ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied
3、授予用户权限，然后在连接数据库
SYS@myh>grant create session to test;
Grant succeeded.
SYS@myh>conn test/oracle
Connected.
SYS@myh>grant create session to test identified by oracle; #创建用户并授予权限
4、用户状态（ACCOUNT_STATUS）
open expried locked
SYS@myh>alter user H account lock; #对用户加锁
User altered.
SYS@myh>conn H/oralce
ERROR:
ORA-28000: the account is locked
SYS@myh>alter user H account unlock; #解锁
User altered.
SYS@myh>alter user test password expire; #使用户密码过期，强制修改密码
User altered.
SYS@myh>conn test/oracle #过期用户连接数据库时，会提示输入新密码
ERROR:
ORA-28001: the password has expired
Changing password for test
New password:
Retype new password:
Password changed
Connected.
5、删除用户
SYS@myh>drop user test;
#当用户此用户已经创建表等实体时，删除用户需要加上 cascade
SYS@myh>drop user test;
drop user test
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'TEST'
SYS@myh>drop user test cascade;
User dropped
补充：重建Scott用户：
SYS@myh>@?/rdbms/admin/utlsampl.sql;
6、查询所有连接SCOTT用户的会话：
SYS@myh>select username,sid,serial# from v$session where username='SCOTT';
USERNAME SID SERIAL#
------------------------------ ---------- ----------
SCOTT 28 17
SCOTT 43 165
SCOTT 55 99
杀掉某个连接的会话：
SYS@myh>alter system kill session '43,165';二、权限管理
1、系统权限
分类：
DBA：拥有全部特权，系统最高权限，只有DBA才可以创建数据库结构
resource：拥有resource权限的可以创建实体
connect：拥有connect全县的俄可以登陆Oracle，不可以创建实体。
#查看全部系统权限
SYS@myh>select distinct name from system_privilege_map order by 1;
对普通用户：授予connect和resource权限
#查看某个用户拥有的系统权限
SYS@myh>select * from dba_sys_privs where grantee='SCOTT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
#赋予权限
SYS@myh>grant create view to scott;
Grant succeeded.
#撤销权限
SYS@myh>revoke create view from scott;
Revoke succeeded.
系统权限的传递：拥有级联权限的普通用户1，可以将分配权限给用户2，撤销用户1的权限，用户2的权限不会被影响。
SYS@myh>select * from dba_sys_privs where grantee='H'; #查看H用户的权限
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
H CREATE SESSION NO
SYS@myh>select * from dba_sys_privs where grantee='SCOTT'; #查看SCOTT用户的权限
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
SYS@myh>grant create view to scott; #授予scott创建视图的权限
SYS@myh>grant create table to scott with admin option; #以级联的方式授予scott创建表的权限
SYS@myh>select * from dba_sys_privs where grantee='SCOTT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT CREATE VIEW NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT CREATE TABLE YES
SYS@myh>conn scott/oracle
Connected.
SCOTT@myh>grant create view to h; #create view 不是级联权限，所以不能授予其他用户
grant create view to h
*
ERROR at line 1:
ORA-01031: insufficient privileges
SCOTT@myh>grant create table to h; #将create table 授予H用户
Grant succeeded.
SCOTT@myh>conn sys/oracle as sysdba;
Connected.
SYS@myh>select * from dba_sys_privs where grantee='H';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
H CREATE SESSION NO
H CREATE TABLE NO
SYS@myh>revoke create table from scott; #收回scott的权限，发现H用户的权限没有被收回
Revoke succeeded.
SYS@myh>select * from dba_sys_privs where grantee='H';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
H CREATE SESSION NO
H CREATE TABLE NO
2、对象权限
select,update,insert alter,index,delete,all（all包括全部权限）
不同的对象对象权限也不同，对象的拥有者，拥有对对象的所有权限，可以授予用户对象权限，从而操作其他用户的对象。
级联：
拥有级联权限的用户1，可以将权限给别的用户2，撤回用户1的权限，用户2的权限也被撤回
SYS@openlab> grant select on scott.emp to ocm with grant option;
3、角色
角色：一组权限的集合，将角色赋予某个用户，这个用户就拥有的这个角色中的所有权限。
角色分为系统生成的角色和自定义的角色。
#查看系统生成的角色
SYS@myh>select * from dba_roles;
#查看角色中包含了哪些权限
SYS@myh>select * from role_sys_privs where role='RESOURCE';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO
#创建角色
SYS@myh>create role r1; #创建角色r1，无密码
Role created.
SYS@myh>create role r2 identified by oracle; #创建r2，密码为oracle
Role created.
#授予角色权限
SYS@myh>grant create view to r1; #将系统权限授予角色
Grant succeeded.
SYS@myh>grant select on scott.emp to r2; #将对象权限授予角色
Grant succeeded.
SYS@myh>grant r2 to r1; #将角色授予角色
Grant succeeded.
#查看角色拥有的权限
SYS@myh>select * from role_sys_privs where role='R1';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
R1 CREATE VIEW NO
#赋予角色：
SYS@openlab> grant r1,r2 to xian; #r1,r2都没有密码，r1生效;一个有密码，有密码的生效；都有密码，都生效
#给角色设置密码
SYS@myh>set role r1 identified by oracle;
Role set.
#删除角色
SYS@myh>drop role r1;
Role dropped.
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
