oracle数据库常用SQL语句以及角色传递

一、本地oracle11g数据库以下用户密码统一修改为：sysorcl；

本地使用DBA权限远程连接ORACLE数据库

以sys用户登陆oracle，执行select * from V_$PWFILE_USERS;可查询到具有sysdba权限的用户。

sys/change_on_install       SYSDBA 或 SYSOPER        不能以 NORMAL 登录，可作为默认的系统管理员

system/manager               SYSDBA 或 NORMAL        可作为默认的系统管理员，不能以 SYSOPER 登录，只能以normal登陆，除非你对它授予了sysdba的系统权限或者sysoper系统权限。

sysman/oem_temp（sysman）             sysman                            为 oms 的用户名

scott/tiger                        NORMAL                            普通用户

aqadm /aqadm                SYSDBA 或 NORMAL        高级队列管理员

Dbsnmp/dbsnmp           SYSDBA 或 NORMAL           复制管理员

select * from dba_roles;--oralce数据库全部角色,具备dba角色的用户才可以查询，如sys,system用户等）

select * from dba_col_privs;--查询用户具有的列权限,,具备dba角色的用户才可以查询，如sys,system用户等）

select * from dict where comments like '%grant%';--查询当前用户可以访问的所有数据字典视图

select * from system_privilege_map order by name;--查询oracle数据库中所有的系统权限,具备dba角色的用户才可以查询，如sys,system用户等）

 

select * from session_roles;         --查询当前用户具有的角色

select * from user_role_privs;  --查询当前用户具有的角色

select * from user_sys_privs; --查询当前用户具有的权限（系统权限，对数据库登录，创建表，使用表空间等权限）

select * from user_tab_privs; --查询当前用户具有的权限（对象权限，对其他用户表或视图等权限）

--查询某个用户具有的角色（用户名必须大写，具备dba角色的用户才可以查询，如sys,system用户等）

select * from dba_role_privs t where t.grantee='MER_BAMS';

-查询某个用户或者角色具有的权限（系统权限，用户名或者角色名必须大写，具备dba角色的用户才可以查询，如sys,system用户等）

select * from dba_sys_privs t where t.grantee in ('MER_BAMS','RESOURCE');

select * from role_sys_privs t where t.role in ('MER_BAMS','RESOURCE');

--查询某个用户具有的权限（对象权限，用户名或者角色名必须大写，具备dba角色的用户才可以查询，如sys,system用户等）

select * from dba_tab_privs t where t.GRANTEE='MER_BAMS';

select ROLE, PRIVILEGE from role_sys_privs where role='DBA';--查询BDA的权限

 

create user username identified by userpassword;--创建用户

alter user username identified by userpassword;--修改用户密码

alter user username default tablespace user_space;--修改用户的默认表空间

alter user username temporary tablespace user_space_temp;--修改用户的临时表空间

select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;--查看所有表空间总大小

select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;--查看所有表空间已使用的大小

create user username identified by username default tablespace user_space;

temporary tablespace user_space_temp;--创建用户并指定默认表空间和临时表空间

--默认情况下用户创建好之后系统会默认给用户分配一个默认的表空间（users）和临时表空间（temp）

--以dba角色登录可以查询用户mer_bams的是使用的默认表空间和临时表空间

select username,default_tablespace,temporary_tablespace from dba_users where username='MER_BAMS';

alter user username account unlock;--解锁用户

drop user username cascade;--级联删除用户（加上cascade关键字，即删除该用户下所有，谨慎操e作）

--修改表空间物理存储文件bdyd自动扩展50M,最大为4000M（可以设置为最大无限制）

alter database datafile 'D:\oracle\11.1.0\oradata\bdyd' autoextend no next 50m maxsize 4000m;

drop tablespace userspace including contents and datafiles;--删除表空间userspace包括物理文件

select * from V$PWFILE_USERS;--查看系统DBA用户（PWDorcl.ora文件）

select * from dba_users; --查询当前数据库的所有用户

select * from dba_roles; --查询当前数据库的所有角色

