oracle权限grant权限

系统运维

oracle权限管理
精英翻到结尾看备注

一：用户管理
1.1：用户与模式
在oracle中模式与用户是一一对应的，一个模式只能和被一个用户所拥有且名称和该用户一样；当用户访问自己模式中的对象时不需要指定模式名，如：scott用户访问自己的EMP表时SELECT * from emp不需要加模式名，经过授权的用户还可以访问其他模式的对象，访问时要在对象名的前面加上模式名。
1.2：创建新用户
要连接到Oracle数据库，就需要创建一个用户帐户，每个用户都有一个默认表空间和一个临时表空间
拥有CREATE user 权限的用户可以为数据库创建新的用户
col 字段名 for a10 //控制该字段长度
set timing on //开启时间计时下一步的动作
创建用户语法：
CREATE user user_name
IDENTIFIED [BY userpwd|EXTERNALLY|GLOBALLY|]
[DEFAULT TABLESPACE default_tablespace ]
[TEMPORARY TABLESPACE temp_tablespace]
[QUOTA size |UNLIMITED ON tablespace_name]
[PASSWORD EXPIRE]
[ACCOUNT LOCK|UNLOCK]
//user_name要创建的数据库用户的名称，不能与现有的用户名重复
//IDENTIFIED 用户指定数据库用户的认证方式，password要创建用户的密码，即密码认证方式，EXTERNALLY表示要使用外部认证方式，GLOBALLY表示要使用全局认证方式。
//DEFAULT TABLESPACE 指定创建用户的默认表空间，以后创建的对象存储在默认表空间内，默认表空间为USERS表空间。
//TEMPORARY TABLESPACE 指定用户的临时表空间，当会话期间执行的操作超过了PGA的可用空间，就会使用临时表空间，默认临时表空间为TEMP表空间。
//QUOTA限制用户在某个表空间中使用的存储空间大小，默认在表空间上的配额为0，用户不能使用任何空间存储任何对象，不能为用户指定临时表空间的配额。
//PASSWORD EXPIRE 指定用户在第一次登录时对密码进行修改
//ACCOUNT 指定用户的锁定状态，默认为unlock。
创建用户后，oracle数据库会自动创建一个相同名称的模式，该用户创建的对象都会保存该模式中。

创建用户user1设置密码123，其他默认
查询dateabase_properties视图，了解数据库当前的默认表空间和临时表空间
SELECT property_name,property_value from database_properties
where property_name like \'DEFAULT%\'

--查询用户user1的默认表空间和临时表空间，使用的是数据库当前默认表空间和临时表空间

1.3创建表空间
CREATE TABLESPACE test DATAFILE \'test.dbf\' SIZE 10M;//创建表空间test
CREATE TEMPORARY TABLESPACE test_temp TEMPFILE \'test_temp.dbf\' SIZE 10M;//创建临时表空间test_temp

-创建用户user2指定默认表空间test，临时表空间test_temp，配额无限制。
CREATE user user2 IDENTIFIED BY 123
DEFAULT TABLESPACE test TEMPORARY TABLESPACE test_temp
QUOTA UNLIMITED ON test;

用户user2可以使用表空间上任意大小的存储数据对象，使用视图dba_ts_quota可以查看用户被分配的表空间配额情况，user1没有被分配，所以查不到记录。
1.4：修改用户
语法：
-- ALTER user user1 ACCOUNT LOCK;//将用户user1锁定
-- ALTER user user1 PASSWORD EXPIRE ACCOUNT UNLOCK;//解锁user1并要求登录时改密码
-- ALTER user user1 DEFAULT TABLESPACE test;//修改用户的默认表空间test
-- ALTER user user1 QUOTA 5M ON test ;//修改用户user1的配额为5M
1.5：删除用户
DROP user user1 [cascade] //删除user1用户
//当用户没有创建数据对象时，可以直接删除，如果用户创建了对象，要使用关键字cascade进行级联删除用户创建的数据对象，会先删除数据再删除用户。当用户正在登陆时是不能删除用户的，强制删除的话，需要先通过视图V$SESSION查询该用户的会话ID等信息，然后使用ALTER SYSTEM KILL SESSION语句杀掉当前用户的会话，最后使用DROP USER删除用户。




