本文详细介绍了Oracle数据库中用户账户的管理，包括用户类型、登录、查看与创建用户，以及解锁和修改密码。同时，讨论了表空间的管理，如何创建、查看和调整大小，以及用户默认表空间的设定。此外，文章还涵盖了用户表的管理、空间配额的设定和概要文件的作用及参数配置，强调了系统权限、对象权限和角色在数据库安全中的重要性。
1. 管理数据库账户
1.1 用户类型
sys; # 系统管理员,拥有最高权限
system; # 本地管理员,次高权限
scott; # 普通用户,密码默认为tiger,默认未解锁
1.2 登陆
# 服务器登陆
sqlplus / as sysdba; # 登陆sys账户
sqlplus sys as sysdba; # 同上
sqlplus scott/tiger; # 登陆普通用户scott
-- sqlplus 登录@和密码之间不能有空格,conn切换账户可以有空格--------
# 远程登录
sqlplus sys/password@orcl as sysdba
sqlplus scott/tiger@orcl
# 连接登陆
conn sys/pasword@orcl as ssydba
conn scott/tiger@orcl
1.3 查看用户
-- sys 查看数据库里有多少用户？
show user -- 查看当前登录账户
select * from dba_users; -- 查看全库所有用户
select * from all_users; -- 查看当前用户能看的用户信息
select * from user_users; -- 查看当前用户信息
SQL>
SQL> desc dba_users;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
LOCAL_TEMP_TABLESPACE VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(128)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(17)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED VARCHAR2(1)
INHERITED VARCHAR2(3)
DEFAULT_COLLATION VARCHAR2(100)
IMPLICIT VARCHAR2(3)
ALL_SHARD VARCHAR2(3)
PASSWORD_CHANGE_DATE DATE
SQL> desc all_users;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
CREATED NOT NULL DATE
COMMON VARCHAR2(3)
ORACLE_MAINTAINED VARCHAR2(1)
INHERITED VARCHAR2(3)
DEFAULT_COLLATION VARCHAR2(100)
IMPLICIT VARCHAR2(3)
ALL_SHARD VARCHAR2(3)
SQL> desc user_users;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
LOCAL_TEMP_TABLESPACE VARCHAR2(30)
CREATED NOT NULL DATE
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
ORACLE_MAINTAINED VARCHAR2(1)
INHERITED VARCHAR2(3)
DEFAULT_COLLATION VARCHAR2(100)
IMPLICIT VARCHAR2(3)
ALL_SHARD VARCHAR2(3)
PASSWORD_CHANGE_DATE DATE
SQL> 1.4 创建编辑用户
create user username identified by password; -- 创建用户
alter user username account unlock; -- 解锁用户
alter user username identified by password; -- 修改密码
-- 创建用户的三个缺省:default tbs、default temp tbls、default profileSQL>
SQL> create user pandasthree identified by pandasthree;
User created.
SQL> alter user pandasthree account unlock;
User altered.
SQL> alter user pandasthree identified by pandas;
User altered.
SQL>1.5 删除用户
-- cascade会将该用户下的所有表都删除
drop user username cascade;
SQL> drop user pandasthree cascade;
User dropped.
SQL>
2. 用户表管理
2.1 当前用户的表
desc user_tables
select * from user_tables;
select table_name from user_tables;
2.2 所有用户的表
select * from all_tables;
2.3 系统表
select * from dba_tables;
2.4 查看指定用户的表
select * from dba_tables where owner='用户名';
select table_name from dba_tables where owner='SCOTT';
2.5 查看指定用户表的数据
select * from username.tablename3. 用户的默认表空间
每个用户账户都可以指定默认的 TBS,用户创建的任何对象(如表或索引)将缺省保存在此 TBS中,如果创建用户时没有指定默认 TBS,那么属于数据库级的默认 TBS(DBCA 创建数据库时默认是users TBS)将应用于该账户.
3.1 查看数据库级的默认 TBS
select * from database_properties;3.2 查看当前用户 TBS
SQL> select default_tablespace from user_users;
DEFAULT_TABLESPACE
------------------------------------------------------------------------------------------
SYSTEM
SQL> 3.3 查看所有TBS
SQL> select tablespace_name from user_tablespaces;
TABLESPACE_NAME
------------------------------------------------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEMP02
DATA01
TBS_MAXWELL
TEMP_MAXWELL
UNDOTBS2
TBS_TOOLS
RMAN1
12 rows selected.
SQL> 3.4 查看TBS对应的数据文件
SQL>
SQL> col tablespace_name format a10
SQL> col file_name format a45
SQL>
SQL> select tablespace_name, file_id,file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
TABLESPACE FILE_ID FILE_NAME TOTAL_SPACE
---------- ---------- --------------------------------------------- -----------
DATA01 85 /u02/oradata/CDB1/pdb1/data01.dbf 50
RMAN1 169 /u02/oradata/CDB1/pdb1/rman01.dbf 6
SYSAUX 10 /u02/oradata/CDB1/pdb1/sysaux01.dbf 460
SYSTEM 9 /u02/oradata/CDB1/pdb1/system01.dbf 300
TBS_MAXWEL 126 /u02/oradata/CDB1/pdb1/TBS_MAXWELL_01.dbf 500
L
TBS_TOOLS 163 /u02/oradata/CDB1/pdb1/TBS_TOOLS01.dbf 10
UNDOTBS1 11 /u02/oradata/CDB1/pdb1/undotbs01.dbf 185
UNDOTBS2 127 /u02/oradata/CDB1/pdb1/undotbs02.dbf 200
USERS 12 /u02/oradata/CDB1/pdb1/users01.dbf 230
9 rows selected.
SQL>
3.5 创建 TBS
SQL> create tablespace 表间名 datafile '数据文件名' size TBS大小
SQL> create tablespace test_tbs datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 10m;
3.6 创建用户指定 TBS
SQL> create user 用户名 identified by 密码 default tablespace TBS名称;
3.7 更改默认 TBS
SQL> alter database default tablespace tablespace_name;
3.8 删除drop TBS
SQL> drop tablespace tbs_name including contents and datafiles;
4. 空间配额
配额(quota)是 TBS为用户的对象使用的空间量,dba 建立用户时就应该考虑限制用户的磁盘空间配额,否则无限制配额的用户可能把的 TBS撑爆(甚至损坏system TBS).
ALTER USER klaus QUOTA 10m ON test_tbs; -- 设置限额=10m
ALTER USER klaus QUOTA -- 不受限制
ALTER USER klaus QUOTA 0 ON test_tbs; -- 收回限额
知识点:
要创建表,用户必须具有执行create table的权限,而且拥有在该表使用的 TBS上的配额.
用户不需要临时 TBS上的配额,UNOD TBS也不能针对用户设置配额.
SQL> select * from v$tablespace;
5. 调整 TBS的大小
( TBS的大小等同它下的数据文件大小之和)
当发生 TBS不足的问题时常用的3个解决办法
5.1 查看表空间大小
-- 查看大小
SELECT TABLESPACE_NAME "TablespaceName",
To_char(Round(BYTES / 1024, 2), '99990.00') || '' "Total",
To_char(Round(FREE / 1024, 2), '99990.00') || 'G' "Free",
To_char(Round((BYTES - FREE) / 1024, 2), '99990.00') || 'G' "Used",
To_char(Round(10000 * USED / BYTES) / 100, '99990.00') || '%' "Present"
FROM (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
Floor(A.BYTES / (1024 * 1024)) BYTES,
Floor(B.FREE / (1024 * 1024)) FREE,
Floor((A.BYTES - B.FREE) / (1024 * 1024)) USED
FROM (SELECT TABLESPACE_NAME TABLESPACE_NAME, Sum(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME TABLESPACE_NAME, Sum(BYTES) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME)
ORDER BY Floor(10000 * USED / BYTES) DESC;
-- ---结果如下---
TablespaceName Total Free Used Present
----------------------------- ----- ------ ----- ---------
SYSTEM 0.87 0.00G 0.87G 99.89%
SYSAUX 0.61 0.03G 0.57G 94.19%
UNDOTBS2 0.02 0.01G 0.01G 52.00%
USERS 0.00 0.00G 0.00G 40.00%
5.2 调整表空间大小的三种方式
-- 增加原数据文件大小(resize)
alter database datafile '/u01/app/oracle/oradata/orcl/timran01.dbf' resize 10m;
-- 增加数据文件(add datafile)
alter tablespace klaus add datafile '/u01/app/oracle/oradata/orcl/klaus02.dbf' size 20m;
-- 设置表空间数据文件自动增长(autoextend)
alter database datafile '/u01/app/oracle/oradata/orcl/klaus01.dbf' autoextend on next 10m maxsize 500m;
5.3 实验
-- 例:
-- 使表空间不足
SQL> select tablespace_name from user_tablespaces;
SQL> create tablespace klaus datafile '/u01/app/oracle/oradata/orcl/klaus01.dbf' size 5m;
SQL> create table scott.test1 (id int) tablespace klaus;
SQL> insert into scott.test1 values(1);
SQL> insert into scott.test1 select * from scott.test1;
SQL> /
SQL> /
131072 rows created.
SQL> /
insert into scott.test1 select * from scott.test1
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.TEST1 by 128 in tablespace KLAUS
-- 方法一:用resize方法扩充TBS
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/klaus01.dbf' resize 10m;
SQL> insert into scott.test1 select * from scott.test1;
SQL> /
262144 rows created.
SQL> /
insert into scott.test1 select * from scott.test1
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.TEST1 by 128 in tablespace KLAUS
-- 方法二:用add datafile方法扩充TBS
SQL> alter tablespace klaus add datafile '/u01/app/oracle/oradata/orcl/klaus02.dbf' size 20m;
SQL> insert into scott.test1 select * from scott.test1;
SQL> /
1048576 rows created.
SQL> / insert into scott.test1 select * from scott.test1
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.TEST1 by 128 in tablespace KLAUS
-- 方法三:用autoextend方法扩充TBS
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/klaus01.dbf' autoextend on next 10m maxsize 500m;
SQL> insert into scott.test1 select * from scott.test1;
SQL> drop tablespace klaus including contents and datafiles;
6. 管理概要文件(profile)
6.1 作用
对用户访问数据库做一些限制
6.2 几个要点
- 概要文件(profile)具有两个功能,一个是实施口令限制,另一个是限制会话可以占用的资源.
- 始终要实施口令控制,而对于资源限制,则只有实例参数RESOURE_LIMIT为TRUE时(默认是FALSE)才会实施.
- 系统自动使用概要文件,有一个默认的default profile,限制很松,作用较小.
- 可以使用create profile为用户创建它自己的概要文件,没有说明的参数就从default profile的当前版本中提取
6.3 Password_parameter参数
Failed_login_attempts #:指定在账户被锁定之前所允许尝试登陆的最大次数.
PASSWORD_LIFE_TIME #:密码有效期
Password_lock_time #:在到达Failed_login_attempts后锁定账户的天数.
Password_life_time #:口令过期前的天数,口令在过期后仍可以使用,具体取决于
Password_grace_time #:口令过期(将生成更改口令的提示)后第一次成功登录后的天数,期间,旧口令仍然可用.
Password_reuse_time #:可以重新使用口令前的天数
password_reuse_max #:可以重新使用口令的次数
Password_verify_function#:更改口令时运行的函数名,此函数一般用于检查新口令所需的复杂程度.
6.4 Resource_parameter参数
Session_per_user #:对同一个用户账户执行的并发登录数.
Cpu_per_session #:在强制终止会话前,允许会话的服务器进程使用的CPU时间(厘秒).
Cpu_per_call #:在强制终止某条SQL语句前,允许会话的服务器进程用于执行此语句的CPU时间(厘秒).
Connect_time #:在强制终止会话前,会话的最长持续时间(分钟).
Idle_time #:在强制终止会话前,允许会话处于闲置状态的最长时间(分钟).
Logical_reads_per_session#:在强制终止会话前,会话可读取的块数(无论块在数据缓冲区还是磁盘).
Logical_read_per_call #:在强制终止单个语句前,此语句可读取的块数(无论块在数据缓冲区还是磁盘).
Private_sga #:对于通过共享服务器体系结构连接的会话,允许会话的会话数据在SGA中占用的字节数(知识点).
Composite_limit #:前面几个参数的加权和.这是一个高级功能,其需要的配置不在 OCP考试范围.
6.5 实验
6.5.1 创建用户
SQL> create user pandas identified by pandas;
User created.
SQL>6.5.2 创建profile文件
SQL> -- 两次口令错误,账户锁定
SQL> create profile two_error limit failed_login_attempts 2;
Profile created.
SQL>6.5.3 分配profile
SQL> -- 将概要文件分配给pandas用户
SQL> alter user pandas profile two_error;
User altered.
SQL> -- 查看用户的profile
SQL>
SQL> select username, PROFILE from dba_users where username='PANDAS';
USERNAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROFILE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PANDAS
TWO_ERROR
SQL>6.5.4 尝试错误密码登陆
SQL> conn pandas/pand@PDB1;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn pandas/pd@PDB1;
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn pandas/pands@PDB1;
ERROR:
ORA-28000: The account is locked.
SQL> 6.5.5 sys为pandas解锁
SQL>
SQL> show user;
USER is ""
SQL> conn / as sysdba;
Connected.
SQL> show user;
USER is "SYS"
SQL> alter user pandas account unlock;
alter user pandas account unlock
*
ERROR at line 1:
ORA-01918: user 'PANDAS' does not exist
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB1;
Session altered.
SQL> alter user pandas account unlock;
User altered.
SQL> 6.5.6 sys删掉two_error概要文件
SQL>
SQL> -- 删除two_error后,klaus用户又绑定到 default profile上
SQL> drop profile two_error cascade;
Profile dropped.
SQL> 6.5.6 自动绑定默认profile
SQL>
SQL> -- profile这部分操作参数较多,使用命令有些啰嗦,可以使用OEM方式来管理,比较方便.
SQL> select username, PROFILE from dba_users where username='PANDAS';
USERNAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROFILE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PANDAS
DEFAULT
SQL> 6.6 查看sys的profile
SQL>
select u.username,
u.expiry_date,
p.profile,
p.resource_name,
p.resource_type,
p.limit
from dba_users u,
dba_profiles p
where p.profile=u.profile
and u.username ='SYS';
USERNAME EXPIRY_DA PROFILE RESOURCE_NAME RESOURCE LIMIT
---------- --------- -------------------- ------------------------------ -------- --------------
SYS 14-FEB-21 DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
SYS 14-FEB-21 DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
SYS 14-FEB-21 DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
SYS 14-FEB-21 DEFAULT CPU_PER_CALL KERNEL UNLIMITED
SYS 14-FEB-21 DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
SYS 14-FEB-21 DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
SYS 14-FEB-21 DEFAULT IDLE_TIME KERNEL UNLIMITED
SYS 14-FEB-21 DEFAULT CONNECT_TIME KERNEL UNLIMITED
SYS 14-FEB-21 DEFAULT PRIVATE_SGA KERNEL UNLIMITED
SYS 14-FEB-21 DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
SYS 14-FEB-21 DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
SYS 14-FEB-21 DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
SYS 14-FEB-21 DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
SYS 14-FEB-21 DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
SYS 14-FEB-21 DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
SYS 14-FEB-21 DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
16 rows selected.
# 密码过期时间
# 可以看到密码有效期180天
# 登陆10次，账户将会锁住
# 如下可以让账户密码永不过期
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED FAILED_LOGIN_ATTEMPTS UNLIMITED;
6.7 密码修改历史
# 密码修改历史
SQL>
SELECT name,
password_date,
user_history$.password
FROM sys.user$,
sys.user_history$
WHERE user$.user# = user_history$.user#;
# 密码创建时间、更改时间、过期时间、锁定时间
SQL>
select
to_char(ctime,'YYYY-MM-DD HH24:MI:SS') create_time,
to_char(ptime,'YYYY-MM-DD HH24:MI:SS') change_time,
to_char(exptime,'YYYY-MM-DD HH24:MI:SS') expire_time,
to_char(ltime,'YYYY-MM-DD HH24:MI:SS') life_time
from user$ where name='SYS';
7. 系统权限,对象权限,角色
7.1 权限的引入
数据库安全分为系统安全和数据安全
系统安全:用户名和口令,分配给用户的磁盘空间及用户的系统操作,如profile等数据库安全:对数据库对象的访问及操作
用户具备系统权限才能够访问数据库,具备对象权限才能访问数据库中的对象简而言之:权限(privilege):system privilege和object privilege
7.1.1 system privilege
针对于database 的相关权限
系统权限通常由 DBA 授予 (11g 有 200 多种,select distinct privilege from dba_sys_privs;也可被其他用户或角色授予)
7.1.1.1 典型DBA权限:
CREATE USER
DROP USER
BACKUP ANY TABLE
SELECT ANY TABLE
CREATE ANY TABLE
7.1.1.2 典型用户需要的系统权限:
CREATE SESSION
CREATE TABLE
CREATE SEQUENCE
CREATE VIEW
CREATE PROCEDURE
7.1.2 object privilege
针对于schema (用户)的object
对象权限有8种: ALTER, DELETE, EXECUTE, INDEX, INSERT, REFERENCES, SELECT, UPDATE
对象权限 表 视图 序列 过程7.2 权限的授权
7.2.1 授予系统权限语法:
GRANT sys_privs, [role] TO user|role|PUBLIC [WITH ADMIN OPTION]
-- 授予角色与系统权限的语法格式是一样的,所以可以并列在一个句子里赋权
7.2.2 授予对象权限语法
GRANT object_privs ON object TO user|role|PUBLIC [WITH GRANT OPTION]
7.2.3 创建和删除角色
CREATE role myrole;
DROP role myrole;
7.3 角色的引入
系统权限太繁杂,将系统权限打包成角色,Oracle建议通过角色授权权限,目的就是为了简化用户访问管理
connect是角色
sys:
SQL> drop user pandas cascade; -- drop之前的用户
User dropped.
SQL> 7.3.1 通过实验理解
7.3.1.1 创建用户
SQL>
SQL> create user pandas identified by pandas;
User created.
SQL>
7.3.1.2 登陆用户被拒绝
SQL> conn pandas/pandas@PDB1;
ERROR:
ORA-01045: user PANDAS lacks CREATE SESSION privilege; logon denied -- 用户 Pandas 没有 CREATE SESSION 权限; 登录被拒绝
Warning: You are no longer connected to ORACLE. -- 警告: 您不再连接到 ORACLE.
SQL>
7.3.1.3 赋予create session
(会话权限,即登陆权限)
SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB1;
Session altered.
SQL> grant create session to pandas;
Grant succeeded.
SQL>
SQL> conn pandas/pandas@PDB1;
Connected.
SQL> select * from tab;
no rows selected
SQL> 7.3.1.4 创建表无权限
SQL>
SQL> show user;
USER is "PANDAS"
SQL> create table k (id int);
create table k (id int)
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> 7.3.1.5 赋予系统权限create table
SQL>
SQL> show user;
USER is "PANDAS"
SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> grant create table to pandas;
grant create table to pandas
*
ERROR at line 1:
ORA-01917: user or role 'PANDAS' does not exist
SQL> show user;
USER is "SYS"
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB1;
Session altered.
SQL> grant create table to pandas;
Grant succeeded.
SQL> conn pandas/pandas@PDB1;
Connected.
SQL> show user;
USER is "PANDAS"
SQL> create table k (id int);
Table created.
SQL> 7.3.1.6 查看默认 TBS
SQL> select default_tablespace from user_users;
DEFAULT_TABLESPACE
--------------------------------------------------------------------------------
USERS
SQL>
7.3.1.7 若对 TBS users无权限(11g)
一般创建的账户指定的 TBS是users,在12c中resource里不再包含unlimited tablespace系统权限
pandas:
SQL> create table k (id int);
create table k (id int)
*
第 1 行出现错误:
ORA-01950: 对 TBS ‘USERS’ 无权限
7.3.1.8 赋予unlimited tablespace系统权限
sys:
SQL> grant unlimited tablespace to pandas; -- 授予klaus系统权限 unlimited tablespace,可以无限制的使用任何 TBS
SQL> alter user pandas quota 5m on users; -- 仅对于使用 users TBS加上了磁盘限额.
klaus:
SQL> create table k (id int);
Table created.
SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB1;
Session altered.
SQL> grant unlimited tablespace to pandas;
Grant succeeded.
SQL> alter user pandas 20m on users;
alter user pandas 20m on users
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> alter user pandas quota 20m on users;
User altered.
SQL>7.3.2 查看session_privs
这个语句最常用,但其中不包括当前用户的对象权限(知识点)
SQL> show user;
USER is "PANDAS"
SQL>
SQL>
SQL> select * from session_privs;
PRIVILEGE
--------------------------------------------------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
SQL> 7.3.3 列出oracle所有系统权限;
SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB1;
Session altered.
SQL> set pagesize 200 linesize 200
SQL>
SQL> select distinct privilege from dba_sys_privs;
SQL> show user;
USER is "SYS"
SQL> drop user pandas cascade;
User dropped.
SQL> create user pandas identified by pandas;
User created.
SQL> grant connect,resource to pandas;
Grant succeeded.
SQL> conn pandas/pandas@PDB1;
Connected.
SQL> set pagesize 200 linesize 200
SQL> select * from session_privs;
PRIVILEGE
------------------------------------------------------------------------------------------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SET CONTAINER
10 rows selected.
SQL> 
扫一扫
540
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
