关于数据库SCHEMA用户与应用中连接用户分离的两种方法简介及评估
来源:互联网 发布:java并发什么意思 编辑:程序博客网 时间:2024/05/03 11:48
Schema Owners and Application Users
In the context of this article, the schema owner represents the Oracle user that owns all your database objects, while application users are Oracle users that need access to those schema objects.
Allowing applications to make direct connections to the schema owner is a bad idea because it gives those applications far to many privileges, which can easily result in damage to your data and the objects themselves. Instead, it is better to define application users and grant those users the necessary privileges on the schema owners objects.
This article presents two methods for achieving this separation and highlights their pros and cons. For simplicities sake I've only defined two roles, but you can define as many roles as you wish, making the security as granular as you need for each type of application user.
CURRENT_SCHEMA
Approach- Synonym Approach
CURRENT_SCHEMA
Approach
This method uses the CURRENT_SCHEMA
session attribute to automatically point application users to the correct schema.
First, we create the schema owner and an application user.
CONN sys/password AS SYSDBA-- Remove existing users and roles with the same names.DROP USER schema_owner CASCADE;DROP USER app_user CASCADE;DROP ROLE schema_rw_role;DROP ROLE schema_ro_role;-- Schema owner.CREATE USER schema_owner IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT CONNECT, CREATE TABLE TO schema_owner;-- Application user.CREATE USER app_user IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;GRANT CONNECT TO app_user;
Notice that the application user can connect, but does not have any tablespace quotas or privileges to create objects.
Next, we create some roles to allow read-write and read-only access.
CREATE ROLE schema_rw_role;CREATE ROLE schema_ro_role;
We want to give our application user read-write access to the schema objects, so we grant the relevant role.
GRANT schema_rw_role TO app_user;
We need to make sure the application user has its default schema pointing to the schema owner, so we create an AFTER LOGON trigger to do this for us.
CREATE OR REPLACE TRIGGER app_user.after_logon_trgAFTER LOGON ON app_user.SCHEMABEGIN DBMS_APPLICATION_INFO.set_module(USER, 'Initialized'); EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCHEMA_OWNER';END;/
Now we are ready to create an object in the schema owner.
CONN schema_owner/passwordCREATE TABLE test_tab ( id NUMBER, description VARCHAR2(50), CONSTRAINT test_tab_pk PRIMARY KEY (id));GRANT SELECT ON test_tab TO schema_ro_role;GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;
Notice how the privileges are granted to the relevant roles. Without this, the objects would not be visible to the application user. We now have a functioning schema owner and application user.
SQL> CONN app_user/passwordConnected.SQL> DESC test_tab Name Null? Type ----------------------------------------------------- -------- ------------------------------------ ID NOT NULL NUMBER DESCRIPTION VARCHAR2(50)SQL>
This method is ideal where the application user is simply an alternative entry point to the main schema, requiring no objects of its own. It is clean and doesn't require management of thousands of synonyms. I don't find it very useful for developers who need to make copies or modify schema objects during development.
Synonym Approach
This method relies on synonyms owned by the application user to point to the correct location of the schema objects.
First, we create the users in a similar way to the previous example.
CONN sys/password AS SYSDBA-- Remove existing users and roles with the same names.DROP USER schema_owner CASCADE;DROP USER app_user CASCADE;DROP ROLE schema_rw_role;DROP ROLE schema_ro_role;-- Schema owner.CREATE USER schema_owner IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT CONNECT, CREATE TABLE TO schema_owner;-- Application user.CREATE USER app_user IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;GRANT CONNECT, CREATE SYNONYM TO app_user;
Once again, the application user can connect, but does not have any tablespace quotas. The difference here is that the application user does have the privilege to create synonyms.
Next, we create some roles to allow read-write and read-only access and grant the read-write role to the application user.
CREATE ROLE schema_rw_role;CREATE ROLE schema_ro_role;GRANT schema_rw_role TO app_user;
Now we are ready to create an object in the schema owner in the same way we did in the previous example.
CONN schema_owner/passwordCREATE TABLE test_tab ( id NUMBER, description VARCHAR2(50), CONSTRAINT test_tab_pk PRIMARY KEY (id));GRANT SELECT ON test_tab TO schema_ro_role;GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;
If we now connect to the application user we are not able to see the object without qualifying it with a schema name. We can either proceed in this fashion, or use a synonym to point to the correct object.
SQL> CONN app_user/passwordConnected.SQL> DESC test_tabERROR:ORA-04043: object test_tab does not existSQL> DESC schema_owner.test_tab Name Null? Type ----------------------------------------------------- -------- ------------------------------------ ID NOT NULL NUMBER DESCRIPTION VARCHAR2(50)SQL> CREATE SYNONYM test_tab FOR schema_owner.test_tab;Synonym created.SQL> DESC test_tab Name Null? Type ----------------------------------------------------- -------- ------------------------------------ ID NOT NULL NUMBER DESCRIPTION VARCHAR2(50)SQL>
I find this method rather cumbersome due to the sheer number of synonyms required, especially when there are a large number of application users. Obviously, it is possible to use public synonyms, but this can be problematic when you have multiple application schemas on a single instance. I only use this method when I have developers who need to create their own schema objects for testing.
Hope this helps. Regards Tim...
Back to the Top.
- 关于数据库SCHEMA用户与应用中连接用户分离的两种方法简介及评估
- MySQL中添加用户的两种方法
- java中取得用户输入的两种方法
- 用户体验的评估与创造
- 关于数据库用户的创建与删除
- 数据库中schema与catalog简介
- 用户、会话及事务控制-------数据库会话与连接
- MySQL添加用户的两种方法
- 修改用户的 schema
- Linux和Mysql创建用户及授权方法与C连接Mysql的方法
- Linux和Mysql创建用户及授权方法与C连接Mysql的方法
- c#中连接数据库关于sqlconnection的用户sa无法登录的问题
- 【翻译自mos文章】改变数据库用户sysman(该用户是DB Control Repository 的schema)密码的方法
- 要实现读写分离需要根据用户的操作连接不同的数据库
- 在web.config中连接数据库的两种方法
- Mysql中通过processlist方式查询Mysql数据库当前连接的用户及会话信息
- 停止数据库的用户连接
- (SqlServer)分离所有用户数据库
- c语言版数据结构(奇迹冬瓜)-数组和广义表(十字链表存贮稀疏矩阵的行列值)
- .技术债务真正的代价
- Java性能(4) Vector, ArrayList, LinkedList
- 《使用OpenCV开发机器视觉项目》&之一卡通画与皮肤变色之初探
- Java中的SPI(Service Provider Interface)介绍及示例
- 关于数据库SCHEMA用户与应用中连接用户分离的两种方法简介及评估
- 谷歌 2012 年终总结:这一年你又好奇着什么?
- 经典排序算法 – 插入排序
- 深入理解JVM内幕:从基本结构到Java 7新特性
- 软件架构师是什么玩意儿?
- SAP中如何查看BOM的修改记录呢?
- 设备驱动之二----字符设备驱动
- jQuery插件: formValidator 表单验证插件
- 移动浏览器的“隐形危机”