Oracle creation, viewing of users and roles, user role empowerment

First, create the user and give permission

1. Create user

Create user Wangxiangyu identified by Wangxiangyu;

2. Empowering

Grant DBA to Wangxiangyu;

Grant create session to Wangxiangyu; --Session permissions (cannot log on without this permission)

3. View the system permissions that have been given to the user

SELECT * from User_sys_privs;

Second, create the role

A role, a collection of permissions, that can grant a role to a user

1. Create a role

Create role Myrole;

2. Empowering

Grant create session to myrole;--grants permissions to the role that created the session Myrole

3. Assigning roles to users

Grant Myrole to zhangsan;--the role of Zhangsan user Myrole

4. Delete a role

Drop role Myrole;

View All Users

SELECT * from Dba_users;

SELECT * from All_users;

SELECT * from User_users;

ALTER user user_name account lock; Lock users

Alter user user_name account unlock; Unlock Users

Querying permissions owned by the current user

SELECT * from Session_privs;

View the system permissions that the user is given (directly assigned to the user or role)

SELECT * FROM dba_sys_privs where grantee = ' RESOURCE ';

SELECT * from User_sys_privs;

Note: The User_sys_privs view lists the system permissions that have been granted to users.

Its columns include username, privilege, and Admin_option (a flag set to Yes or no to indicate whether permissions are granted with Admin Option), and all system permissions granted directly to the user can be displayed through the view. System permissions granted to a user through a role cannot be displayed in this view.

View all roles

SELECT * from Dba_roles;

View the roles owned by the user

SELECT * from Session_roles ORDER by role;--returns all roles granted to the current user, including nested authorized roles

SELECT * from Dba_role_privs;

SELECT * from User_role_privs;

To view the permissions that the current user role contains

SELECT * from role_sys_privs where role = ' CONNECT ';

View User Object permissions

SELECT * from Dba_tab_privs;

SELECT * from All_tab_privs;

SELECT * from User_tab_privs;

See which users have SYSDBA or Sysoper system permissions (requires appropriate permissions when querying)

SELECT * from V$pwfile_users;

To view the relationship between a user and a default table space

Select Username, default_tablespace from dba_users where username= ' SCOTT ';

View the current user's table

SELECT * from User_tables;

Visualize the empowerment of:

1. Use INS user to build table

2. Use Mobapp User (Admin) to assign a table of INS user to ODSO user

Users-->ins, select the table you want to empower (right-click, edit, Permissions)

Same as: Grant SELECT, INSERT, UPDATE, delete on Ins.tb_cablecheck_equ_odso to ODSO;

3, use ODSO user login, delete and change the table test

Command empowerment:

Give permission: Grant ... to ...

REVOKE permission: Revoke ...

Landing

Grant create session to Zhangsan;

Working with Table spaces

Grant unlimited tablespace to Zhangsan;

Create a table

Grant CREATE table to Zhangsan;

Delete a table

Grant drop table to Zhangsan;

Grant drop on table_name to user_name;

Insert Table

Grant Insert table to Zhangsan;

Grant INSERT on TABLE_NAME to user_name;

Grant Insert (ID) on table_name to user_name;

Updating table data

Grant Update table to Zhangsan;

Grant update on TABLE_NAME to user_name;

Grant Update (ID) on table_name to user_name;

Modify Table Structure

Grant ALTER TABLE on TABLE_NAME to user_name;

Query table

Grant SELECT on table_name to user_name;

Create a process

Grant create any procedure to username;

Execution process

Grant execute any procedure to username;

Grant execute on INS.P_TRUN_LINK_ODSO to Odso_insert;

Grant all permissions (all) to all users (public)

Grant all to public;

Permission passing

That is, user a grants permission to B,b to grant the permission of the operation to C again,

The command is as follows:

Grant ALTER TABLE on TABLE_NAME to user_name with admin option;

Grant update on TABLE_NAME to user_name with GRANT option; --Transfer Update permissions

Grant ALTER TABLE on TABLE_NAME to user_name with GRANT option;

Oracle creation, viewing of users and roles, user role empowerment