oracle sql用户,ORACLE SQL 管理用户

第十三章:管理用户

用户的管理体现在两方面：

1.资源

2.权限

管理员才可以创建用户:

conn / as sysdba

conn system/oracle

创建数据库审核的用户:

create user test

identified by test --初始口令

default tablespace users --存储对象的默认表空间

temporary tablespace temp --用户作排序时使用的表空间

quota 10m on users --test用户在users表空间的空间限额

password expire; --初始口令过期，会提示用户重置口令

创建操作系统审核的用户:

SQL> show parameter os_authent_prefix

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

os_authent_prefix string ops$

SQL> select osuser from v$session where username is not null;

OSUSER

------------------------------

oracle

create user ops$oracle

identified externally;

本地匿名，远程带口令

create user ops$oracle

identified by pwd123;

对用户授予系统权限:

grant create session,create table to u1;

查看用户被授予的系统权限:

conn / as sysdba

select * from DBA_SYS_PRIVS where GRANTEE='U1';

conn u1/u1

select * from session_privs;

对用户授予对象权限:

conn scott/tiger

grant select on emp to u1;

查看用户被授予的对象权限:

col GRANTEE for a15

col PRIVILEGE for a20

col owner for a15

SELECT GRANTEE,PRIVILEGE,OWNER,TABLE_NAME

FROM DBA_TAB_PRIVS

WHERE GRANTEE='U1';

select OWNER,TABLE_NAME,PRIVILEGE,COLUMN_NAME from dba_col_privs where GRANTEE='U1';

授权动作的扩展选项:

create user u2 identified by u2;

conn / as sysdba

grant create session to u1 with admin option;

grant select on scott.emp to u1 with grant option;

conn u1/u1

grant create session to u2;

grant select on scott.emp to u2;

with admin option VS with grant option

权限回收无级联 VS 权限回收有级联

查看用户的空间使用份额:

select * from dba_ts_quotas where username='U1';

修改用户的空间使用份额:

alter user U1 quota 20m on users;

alter user U1 quota 0 on users;

修改用户口令:

alter user u1 identified by pwd123;

权限的回收:

revoke create session,create table from test;

revoke select on scott.emp from test;

角色:一组权限的逻辑集合

create role r1;

grant create session to r1;

create role r2;

grant create table to r2;

grant r2 to r1;

create role r3;

grant create view to r3;

将角色授予用户:

grant r1,r3 to u1;

用户被授予的角色:

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='U1';

角色被授予的角色:

SELECT * FROM ROLE_ROLE_PRIVS WHERE ROLE='R1';

角色被授予的系统权限:

select * from ROLE_SYS_PRIVS WHERE ROLE='CONNECT';

角色被授予的对象权限:

select * from ROLE_TAB_PRIVS WHERE ROLE='R1';

conn / as sysdba

alter user u1 default role all except r3;

conn u1/u1

set role all;

删除用户:

drop user u1 cascade;