Oracle creates table spaces, creates users, and authorizes, views permissions ____oracle

Oracle creates table spaces, creates users, and grants, views permissions

Create a temporary table space
CREATE Temporary tablespace test_temp
Tempfile ' C:\oracle\product\10.1.0\oradata\orcl\test_temp01.dbf '
SIZE 32M
Autoextend on
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT Local;

Create a user table space
CREATE tablespace Test_data
LOGGING
DataFile ' C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01. DBF '
SIZE 32M
Autoextend on
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT Local;

Create a user and make a table space
CREATE USER username identified by password
DEFAULT tablespace Test_data
Temporary tablespace test_temp;

Granting permissions to Users
GRANT
Create session, create any TABLE, create no VIEW, create any INDEX, create any PROCEDURE,
Alter any TABLE, alter any PROCEDURE,
Drop any TABLE, drop any VIEW, drop any INDEX, drop any PROCEDURE,
SELECT any table, inserts any table, the UPDATE any table, deletes any table
to username;

The role is delegated to username, that is to say, to enable username to manage and use the resources owned by roles to username;

-----------------------------------------------View User Rights---------------------------------------------------------

View All Users
SELECT * from Dba_users;
SELECT * from All_users;
SELECT * from User_users;

View User system permissions
SELECT * from Dba_sys_privs;
SELECT * from User_sys_privs;

To view user objects or role permissions
SELECT * from Dba_tab_privs;
SELECT * from All_tab_privs;
SELECT * from User_tab_privs;

View all roles
SELECT * from Dba_roles;

To view the roles owned by a user or role
SELECT * from Dba_role_privs;
SELECT * from User_role_privs;

-------encountered no privileges on tablespace ' tablespace ' alter user UserQuota 10M [unlimited] on tablespace;

Oracle New user, role, authorization, table space

The permission system of Oracle database is divided into system permissions and object permissions. System permissions (Database system privilege) allows users to perform a specific set of commands. For example, the CREATE TABLE permission allows a user to create a table, and grant any privilege permission allows the user to grant any system permissions. Object permissions (Database object privilege) allow users to perform certain operations on individual objects. For example, delete permission allows a user to delete a row of a table or view, and the SELECT permission allows the user to query information from a table, view, sequence (sequences), or snapshot (snapshots) through a select.

Each Oracle user has a name and password and has some tables, views, and other resources created by it. An Oracle role is a set of permissions (privilege) (or the type of access each user needs depending on their status and conditions). The user can grant or give the role the specified permissions, and then assign the role to the appropriate user. A user can also authorize other users directly.
First, create users

There are two built users within Oracle: System and sys. Users can log on to the system user directly to create other users, because system has the right to create another user. When Oracle is installed, the user or system administrator can first establish a user for themselves. e.g.

SQL code

Create user User01 identified by u01;  

This command can also be used to set additional permissions, as detailed in the self-study material. To change a password, you can use the ALTER USER command:

Alter user User01 identified by Usr01;

Now User01 's password has been changed from "U01" to "Usr01".

In addition to the alter USER command, users can also use the password command. If you use the password command, the new password entered by the user will not appear on the screen. A user with DBA authority can change the password of any other user through the password command, and other users can only change their password.

When the user enters the password command, the user is prompted for the old password and the new password, as follows:

       Password
changing password for User01 old
password:
new password:
Retype new password:

When a password is successfully modified, the user receives the following feedback:

       Password changed

Second, delete users

To delete a user, you can use the drop user command as follows:

      Drop user User01;

If the user owns the object, it cannot be deleted directly, or an error value is returned. Specifies the keyword cascade, which deletes all the user's objects and then deletes the user. The following examples are used to delete users and their objects:

      Drop user User01 cascade;

three, 3 standard roles

Qracle provides three standard roles for compatibility with previous versions: Connect, resource, and DBA. 1. Connect role (Connection roles)

Temporary users, especially those who do not need to build tables, usually only give them connectrole. Connect is a simple privilege to use Oracle, which makes sense only if you have access to other users ' tables, including SELECT, INSERT, UPDATE, and delete. Users who have connect role can also create tables, views, sequences (sequence), clusters (cluster), synonyms (synonym), sessions (session), and chains to other databases (link).
2. Resource role (Resource roles)

More reliable and formal database users can grant resource role. Resource provides users with additional permissions to create their own tables, sequences, procedures (procedure), triggers (trigger), indexes (index), and clusters (cluster).
3. DBA Roles (database Administrator role)

