Precautions for creating users and allocating space

Users are mainly concerned with the following aspects:
1. authentication method, such as password and external authentication. Usually Password
2. Assign and revoke permissions. Generally, assign the connect resource role to the user.
Revoke the default unlimited tablespace permission to avoid unlimited use of the default user tablespace.
Permissions required for other applications
3 space allocation generally creates Independent Application-related tablespace for each user
After creating a tablespace, grant the user unlimited tablespace permission on tablespace to use all the space.
4. default tablespace and temporary tablespace are allocated to users.


Related views:
Dba_sys_privs
Dba_ts_quotas;
Dba_tablespaces;
Dba_users;



1. authentication method, such as password and external authentication. Usually Password
SYS:
Create user James identified by James


2. Assign and revoke permissions. Generally, assign the connect resource role to the user.
Revoke the default unlimited tablespace permission to avoid unlimited use of the default user tablespace.
[Email protected]> select * From dba_sys_privs where grantee = 'connect ';

Grantee privilege Adm
-------------------------------------------------------------------------
Connect create session no

[Email protected]> select * From dba_sys_privs where grantee = 'resource ';

Grantee privilege Adm
-------------------------------------------------------------------------
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

8 rows selected.

Elapsed: 00:00:00. 00
[Email protected]> select * From dba_sys_privs where grantee = 'James ';

Grantee privilege Adm
-------------------------------------------------------------------------
James unlimited tablespace No

Grant connect to James;
Grant resource to James;
Revoke unlimited tablespace from James;


3 space allocation generally creates Independent Application-related tablespace for each user
After creating a tablespace, grant the user unlimited tablespace permission on tablespace to use all the space.

Create tablespace James
Datafile '/export/home/Oracle/oradata/James. dbf'
Size 100 m
Autoextend on
Next 10 m
Maxsize 2048 m
Extent management local uniform size 128 K
Segment space management auto;

[Email protected]> alter user James quota unlimited on James;

[Email protected]> select * From dba_ts_quotas where username = 'James ';

Tablespace_name username bytes max_bytes blocks max_blocks dro
-------------------------------------------------------------------------
James 0-1 0-1 NO

-1 indicates Unlimited


Or assign the required space.
[Email protected]> alter user James quota 10 m on James;

User altered.

[Email protected]> select * From dba_ts_quotas where username = 'James ';

Tablespace_name username bytes max_bytes blocks max_blocks dro
-------------------------------------------------------------------------
James 0 10485760 0 1280 No

4. default tablespace and temporary tablespace are allocated to users.

[Email protected]> alter user James
2 default tablespace James
3 temporary tablespace temp;

[Email protected]> select username, default_tablespace, temporary_tablespace from dba_users where username = 'James ';

Username default_tablespace temporary_tablespace
---------------------------------------------------------------------------
James temp

Precautions for creating users and allocating space