Shulou(Shulou.com)06/02 Report--

Oracle rights management

The elite turns to the end to read the remarks.

One: user management

1.1: users and modes

In oracle, a schema corresponds to a user one by one. A schema can only be owned by a user and the name is the same as that user. When a user accesses an object in his own schema, he or she does not need to specify a schema name. For example, SELECT * from emp does not need to add a schema name when a scott user accesses his own EMP table. Authorized users can also access objects of other schemas, adding the schema name before the object name.

1.2: create a new user

To connect to the Oracle database, you need to create a user account, each with a default table space and a temporary table space

Users with CREATE user privileges can create new users for the database

The col field name for A10 / / controls the length of this field

Set timing on / / start the timing of the next action

Create user syntax:

CREATE user user_name

IDENTIFIED [BY userpwd | EXTERNALLY | GLOBALLY |]

[DEFAULT TABLESPACE default_tablespace]

[TEMPORARY TABLESPACE temp_tablespace]

[QUOTA size | UNLIMITED ON tablespace_name]

[PASSWORD EXPIRE]

[ACCOUNT LOCK | UNLOCK]

/ / the name of the database user to be created by user_name, which cannot be duplicated with the existing user name

/ / IDENTIFIED users specify the authentication method for database users. Password wants to create a user's password, that is, password authentication, EXTERNALLY means to use external authentication, and GLOBALLY means to use global authentication.

/ / DEFAULT TABLESPACE specifies that the default tablespace for the user is created. Objects created later are stored in the default tablespace, and the default tablespace is the USERS tablespace.

/ / TEMPORARY TABLESPACE specifies the user's temporary tablespace. When the operation performed during the session exceeds the available space of the PGA, the temporary tablespace is used. The default temporary tablespace is the TEMP tablespace.

/ / QUOTA limits the amount of storage space users can use in a table space. The default quota on the table space is 0. Users cannot use any space to store any objects or specify temporary table space quotas for users.

/ / PASSWORD EXPIRE specifies that the user changes the password when logging in for the first time

/ / ACCOUNT specifies the locked status of the user. The default is unlock.

After you create a user, the oracle database automatically creates a schema with the same name, and all objects created by that user are saved in the schema.

Create user user1 to set password 123, other defaults

Query the dateabase_properties view for the current default and temporary tablespaces in the database

SELECT property_name,property_value from database_properties

Where property_name like 'DEFAULT%'

-- query the default and temporary tablespaces of user user1, using the current default and temporary tablespaces of the database

1.3 create tablespaces

CREATE TABLESPACE test DATAFILE 'test.dbf' SIZE 10M ramp / create tablespace test

CREATE TEMPORARY TABLESPACE test_temp TEMPFILE 'test_temp.dbf' SIZE 10M ramp / create temporary tablespace test_temp

-create user user2 to specify default tablespace test, temporary tablespace test_temp, unlimited quota.

CREATE user user2 IDENTIFIED BY 123

DEFAULT TABLESPACE test TEMPORARY TABLESPACE test_temp

QUOTA UNLIMITED ON test

User user2 can use storage data objects of any size on the table space, and you can use the view dba_ts_quota to view the table space quota assigned to the user. User1 is not allocated, so the record cannot be found.

1.4: modify user

Syntax:

-- ALTER user user1 ACCOUNT LOCK;// locks user user1

-- ALTER user user1 PASSWORD EXPIRE ACCOUNT UNLOCK;// unlocks user1 and requires password change when logging in

-- ALTER user user1 DEFAULT TABLESPACE test;// modifies the user's default tablespace test

-- ALTER user user1 QUOTA 5m ON test; / / modify the quota of user user1 to 5m

1.5: delete user

DROP user user1 [cascade] / / Delete user1 users

/ / when a user does not create a data object, it can be deleted directly. If a user creates an object and uses the keyword cascade to cascade delete the data object created by the user, the data will be deleted first and then the user. When the user is logging in, the user cannot be deleted. If the user is forced to delete, you need to query the user's session ID and other information through the view V$SESSION, then use the ALTER SYSTEM KILL SESSION statement to kill the current user's session, and finally use DROP USER to delete the user.

1.6 query user information through data dictionary

1.//user_users: displays the details of the currently logged in user, including user name, user ID, account status, default tablespace, temporary tablespace

2.//all_users: displays the users that can be accessed by the currently logged-in user, including only the user name, user ID, and the time the user was created

3.//dba_users: displays all user information in the database, including user name, user ID, account status, password, lock date, expiration date, default tablespace temporary tablespace.

Second: rights management

This view can find the required permissions.

Log in as sysdba. After login, the SYS,SYS user has the permissions of roles such as DBA in addition to the permissions of the SYSDBA system. It is very different from the permissions of the sysdba system. Ordinary users may not even be able to log in to the database even if they have sysdba permissions.

2.1: there are two types of permissions, system permissions and object permissions

System permissions allow users to perform certain database operations, such as creating a table is a system permission

Object permissions allow users to perform specific operations on database objects (such as tables, views, sequences, etc.)

2.2: the administrator grants permissions to ordinary users

Syntax:

GRANT sys_priv [, sys_priv]

