Oracle management - tablespace permission control

One 、 to grant authorization

When building users, you can usually choose one default tablespace, However, the user cannot write data to the tablespace without authorization , Authorization is needed . Authorization includes global authorization and approval quota Limit two situations （quota Quotas can prevent a user from using too much space in a table space ）
1. Grant permission to a user's global tablespace （ Grant... Separately by command ）

grant unlimited tablespace to username;

2. Grant permission to a user's global tablespace （ Grant... By role ）

When granted by role, it is usually granted resource role .
Be careful ： although resource The character doesn't bring unlimited tablespace Authority , But when granting this role to users ,unlimited tablespace The permission will be brought automatically
1） see resource The permissions under the role , No, unlimited tablespace jurisdiction

SQL> SELECT * from Dba_Sys_Privs s WHERE s.grantee='RESOURCE';

GRANTEE                                                      PRIVILEGE                                                                ADMIN_
------------------------------------------------------------ -------------------------------------------------------------------------------- ------
RESOURCE                                                     CREATE TRIGGER                                                           NO
RESOURCE                                                     CREATE SEQUENCE                                                          NO
RESOURCE                                                     CREATE TYPE                                                              NO
RESOURCE                                                     CREATE PROCEDURE                                                         NO
RESOURCE                                                     CREATE CLUSTER                                                           NO
RESOURCE                                                     CREATE OPERATOR                                                          NO
RESOURCE                                                     CREATE INDEXTYPE                                                         NO
RESOURCE                                                     CREATE TABLE                                                             NO

2） hold resource The role grants the user

SQL> grant resource to username;

Grant succeeded.

3） View the permissions the user has , You can find yourself with unlimited tablespace（ In addition, you can go to see dba_ts_quotas, Such authorized users are not reflected ）

SQL> SELECT * from Dba_Sys_Privs s WHERE s.grantee='USERNAME' ;

GRANTEE                                                      PRIVILEGE                                                                ADMIN_OPTION
------------------------------------------------------------ -------------------------------------------------------------------------------- ------
USERNAME                                                       UNLIMITED TABLESPACE                                                     NO
USERNAME                                                       CREATE TABLESPACE                                                        NO
USERNAME                                                       CREATE SESSION                                                           NO

3. Grant a user permission to an individual tablespace

1） Grant a user full access to a tablespace

ALTER USER username QUOTA UNLIMITED ON rcat 

--- Special , After granting permission, view dba_ts_quotas surface ：
SQL>   SELECT * from Dba_Ts_Quotas z WHERE z.username='USERNAME';

TABLESPACE_NAME                                              USERNAME                                                  BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DROPPE
------------------------------------------------------------ ------------------------------------------------------------ ---------- ---------- ---------- ---------- ------
RCAT                                                         USERNAME                                                   524288         -1         64         -1 NO
 here max_bytes  by -1, That is, unlimited .

2） Grant a user limited access to a tablespace

ALTER USER username QUOTA 1M ON rcat; 
SQL>   SELECT * from Dba_Ts_Quotas z WHERE z.username='USERNAME';

TABLESPACE_NAME                                              USERNAME                                                  BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DROPPE
------------------------------------------------------------ ------------------------------------------------------------ ---------- ---------- ---------- ---------- ------
RCAT                                                         USERNAME                                                   524288    1048576         64        128 NO
--- You can see MAX_BYTES yes 1MB

Two 、 Recycling permissions
1. overall situation

revoke unlimited tablespace from username;

2. Individual tablespaces

ALTER USER username  QUOTA 0 ON rcat