Oracle数据库安全性 - 仅插入特定值

I'm starting to explore some capabilities of Oracle Database Security (11G), and I have created a small fictitious banking database. One of the tables is "TRANSACTIONS" with columns such as:

我开始探索Oracle数据库安全(11G)的一些功能,并且我创建了一个虚构的小型银行数据库。其中一个表是“TRANSACTIONS”,其列如下:

• TRANS_ID
• DATE
• DESCRIPTION
• TYPE etc.

I'm going to be creating users such as Customers and Bank Tellers, but I want to limit what they can do. The question I have is: Can I allow a user to insert data into the TRANSACTIONS table, but only if it's of a specific type, or has a specific description?

我将创建用户和银行柜员等用户,但我想限制他们可以做的事情。我的问题是:我是否可以允许用户将数据插入TRANSACTIONS表,但仅限于特定类型或具有特定描述?

For instance, I want my Bank Teller user to be able to insert a "FEE REVERSAL" and a Withdrawal or Deposit only. Is this as simple as creating a role such as:

例如,我希望我的银行柜员用户能够插入“FEE REVERSAL”和仅提取或存款。这是否像创建角色一样简单:

CREATE ROLE TELLER_ROLE IDENTIFIED BY TELLER;
GRANT UPDATE ON TRANSACTIONS TO TELLER_ROLE
WHERE TRANSACTION.TRANSACTION_TYPE = 'FEE REVERSAL';

Or should I go about this a different way?

或者我应该以不同的方式解决这个问题?

1 个解决方案

#1

Assigning ROLES to users is very important to limit what they can do within the database but I feel you are looking more for data constraints to be enforced. Depending on what you are using for a front-end you can limit their options or even utilize stored procedures to only pass certain values but to enforce the data integrity at the database level you will want to use CONSTRAINTS. Example below;

将ROLES分配给用户对于限制他们在数据库中可以执行的操作非常重要,但我觉得您正在寻找更多要强制执行的数据约束。根据您用于前端的内容,您可以限制其选项,甚至利用存储过程仅传递某些值,但要在数据库级别强制执行数据完整性,您将需要使用CONSTRAINTS。以下示例;

ALTER TABLE transactions
ADD CONSTRAINT check_teller_transaction
  CHECK (record_action IN ('FEE REVERSAL', 'WITHDRAWAL', 'DEPOSIT'));