The DBA role has all the system privileges----including unlimited space limits and the ability to grant various permissions to other users. System is owned by the DBA user. Here are some typical privileges that DBAs often use.

(1) Grant (authorization) Order

The following is a USER01 authorization for the user you just created, with the following command:

(2) REVOKE (REVOKE) permissions

The permissions that have been granted can be undone. For example, to undo the authorization in (1), the command is as follows:

       Revoke connect, resource from User01; 

A user with a DBA role can revoke other permissions from Connect, resource, and DBA for any other user or even another DBA. Of course, this is dangerous, so, unless really needed, DBA authority should not be arbitrarily granted to ordinary users who are not important. Revoking all of the permissions of a user does not mean that the user is removed from Oracle or that any tables created by the user are not destroyed, but simply prevents access to those tables. Other users who want to access these tables can access them as before.

Iv. Creating roles

In addition to the three system roles mentioned earlier----connect, resource, and DBA, users can also create their own role in Oracle. User-created role can consist of table or system permissions or a combination of both. In order to create role, the user must have the Create role system permission. An example of the Create role command is given below:

         Create role student; 

This command creates a role named student.

Once a role is created, the user can authorize it. The syntax for grant commands for role authorization is the same as for the user. When granting role authorization, use the name of role in the to clause of the grant command, as follows:

Grant select on class to student;

Now, all users who have the student role have SELECT permissions on the Class table.

v. Delete roles

To remove a role, you can use the drop Roles command, as follows:

      Drop role student;

The specified role, along with the permissions associated with it, is removed from the database.

Vi. Delete Table Considerations

When you delete all of the data in a table, you must use the

Because the table space occupied by the drop Table,delete * from table name is not released, the tablespace space on the tablespace is depleted after repeated several drop,delete operations.

Oracle View user, user rights, user table space, user default tablespace
1. View the relationship between the user and the default table space.   Select Username,default_tablespace from Dba_users; 2. View the current user's table: select table_name from User_tables;
  3. View all user's table name:   SELECT table_name  from all_tables;     4. View all table names (including system tables)   SELECT table_name  from all_tables;      5. See All Tables:    Select  *  from  tab/dba_tables/dba_objects/cat;    below introduces Oracle query user table space   Oracle Query user tablespace: SELECT * FROM user_all_tables   Oracle query all functions and stored procedures: SELECT * from user _source   Oracle Query All users: SELECT * FROM All_users.select * from dba_users   Oracle View current user connections: SELECT * FROM V$sessio N   Oracle View current User rights: SELECT * FROM Session_privs   Oracle View User table space usage:   1.select a.file_id "Fileno", a.tabl Espace_name   "Tablespace_name",  a.bytes "bytes", a.bytes-  sum (NVL (b.bytes,0)) "Used",  sum ( b.bytes,0)) "Free",  sum (NVL (b.bytes,0))/a.bytes*100 "%free"     from Dba_data_files A, Dba_free_space B & Nbsp;where a.file_id=b.file_id (+)  group by A.tablespace_name,     a.file_id,a.bytes ORDER by A.tablespac e_name;  
1. View All Users: SELECT * from Dba_user;     SELECT * from All_users;   SELECT * from User_users;     2. View User system permissions: SELECT * from Dba_sys_privs;     SELECT * from All_sys_privs;   SELECT * from User_sys_privs;     3. View User Object permissions: SELECT * from Dba_tab_privs;     SELECT * from All_tab_privs;   SELECT * from User_tab_privs;   4. View all roles: select * from Dba_roles;     5. View the roles owned by the user: SELECT * from Dba_role_privs; SELECT * from User_role_privs;
6. View the permissions that the role has:
SELECT * from Role_sys_privs;
SELECT * from Role_tab_privs;
7. View all system permissions select * from System_privilege_map;
8. View all object permissions
SELECT * from Table_privilege_map;

about Oracle User table space permissions ask : Create User A under SYS user A, the default tablespace is A1, temporary tablespace is A2

Then set up the table space A3, how to let user a have the right to set up a table in A3? The prerequisite is not to give a CREATE any table and DBA authority (that is, user A can use the tablespace as a a1,a2,a3).

Does the method you answered be feasible when A3 is replaced with the SYS default tablespace system?

By the way, what's the use of the tabs table in Oracle?

about Oracle User table space permissions answer:

Question One