TO user [, user] | role | PUBLIC

[WITH ADMIN OPTION]

/ / sys_priv indicates the system permissions to be granted. Multiple system permissions can be granted at the same time, separated by commas.

/ / user indicates the user to be granted system permissions, the role to which role wants to grant permissions, and PUBLIC represents the PUBLIC user group. Multiple users can be given system permissions at the same time, separated by commas.

/ / whether WITH ADMIN OPTION allows authorized users to delegate the acquired permissions to other users, which cannot be granted to other users by default

2.3 system permissions, granted system permissions

1. Grant users permission to connect to the database and create database objects: GRANT CONNECT TO tom

two。 Allow users to connect to the database: GRANT CREATE SESSION TO tom

two。 System permissions for creating tables: GRANT CREATE TABLE TO tom

3. Create a profile file: CREATE PROFILE pro1 LIMIT

4. Modify the user's profile file: ALTER user test1 PROFILE pro1

5. Allow users to use users table 10m space quota: ALTER user tom QUOTA 10m ON USERS

6.Tom users use tablespaces at will: GRANT UNLIMITED TABLESPACE TO tom

7. Revoke system permissions revoke:REVOKE create session from tom

2.4 object permissions, granted object permissions

1. Object permissions: object permissions (SELECT on scott.student) to access a user's specific data objects (such as tables, views, etc.)

Commonly used object permissions:

1.select permissions: perform query operations. The objects that can be executed are tables, views, and sequences.

2.insert permissions: perform insert data operations, and the objects that can be executed are tables and views

3.update permissions: perform update data operations, and the objects that can be executed are tables and views

4.alter permissions: perform operations to modify objects, which can be performed on tables and sequences

5.delete permissions: perform operations to delete data, and the objects that can be executed are tables and views

6.index permission: perform index creation operation, and the object that can be executed is the table

7.references permission: perform operations referenced by foreign keys, and the objects that can be executed are tables

8.execute permissions: perform object operations, and the objects that can be executed are functions, procedures, and packages.

Syntax:

GRANT object_privilege [column_name...]

ON obj_name

TO user [, user | role, PUBLIC...]

[WITH GRANT OPTION]

/ / obj_priv represents the object permissions to be granted. Multiple object permissions can be granted at the same time, separated by commas.

/ / column_name represents the column name to be granted to the Operand, separated by commas

/ / obj_name represents the object to which the object permission operates

/ / user refers to the user who wants to grant permission to the object, the role to which role wants to grant permission to the object, and PUBLIC represents the PUBLIC user group. Multiple users can be given permission to belong to the object at the same time, separated by commas.

/ / whether WITH ADMIN OPTION allows authorized users to delegate the acquired permissions to other users, which cannot be granted to other users by default

Revoke object permissions REVOKE:

REVOKE privilege ON object from {user | role | PUBLIC}

Example: revoke SELECT on scott.student from test2

1.GRANT SELECT ON SCOTT.EMP TO tom WITH GRANT OPTION;// allows tom users to query the records of the EMP table and to grant permissions to other users

2.GRANT UPDATE ON SCOTT.EMP TO tom; allows users to update the records of the EMP table

3.GRANT ALL ON SCOTT.EMP TO tom; allows users to insert, delete, update, and query table records

4.GRANT UPDATE (ename,sal) ON SCOTT.EMP TO tom; is accurate enough to allow users to update ename,sal columns in EMP

Three: role management

Role management: a role is a combination of related permissions that can be granted to a role and then to a user to simplify permission management. A user can have one or more roles, and you can grant roles to other users or roles, and that user's permissions change regardless of whether you increase or decrease permissions in the role.

To create the role CREATE ROLE, you should have CREATE ROLE system permissions.

CREATE ROLE role_ name [not IDENTIFIEN] | [IDENTIFIED {BY password | USING [scheam.] package | EXTERNALLY | GLOBALLY}]

/ / role_name indicates the name of the role to be created

/ / NOT IDENTIFIED means that the role takes effect immediately after it is granted to other users and roles. This is the default value.

The / / IDENTIFIED clause indicates that it will not take effect immediately after it is granted to another user, to prevent other users from enabling and disabling the role at will. BY password means to set a password for the role while creating the role. The user cannot have the permission for the role immediately and must be activated with the SET ROLE statement. By default, there is no password, and the user can have the permission for the role immediately. USING package indicates that the role created is an application role that can only be enabled in the application using authorized packages. EXTERNALLY indicates that you want to use external service authorization to enable the role. GLOBALLY indicates that the role is enabled by a user authorized by the enterprise service.

SCOTT user creation is not successful because there is no permission

SYSDBA gives SCOTT users permission to create roles

SCOTT users create 3 roles

3.2 Grant role permissions, which can be system permissions or object permissions.

GRANT privilege TO ROLE

Example: GRANT create session, create any table, drop any table TO role1

The role e_emp1 is granted query permissions for EMP tables in scott mode and system permissions to create and delete views in any mode. The role e_emp2 is granted insert, update, and delete permissions for the EMP table in scott schema. The role m_emp is granted query, insert, update, and delete permissions for the DEPT table in scott mode.

