Create createuser and grant permissions in Oracle

View login users: showuser; selectsys_context (

View logged-on users: show user; select sys_context (

View login users:

You can:
Show user;
Select sys_context ('userenv', 'session _ user') from dual;
Select user from dual;

View all login users must be DBA users:
Select username from v $ session;

DBA users such as sys and system View objects (tables) in other users (test ):
SQL> select * from test. student;


Create a process in which a common user can use the user:
1. Create a user
SQL> create user test indentified by test;
In this way, a user named test is created.
However, at this time, the test cannot be successfully logged in. We need to grant the corresponding permissions.

2. Grant the create session permission
SQL> grant create session to test;
In this way, the test user can log in successfully.

However, the user still cannot create a table. We need to grant the user the permission to create a table:
SQL> grant create table to test;
However, you cannot create a table at this time because you need to have the permission to use the tablespace (equivalent to having the key to enter the room but not the key to enter the big door ...)

Therefore, the corresponding permissions should also be granted.
SQL> grant unlimited tablespace to test;
At this time, the user has the permission to create a table. Because the table is corresponding to the user test, he has the permission to add, query, modify, and delete the created table.

3. view what permissions a user has to query a System View (digital dictionary)
SQL> select * from user_sys_privs;
In this way, you can know the permissions of the current user.

4. revoke permissions
SQL> revoke create table from test;

-----------------------------
Differentiation of common views
Dba_tables dba_all_tables user_tables user_all_tables all_tables all_all_tables
All tables to which the current user belongs (Note uppercase)
SQL> select tablespace_name, table_name from user_all_tables where table_name = 'student ';
SQL> select table_name, tablespace_name from user_tables where table_name = 'student ';
TABLE_NAME TABLESPACE_NAME
------------------------------------------------------------
STUDENT USERS

Sys needs to view dba_all_tables and ALL_ALL_TABLES to view the table of user test.
SQL> select owner, table_name, tablespace_name from dba_all_tables where owner = 'test ';
SQL> select owner, table_name, tablespace_name from all_all_tables where owner = 'test ';
SQL> select owner, table_name, tablespace_name from dba_tables where owner = 'test ';
SQL> select owner, table_name, tablespace_name from ALL_tables where owner = 'test ';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------------------------------------------------------------------
TEST STUDENT USERS

1. DBA_ALL_TABLES describes all object tables and relational tables in the database. Its columns are the same as those in ALL_ALL_TABLES.
2. ALL_ALL_TABLES describes the object tables and relational tables accessible to the current user.
3. USER_ALL_TABLES describes the object tables and relational tables owned by the current user. Its columns (tables t for OWNER) are the same as those in
ALL_ALL_TABLES.
----------------------------------------------------------------------

Scenario 1:
User test user test1
The test1 user created a table mytab and inserted some data.
So can the test user access mytab of test1?
A: No. authorization is required first.
Test1 must be authorized to test: grant select on mytab to test;
In this case, test can use select * from test1.mytab; to access data in mytab.
If you want to grant all permissions of a table (object) to test, you can:
Grant all on mytab to test;
Revoke all Permissions
Revoke all on mytab to test;



Summary
The system permission is implemented by sys.
Who owns the object permission and who authorizes it

System Permissions
Grant create session to test;
Grant create table to test;
Grant unlimited tablespace to test;

Revoke create session from test;
Revoke create table from test;
Revoke unlimited tablespase from test;
Grant create session to public; // grant the table creation permission to all users.
Select * from user_sys_privs; // returns all system permissions of the current user.

Object permission
Grant select on mytab to test;
Grant all on mytab to test;

Revoke select on mytab from test;
Revoke all on mytab from test;

Select * from user_tab_privs; // return all object permissions of the current user.

Object permissions can be controlled to columns
Grant update (name) on mytab to test;
Grant insert (id) on mytab to test;

Select * from user_col_privs;
Note: Query and deletion cannot be controlled to columns.
Insert update insert with commit is required

Transfer Permissions
Transfer of system permissions:
Grant alter table to A with admin option;
A can pass the permission to B. If you want B to pass the permission, you can also use the with admin option.
Grant alter table to B;
Object permission transfer:
Grant select on mytab to A with grant option;
Then A can grant the select permission on the mytab table to B. If B wants to pass the select permission, it can also carry the with grant option.
Grant select on mytab to B;


The user logging on to EM must have the following permissions:
Creates a user named testem with the following permissions:
Create user testem identified by testem;
Grant create session, select any dictionary to testem; // testem can log on to EM, but it is not the administrator of em.
Grant MGMT_USER to testem;

Non-em administrator: (there is no option under "manage)


Add the EM Administrator through EM login:
Name: testem
The email address is not defined for this administrator.
You have the permission to access all the target super administrators.
Database System permission: SELECT ANY DICTIONARY
Database role: MGMT_USER