oracle权限grant权限

  • 作者:zccc
  • 来源:网络
  • 2020-08-11 21:01:37

系统运维 oracle权限管理精英翻到结尾看备注 一:用户管理1.1:用户与模式在oracle中模式与用户是一一对应的,一个模式只能和被一个用户所拥有...

系统运维

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;

<a href=http://www.xinnet.com target=_blank>新网</a><a href=http://www.xinnet.com target=_blank>虚拟主机</a>

  • 相关专题

免责声明:本文内容由互联网用户自发贡献自行上传,本网站不拥有所有权,也不承认相关法律责任。如果您发现本社区中有涉嫌抄袭的内容,请发送邮件至:operations@xinnet.com进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。

相关文章

免费咨询获取折扣