3.3 Grant roles to users:

GRANT role TO user

Example: grant role1 to test2

3.4 View the role information that the user is directly granted

The query view user_role_privs can view information about the roles that the user is directly granted.

View the role of the user1 user in user user1.

As you can see from the figure above, the role e_emp1 is automatically set to the default role of the user user1, while M_EMP is set with a password so it cannot automatically become the default role. For user user1, you can only query the information of EMP table in scott mode, but you cannot view the information of DEPT table.

User user1 can view the EMP table

Cannot view dept table

3.5 modify the default role

ALTER user DEFAULT ROLE [role_name | ALL [EXCEPT role_name] | NONE]

/ / role_name is the name of the role to be set, separated by commas

/ / ALL enables all roles of the current user as the default role, but does not include the role of setting the password.

/ / EXCEPT enables all roles of the current user as the default role except the specified role

/ / NONE disables all roles for the current user, that is, the default role is zero.

ALTER user user1 DEFAULT ROLE NONE; / / modifies that the default role of the user is 0 and does not have any roles after login.

Enable all roles except the e_emp1 role to be the default role after logging in to ALTER user user1 DEFAULT ROLE ALL EXCEPT eBay emp1

Enable all roles as default roles after ALTER user user1 DEFAULT ROLE ALL;//user1 login

Only the e_emp1 role is enabled as the default role after the ALTER user user1 DEFAULT ROLE eBay emp1 account account user1 login.

3.6 enable and disable role set

SET ROLE [role_name [IDENTIFIED BY password | ALL [EXCEPT role_name] | NONE]

/ / role_name is the name of the role to be set, separated by commas

/ / password to be used when IDENTIFIED BY is used to enable or disable roles

/ / ALL enables all roles of the current user as the default role, but does not include the role of setting the password.

/ / EXCEPT enables all roles of the current user as the default role except the specified role

/ / NONE disables all roles for the current user, that is, the default role is zero.

SET ROLE m_emp IDENTIFIED BY 123; / / enter the password while enabling the m_emp role

Enable in user1 user

And view

3.7 withdraw the role from the user:

REVOKE role1 from user2;// reclaims role1 roles from user user2

REVOKE CREATE ANY VIEW,DROP ANY VIEW from eBay emp1; / / reclaim the system permissions of the e_emp1 role

3.8 Delete roles

DROP ROLE e_emp1

(5) the GRANT RESOURCE TO tom:RESOURCE role allows users to use the storage space in the database (the system has its own role)

Note:

Oracle query does not need to enter the previous user name (A.B does not need to enter a)

You can use the following four methods: sufficient space resources or a small amount of table data can be used.

1. Use consent words

Grant CREATE SYNONYM to scott; / / DBA or authorized user authorization

Create synonym emp_test for scott.emp

two。 Working with View

Create view emp_test as SELECT from scott.emp

3. Account authorization

Grant SELECT on scott to test; / / test users can view the tables of scott users

4. Create a database

Create table emp_test1 as SELECT from scott.emp

Assign permissions to users

Grant SELECT on DEMO_INVOICE_CLOUD.FMB_INVOICE to demo_pc_core01

GRANT connect, resource TO test

GRANT RESUMABLE TO TEST;-can automatically expand the space

GRANT BACKUP ANY TABLE TO TEST

GRANT EXECUTE ANY TYPE TO TEST

GRANT SELECT ANY TABLE TO TEST

GRANT READ ANY FILE GROUP TO TEST

GRANT SELECT ANY SEQUENCE TO TEST

GRANT EXECUTE ANY PROCEDURE TO TEST

Permissions for connect users to log in to the database

Resource users can create permissions for database objects, tables, views, and stored procedures, which are generally granted to developers.

Drop user cmsuser cascade

Use the cascade parameter to delete all objects for this user

Permissions for connect users to log in to the database

Resource users can create permissions for database objects, tables, views, and stored procedures, which are generally granted to developers.

Drop user cmsuser cascade

Use the cascade parameter to delete all objects for this user

Rowid is the row identifier in the Oracle database, which is rowid. It is an 18-digit number with a base of 64. The identifier contains the physical location of the row in the oracle database. The query rowid is as follows:

SQL > SELECT rowid,id from test Where rownum < 5

Delete duplicate records in the table

Delete from test a

Where rowid < (SELECT Max (rowid) from test Where Id = a.Id)

Grant permissions for all tables below one user to another user

1. View the tables used by the user below

SELECT * from dba_tables where owner='SCOTT' and status='VALID'

two。 Splice the tables below the user into authorization statements

SELECT 'grant select,delete,update,insert' | | table_name | |'to test; 'from dba_tables where owner='SCOTT' and status='VALID'

3. The output in step 2 is executed with dba or a user with authorization, and the current user can execute if he or she has authorization.

Note: if you need to authorize all tables in the currently logged-in user database to test users, you can use the following statement to concatenate

SELECT 'grant select,delete,update,insert' | | table_name | |'to test; 'from user_tables where owner='SCOTT' and status='VALID'

Show all tables except sys to PC_CORE01 users to view

Grant select any table to PC_CORE01

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.