Skip to content

dcl.md

Latest commit

 

History

History
82 lines (75 loc) · 5.65 KB

dcl.md

File metadata and controls

82 lines (75 loc) · 5.65 KB

GRANT

  • chỉ grant được 1 object tại 1 thời điểm, nếu muốn 2 object thì cần 2 câu grant 
    grant {select/update/delete} on {object/table/view/...} to {user};
    revoke {select/update/delete} on {object/table/view/...} from {user}
  • grant qua role (cần quyền CREATE ROLE privilege) 
    create role {r1}
grant {select/update/delete} to {r1}
alter user {user} default role {r1}
  • get user/schema logging in
    • sql plus: sql show user
    • apex: sql select sys_context('userenv', 'current_schema') from dual

CASCADE DELETE

  • A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in Oracle.
  • A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
  • https://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php
  • drop cả constraint đi cùng với cột
alter table .... drop colum ... cascade constraint;
  • drop table 
    DROP TABLE [schema_name].table_name
[ CASCADE CONSTRAINTS ]
[ PURGE ];
    • CASCADE CONSTRAINTS: Optional. If specified, all referential integrity constraints will be dropped as well.
    • PURGE: Optional. If specified, the table and its dependent objects will be purged from the recycle bin and you will not be able to recover the table. If not specified, the table and its dependent objects are placed in the recycle bin and can be recovered later, if needed.
    • Note: If there are referential integrity constraints on table_name and you do not specify the CASCADE CONSTRAINTS option, the DROP TABLE statement will return an error and Oracle will not drop the table.
  • cascade delete: nếu bản ghi cha bị xóa, các bản ghi con ở các bảng khác tương ứng với foreign key cũng sẽ bị xóa (khởi tạo lúc tạo bảng)
    • cú pháp lúc tạo bảng: foreign key ... ON DELETE CASCADE
  • cascade set null
    • https://www.techonthenet.com/oracle/foreign_keys/foreign_null.php
    • A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null. The records in the child table will not be deleted.
    • A foreign key with a "set null on delete" can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Definer Right và Invoker Right

  • definer right: được hiểu là quyền để thực thi lấy từ definer, tức là 1 user A tạo procedure có delete đến bảng của user C, user B gọi vào A thì chỉ cần quyền exec, mà ko cần quyền delete
    • nếu 1 user tạo 1 procedure, các user khác exec procedure này, họ chỉ cần quyền exec, còn lại các quyền khác là của người tạo procedure, và người exec ko cần quan tâm
    • keywork: AUTHID DEFINER
  • invoker right: được hiểu là quyền để thực thi lấy từ người thực thi, ví dụ 1 procedure thực hiện câu delete, thì user thực thi cần có quyền delete, không như definer right
    • tuy nhiên nếu revoke quyền delete của user A, thì procedure vẫn sẽ lỗi giống definer right -- xem lại
    • keywork: AUTHID CURRENT_USER

System privilege vs object privilege

  • system privilege: quyền thực thi action trên database system: create table, alter user,...
    • để grant, keyword: ADMIN OPTION và cần quyền GRANT ANY PRIVILEGE
  • user privilege: quyền thực thi action trên 1 object cụ thể: table, view, procedure,...
    • Các loại object privilege
      • select
      • read (with out for update)
      • insert
      • update
      • delete
      • alter
      • index
      • execute
      • reference: create foreign key
      • các loại này có thể grant riêng rẽ: grant {object_privilege} on {object_name} to user|role {user|role}, hoặc grant all thông qua GRANT ALL PRIVILEGES

WITH GRANT OPTION

  • GRANT SELECT, UPDATE ON employees TO user_b WITH GRANT OPTION;
    • In this example, user_b is granted SELECT and UPDATE privileges on the employees table. Crucially, user_b also receives the GRANT OPTION for these privileges, meaning user_b can now execute GRANT statements to give SELECT and UPDATE privileges on employees to other users.
    • If a privilege granted WITH GRANT OPTION is later revoked from the original grantee, any privileges they subsequently granted to other users based on that GRANT OPTION are also automatically revoked (unless those other users also received the same privileges from a different source). This creates a "chain" of privileges that can be severed by revoking the original WITH GRANT OPTION grant
    • If you revoke from user the privilege that you granted using the WITH GRANT OPTION keyword, you sever the chain of privileges.
    • The grantee can grant the object privilege to any user in the database, but cannot include WITH GRANT OPTION unless they have the GRANT ANY OBJECT PRIVILEGE system privilege.

Other

  • create user: create user {user} identified by {password}
  • change password: alter user {user} identified by {password}
  • Add not null constraint for existed column: ALTER TABLE orders MODIFY customer_id NOT NULL;
  • Both USER_OBJECTS and CAT views provide the same information about all objects that are owned by the user.
    • USER_OBJECTS provides detailed metadata about all objects owned by the user.
    • CAT is a simplified view that shows just the name and type of certain objects (tables, views, synonyms).