select * from v$instance;--查询数据库的实例名

select * from v$database;--查询数据库的名称

select * from global_name;--查询数据库的全称

select * from v$version; --查询数据库的版本号

--命令导出表,加入tables=(emp,dept),可导出指定的表,加入rows=n则只导出表结构

exp userid=username/userpassword@127.0.0.1:1521/orcl file=D:/username.dmp;

imp userid=username/userpassword@127.0.0.1:1521/orcl file=D:/username.dmp full=y;--命令导入表

create table tablename_bak as select * from tablename;--创建备份表包括表结构和数据（很实用）

create table tablename_bak as select * from tablename where 1=2;--创建备份表只有表结构（很实用）

create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from scott.emp;--用查询结构创建新表（很实用）

insert into student(s_no,s_name,s_sex,s_birthday,s_sal) select * from student;--表数据的自我复制

alter table tablename add(tablecolumn varchar2(32) default null null);--添加表字段

comment on column tablename.tablecolumn is '备注信息';--为刚创建的字段添加备注

alter table tablename rename column tablecolumn to new_tablecolumn;--修改表字段名称

alter table tablename modify(tablecolumn varchar2(32));--修改表字段长度

alter table tablename drop column tablecolumn;--删除表字段

rename tablename to new_tablename;--修改表的名称

drop table tablename;--删除表

--修改oracle数据的日期格式(oracle默认的日期格式是'DD-MON-YY','09-6月-99',1999年6月9号)

alter session set nls_date_format = 'yyyy-mm-dd';

select * from all_table t where t.table_name='TABLE_NAME' and t.owner='USER_NAME';--查询用户USER_NAME下表TABLE_NAME的信息

 

二、权限或角色的传递概念（角色也可以传递）

对象权限是：用户对其他用户的数据对象（表，视图，存储过程等）的操作权限

系统权限是：用户对数据库的相关权限，如登录数据库，使用表空间，查询存储过程，创建表，更新表等。

如果是对象权限就在后面加入with grant option;

    例子描述：SYS用户加上with grant option方式赋予A用户查询SCOTT.EMP表的权限，

            以用户SYS用户登录，加上with grant option方式赋予A用户查询SCOTT.EMP表的权限

                SYS用户：grant select on scott.emp to a with grant option；

            以用户A用户登录，加上with grant option方式赋予B用户查询SCOTT.EMP表的权限

                A用户： grant select on scott.emp to bwith grant option；--如果没有加with grant option，则在用户B处权限停止传递

            以用户A登录不可以自己撤销自己对SCOTT.EMP表的查询权限:revoke select on scott.emp from a;--不可以撤销

            如果用户SYS用户撤销A用户查询SCOTT.EMP表的权限：revoke select on scott.emp from a;

            则B用户也没有了查询SCOTT.EMP表的权限，此种方式是级联撤销。

    注意的是：对象权限会级联撤销，但是当前用户不可以撤销自己的权限，查看标识USER_TBA_PRIVS表中GRANTTABLE=YES，则为可传递权限。

如果是系统权限就在后面加入with admin option;

    例子描述：SYS用户加上with admin option方式赋予A用户CONNECT权限(其实是CONNECT角色，即CREATE SESSION权限，表示登录数据库权限)，

            以用户SYS用户登录，加上with admin option方式赋予A用户CONNECT权限

                SYS用户：grant connect to a with admin option；

            以用户A用户登录，加上with admin option方式赋予B用户CONNECT权限

                A用户: grant connecton scott.emp to b；--如果没有加with grant option，在用户B处权限停止传递

            以用户A登录可以自己撤销自己的CONNECT权限：revoke connect from a;--可以撤销

            如果用户SYS用户撤销A用户CONNECT权限：revoke connect from a;

            而B用户仍然有CONNECT权限，并没有级联撤销，CONNECT权限在用户B处任然可以传递。

    注意的是：系统权限不会级联撤销，但是当前用户可以撤销自己的权限，查看标识USER_ROLE_PRIVS表中ADMIN_OPRION=YES，则为可传递权限或角色。