1.6通过数据字典查询用户信息
1.//user_users:显示当前登录用户详细信息，包括用户名、用户ID、帐号状态、默认表空间、临时表空间
2.//all_users:显示可以被当前登录的用户所访问的用户，只包括用户名、用户ID、用户创建的时间
3.//dba_users:显示数据库中所有的用户信息，包括用户名、用户ID、帐号状态、密码、锁定日期、失效日期、默认表空间临时表空间。

二：权限管理

此视图可以找到所需的权限。
以sysdba的身份登录，登录后用户是SYS，SYS用户除了拥有SYSDBA系统权限外，还拥有DBA等角色的权限；和拥有sysdba系统权限有很大的区别，普通用户即使拥有了sysdba权限可能连数据库都登录不了。

2.1：权限有两种类型，系统权限和对象权限
系统权限允许用户执行某些数据库操作，如创建表就是一个系统权限
对象权限允许用户对数据库对象（如表、视图、序列等）执行特定操作
2.2：管理员授予普通用户权限
语法：
GRANT sys_priv[,sys_priv]
TO user[,user ]|role |PUBLIC
[WITH ADMIN OPTION]
//sys_priv表示将要授予的系统权限，可以同时授予多个系统权限，他们之间用逗号隔开
//user表示要授予系统权限的用户，role要授予权限的角色，PUBLIC表示PUBLIC用户组，可以同时给多个用户属于系统权限，中间用逗号隔开。
//WITH ADMIN OPTION是否允许被授权的用户将获取的权限转授给其他用户，默认不能授予其他用户
2.3系统权限，授予系统权限
1.授予用户连接数据库权限并创建数据库对象: GRANT CONNECT TO tom;
2.允许用户连接数据库：GRANT CREATE SESSION TO tom
2.建立表的系统权限: GRANT CREATE TABLE TO tom ;
3.创建profile文件：CREATE PROFILE pro1 LIMIT
4.修改用户的profile文件：ALTER user test1 PROFILE pro1;
5.允许用户使用users表10M空间配额:ALTER user tom QUOTA 10M ON USERS;
6.Tom用户任意使用表空间：GRANT UNLIMITED TABLESPACE TO tom;
7.收回系统权限revoke：REVOKE create session from tom;
2.4对象权限，授予对象权限
1.对象权限：访问某个用户的特定数据对象(如表、视图等)的对象权限(SELECT on scott.student)
常用的对象权限：
1.select权限：执行查询操作，能够执行的对象是表，视图和序列
2.insert权限：执行插入数据操作，能够执行的对象是表和视图
3.update权限：执行更新数据操作，能够执行的对象是表和视图
4.alter权限：执行修改对象的操作，能够执行的对象是表和序列
5.delete权限：执行删除数据的操作，能够执行的对象是表和视图
6.index权限：执行创建索引操作，能够执行的对象是表
7.references权限：执行外键引用的操作，能够执行的对象是表
8.execute权限：执行对象操作，能够执行的对象是函数，过程和包。
语法：
GRANT object_privilege [column_name…]
ON obj_name
TO user [, user|role, PUBLIC…]
[WITH GRANT OPTION];
//obj_priv表示将要授予的对象权限，可以同时授予多个对象权限，他们之间用逗号隔开
//column_name表示要授予操作对象的列名，多个列名之间用逗号隔开
//obj_name表示对象权限操作的对象
//user表示要授予对象权限的用户，role要授予对象权限的角色，PUBLIC表示PUBLIC用户组，可以同时给多个用户属于对象权限，中间用逗号隔开。
//WITH ADMIN OPTION是否允许被授权的用户将获取的权限转授给其他用户，默认不能授予其他用户
收回对象权限REVOKE：
REVOKE privilege ON object from {user|role|PUBLIC};
例：revoke SELECT on scott.student from test2
1.GRANT SELECT ON SCOTT.EMP TO tom WITH GRANT OPTION;//允许tom用户查询EMP表的记录并且可以将获得的权限授予其他用户
2.GRANT UPDATE ON SCOTT.EMP TO tom；允许用户更新EMP表的记录
3.GRANT ALL ON SCOTT.EMP TO tom；允许用户插入、删除、更新、查询表记录
4.GRANT UPDATE(ename,sal)ON SCOTT.EMP TO tom;精确到允许用户更新EMP中的ename,sal列

