Oracle基本操作笔试面试题之用户、角色、权限管理

用户创建、修改、删除

用户创建

create user username profile default identified by “user_passwd” default tablespace

tablespace_name account unlock;

修改用户密码：

alter user username identified by char_name；

修改用户使用状态

alter user username account unlock

用户赋权限：

grant connect,resource,select any table,update any table,delete any table,insert any

table,select

any dictionary,create any procedure,execute any procedure,create any TRIGGER,create

any view, unlimited tablespace，

drop any view,create any sequence,select any sequence,drop any sequence,CREATE

DATABASE LINK,

CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK,CREATE

ANY synonym,DROP ANY synonym,

CREATE PUBLIC synonym,DROP PUBLIC SYNONYM,SELECT_CATALOG_ROLE

to &u;

给某一个用户赋予某张表的某种权限：

grant privilege_name（select insert update） table_name to username;

例如：

grant update ON TABLE_NAME TO username;

grant delete ON TABLE_NAME TO username;

grant insert ON TABLE_NAME TO username;

用户权限回收：

revoke role_name from username;

alter user username default role all;

用户删除：

Drop user username cascade （如果有数据，带 cascade 参数）

查看用户属性和状态：

select * from dba_users;

select * from dba_sys_privs

select * from dba_tab_privs

权限名称：

administer database trigger

administer resource manager

alter any cluster

alter any dimension

alter any evaluation context

alter any index

alter any indextype

alter any library

alter any outline

alter any procedure

alter any role

alter any rule

alter any rule set

alter any sequence

alter any snapshot

alter any table

alter any trigger

alter any type

alter database

alter profile

alter resource cost

alter rollback segment

alter session

alter system

alter tablespace

alter user

analyze any

audit any

audit system

backup any table

debug any procedure

debug connect session

delete any table

dequeue any queue

drop any cluster

drop any context

drop any dimension

drop any directory

drop any evaluation context

drop any index

drop any indextype

drop any library

drop any operator

drop any outline

drop any procedure

drop any role

drop any rule

drop any rule set

drop any sequence

drop any snapshot

drop any synonym

drop any table

drop any trigger

drop any type

drop any view

drop profile

drop public database link

drop public synonym

drop rollback segment

drop tablespace

become user

comment any table

create any cluster

create any context

create any dimension

create any directory

create any evaluation context

create any index

create any indextype

create any library

create any operator

create any outline

create any procedure

create any rule

create any rule set

create any sequence

create any snapshot

create any synonym

create any table

create any trigger

create any type

create any view

create cluster

create database link

create dimension

create evaluation context

create indextype

create library

create operator

create procedure

create profile

create public database link

create public synonym

create role

create rollback segment

create rule

create rule set

create sequence

create session

create user

create view

drop user

enqueue any queue

execute any evaluation context

execute any indextype

execute any library

execute any operator

execute any procedure

execute any rule

execute any rule set

execute any type

exempt access policy

flashback any table

force any transaction

force transaction

global query rewrite

grant any object privilege

grant any privilege

grant any role

insert any table

lock any table

manage any queue

manage tablespace

on commit refresh

query rewrite

restricted session

resumable

select any dictionary

select any sequence

select any table

under any table

under any type

under any view

unlimited tablespace

update any table

create snapshot

create synonym

create table

create tablespace

create trigger

create type

2、角色创建、修改、删除

角色创建

create role role_name;

角色权限修改

grant privilege_name to role_name

revoke privilege_name from role_name

角色删除

drop role role_name

系统角色：

AQ_ADMINISTRATOR_ROLE

AQ_USER_ROLE

AUTHENTICATEDUSER

CONNECT

CTXAPP

DBA

DELETE_CATALOG_ROLE

EJBCLIENT

EXECUTE_CATALOG_ROLE

EXP_FULL_DATABASE

GATHER_SYSTEM_STATISTICS

HS_ADMIN_ROLE

WKUSER

WM_ADMIN_ROLE

XDBADMIN

3、权限增加和收回

IMP_FULL_DATABASE

JAVADEBUGPRIV

JAVAIDPRIV

JAVASYSPRIV

JAVAUSERPRIV

JAVA_ADMIN

JAVA_DEPLOY

LOGSTDBY_ADMINISTRATOR

OEM_MONITOR

RECOVERY_CATALOG_OWNER

RESOURCE

SALES_HISTORY_ROLE

SELECT_CATALOG_ROLE