三：角色管理
角色管理：角色是一组相关权限的组合，可以将权限授予角色，再把角色授予用户，以简化权限管理。一个用户可以拥有一个或多个角色，并可以将角色授予其他用户或角色，无论在角色中增加或减少权限，该用户的权限都会随之变化。
3.1创建角色CREATE ROLE,应该具有CREATE ROLE系统权限。
CREATE ROLE role_name[NOT IDENTIFIEN]|[IDENTIFIED {BY password|USING [scheam.]package |EXTERNALLY|GLOBALLY}]
//role_name 表示要创建的角色的名称
//NOT IDENTIFIED 表示角色被授予其他用户和角色后立刻生效，这是默认值
//IDENTIFIED子句表示被授予其他用户后不会立刻生效，以防止其他用户随意启用和禁用该角色。BY password表示创建角色的同时为角色设置密码，用户不能立即拥有该角色权限，必须使用SET ROLE语句激活后才拥有；默认是没有密码，用户可以立即拥有该角色权限。USING package表示创建的角色是一个应用程序角色，该角色只能在应用程序中使用授权的包来启用。EXTERNALLY 表示要使用外部服务授权来启用该角色。GLOBALLY表示通过企业服务授权的用户来启用该角色。

SCOTT用户创建不成功是因为没有权限

SYSDBA给SCOTT用户创建角色的权限

SCOTT用户创建3个角色

3.2 授予角色权限，可以是系统权限或者对象权限。
GRANT privilege TO ROLE;
例： GRANT create session ,create any table, drop any table TO role1;
为角色e_emp1授予scott模式的EMP表的查询权限和在任何模式下创建，删除视图的系统权限。为角色e_emp2授予scott模式的EMP表的插入、更新和删除的权限。为角色m_emp授予scott模式下的DEPT表的查询、插入、更新、删除权限。


3.3 将角色授予用户:
GRANT role TO user;
例： grant role1 to test2;

3.4查看用户被直接授予的角色信息
查询视图user_role_privs可以查看用户被直接授予的角色的信息。

在用户user1中查看user1用户的角色。
由上图可以看出，角色e_emp1被自动设置为用户user1的默认角色，而M_EMP被设置了密码因此不能自动成为默认角色。对于用户user1只能查询scott模式下EMP表的信息，不能查看DEPT表的信息。

用户user1可以查看EMP表

不能查看dept表
3.5修改默认角色
ALTER user DEFAULT ROLE [role_name |ALL [EXCEPT role_name] |NONE]
//role_name为要设置的角色名，多个用逗号隔开
//ALL启用当前用户的所有角色作为默认角色，但不包括设置密码的角色。
//EXCEPT除了指定的角色外，启用当前用户所有角色作为默认角色
//NONE禁用当前用户的所有角色，即默认角色为零个。
ALTER user user1 DEFAULT ROLE NONE; //修改用户默认角色为0个，登录后不具有任何角色。
ALTER user user1 DEFAULT ROLE ALL EXCEPT e_emp1;//user1登录后启用除e_emp1角色外，其他所有角色为默认角色
ALTER user user1 DEFAULT ROLE ALL;//user1登录后启用所有角色为默认角色
ALTER user user1 DEFAULT ROLE e_emp1;//user1登录后只启用e_emp1角色为默认角色。
3.6启用和禁用角色set
SET ROLE [role_name [IDENTIFIED BY password|ALL [EXCEPT role_name] |NONE]
//role_name为要设置的角色名，多个用逗号隔开
//IDENTIFIED BY 用于启用或禁用角色时要使用的密码
//ALL启用当前用户的所有角色作为默认角色，但不包括设置密码的角色。
//EXCEPT除了指定的角色外，启用当前用户所有角色作为默认角色
//NONE禁用当前用户的所有角色，即默认角色为零个。
SET ROLE m_emp IDENTIFIED BY 123; //启用m_emp角色的同时输入密码

在user1用户中启用

并查看

3.7 从用户收回角色:
REVOKE role1 from user2;//从用户user2中回收role1角色
REVOKE CREATE ANY VIEW,DROP ANY VIEW from e_emp1; //回收e_emp1角色的系统权限
3.8删除角色
DROP ROLE e_emp1
（5）GRANT RESOURCE TO tom：RESOURCE角色允许用户使用数据库中的存储空间（系统自带角色）

备注：
oracle查询不用输入前面的用户名(a.b不用输入a了)
可以使用下面四种方法：空间资源充足或者表数据量小可以使用
1.使用同意词
grant CREATE SYNONYM to scott; //DBA或者有权限的用户授权
create synonym emp_test for scott.emp;
2.使用视图
create view emp_test as SELECT from scott.emp;
3.账户授权
grant SELECT on scott to test; //test用户可以查看scott用户的表
4.创建数据库
create table emp_test1 as SELECT from scott.emp

给用户赋权限
grant SELECT on DEMO_INVOICE_CLOUD.FMB_INVOICE to demo_pc_core01;
GRANT connect, resource TO test;
GRANT RESUMABLE TO TEST; --可以自动扩展空间
GRANT BACKUP ANY TABLE TO TEST;
GRANT EXECUTE ANY TYPE TO TEST;
GRANT SELECT ANY TABLE TO TEST;
GRANT READ ANY FILE GROUP TO TEST;
GRANT SELECT ANY SEQUENCE TO TEST;
GRANT EXECUTE ANY PROCEDURE TO TEST;
connect 用户能登录数据库的权限
resource 用户能创建一些数据库对像的权限，表、视图，存储过程，一般是授予开发人员的
drop user cmsuser cascade;
使用cascade参数可以删除该用户的全部objects

connect 用户能登录数据库的权限
resource 用户能创建一些数据库对像的权限，表、视图，存储过程，一般是授予开发人员的
drop user cmsuser cascade;
使用cascade参数可以删除该用户的全部objects

rowid是Oracle数据库中行标识符，为rowid,它是一个18位数字，以64为基数，该徝包含了该行在oracle数据库中的物理位置，查询rowid如下：
SQL> SELECT rowid,id from test Where rownum< 5;
删除表中重复记录
Delete from test a
Where rowid < (SELECT Max(rowid)from test Where Id = a.Id);

将一个用户下面的所有表的权限授予另一个用户
1.查看出该用户下面使用的表
SELECT * from dba_tables where owner=\'SCOTT\' and status=\'VALID\'
2.将该用户下面的表拼接成授权语句
SELECT \'grant select,delete,update,insert \'|| table_name||\' to test ;\' from dba_tables where owner=\'SCOTT\' and status=\'VALID\'
3.将步骤2中的输出结果用dba或者有授权权限的用户执行，当前用户如有授权权限则可以执行。

注意：如果需要将当前登录用户数据库中所有表授权给test用户可以使用下面语句进行拼接
SELECT \'grant select,delete,update,insert \'|| table_name||\' to test ;\' from user_tables where owner=\'SCOTT\' and status=\'VALID\'
将除sys外所有的表给PC_CORE01用户查看
grant select any table to PC_CORE01;