-= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = - Oracle user ,object ,system - = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = a ,user mode user :access to the database ,under the appropriate user identity through the verification ,and have the relevant permissions to complete a series of actions SYS user ,default create always ,and not locked ,has a data dictionary and its relevance to all object SYSTEM user ,default create always ,and not locked ,can access the database of all objects within the model ( schema ) :a user has all the collections of objects .
Create object privileges and the creation of the user of the object known to possess a mode of attention :create database object ( view ,form) of any user has a user name to the first mode ,and is regarded as the model two ,users create and modify user conditions: the need for a create user ,such as sys ,UGG Boots,system ,SYSDBA ,DBA role USER user IDENTIFIED syntax: CREATE {BY password EXTERNALLY GLOBALLY AS external name} DEFAULT TABLESPACE TEMPORARY TABLESPACE QUOTA {n { UNLIMITED } ON tablespace_name QUOTA {n { UNLIMITED } ON tablespace_name .
.. ACCOUNT {LOCK UNLOCK} PROFILE {profile_name DEFAULT} Eg CREATE USER Robinson IDENTIFIED :BY tiger ;DEFAULT TABLESPACE and TEMPORARY ,omitting the TABLESPACE ,by database_properties SQL > corresponding parameters ;SELECT property_name ,property_value FROM database_properties WHERE property_name LIKE PROPERTY_NAME PROPERTY_VALUE ;DEFAULT_TEMP_TABLESPACE TEMP DEFAULT_PERMANENT_TABLESPACE USERS DEFAULT_TBS_TYPE ------------------------------ -------------------------------------------------- SMALLFILE more about the table space please refer to :1 alter user grammar with the create user ,saying just the keyword create replaced alter alter ,user can be modified except username of any attribute ALTER USER Robinson ACCOUNT LOCK ;2 modify password DBA can create and modify user password user can use the ALTER USER statement to modify the password SQL > ;ALTER Robinson IDENTIFIED BY newpassword ;DROP USER username 3 delete user: CASECADE along with the user-created object deleted ,if the user creates the object ,with CASCADE deleted, or delete do not lose another ,not delete current Being connected with the ORACLE server user .
4 the change of user dduyoung quota unlimited on :ALTER USER username QUOTA 0 ON system ;ALTER USER Scott QUOTA UNLIMITED ON USERS ;ALTER USER dog QUOTA 30M ON system ;5 view user tablespace quotas ( dba_ts_quotas ) : SQL > ;SELECT USERNAME ,TABLESPACE_NAME ,MAX_BYTES / 1024 / 1024 Max MB FROM dba_ts_quotas 2 WHERE USERNAME = SCOTT USERNAME TABLESPACE_NAME Max ;MB SCOTT SYSTEM 306 ------------------------------ -------------------------- view a specific object user objects using the dba_objects view SQL > ;SELECT owner ,object_name object_type FROM dba_objects ,Beats By Dre Headphones,WHERE owner = ;three ,ORACLE permissions : System :allows the user to perform a specific database actions ,such as create table ,create index ,connecting the instance object privileges :allows the user to manipulate some specific objects ,UGG Boots,such as read view ,can be Some of the columns ,update stored procedure is executed 1 system authority over one hundred kinds of effective authority ( SELECT * FROM SYSTEM_PRIVILEGE_MAP ) database administrator has advanced permissions to complete the tasks of management, for example :&ndash ;create user &ndash ;delete user delete table &ndash ;&ndash ;a backup table .
The commonly used system :CREATE SESSION create session CREATE SEQUENCE CREATE SYNONYM create synonym create sequence CREATE TABLE in user mode in CREATE ANY TABLE create table create any table DROP TABLE in user mode delete table DROP ANY TABLE in any mode delete table CREATE PROCEDURE EXECUTE ANY PROCEDURE to create a stored procedure to perform any pattern of the stored procedure CREATE USER create user DROP USER delete users of CREATE VIEW B user create view grant system privilege GRANT privilege privilege TO US ,.
.. Er role ,user ,PUBLIC... WITH ADMIN ;PUBLIC WITH ADMIN OPTION for all users so that users also has distribution rights rights ,can grant this permission to others to SQL > ;GRANT CREATE SESSION ,CREATE TABLE ,Moncler Outlet,CREATE USER TO Scott ;SQL > ;GRANT EXECUTE ANY PROCEDURE TO Scott WITH ADMIN OPTION ;SQL > ;CONN Scott ;WITH ADMIN OPTION - Scott has ,so they can be granted Robinson Enter password EXECUTE ANY PROCEDURE :Connected .
SQL > EXECUTE ANY PROCEDURE TO ;GRANT Robinson ;Grant succeeded .SQL > EXECUTE ANY PROCEDURE TO ;GRANT PUBLIC ;EXECUTE ANY PROCEDURE -- will be granted to all users of Grant succeeded SQL > ;CONN .
System / RedHat ;-- the use of system Robinson CREATE TABLE ,CREATE SESSION permissions granted Connected SQL > ;GRANT CREATE TABLE ,CREATE SESSION TO Robinson ;Grant succeeded .
C. With system privileges -- the use of Robinson has create session ,create table SQL > ;CREATE TABLE tb1 AS SELECT * FROM USER_TABLES ;- the following tips without permission in the users table space to create object CREATE TABLE tb1 AS SELECT * FROM USER_TABLES * ERROR at line 1 :ORA 01950 :no privileges on tablespace SQL > ;CONN sys as SYSDBA ;- - the use of sys account and Robinson in users tablespace quotas can create table tb1 Enter password :Connected .
SQL > ALTER USER Robinson QUOTA 10M ;ON USERS ;User altered .SQL > ;CONN Robinson / lion ;Connected SQL > .;CREATE TABLE tb1 AS SELECT * FROM USER_TABLES ;Table created .
D. Dba_sys_privs - view the system permissions for all users granted To the system permissions user_sys_privs -- in view of the current user system privileges granted by SQL > ;SELECT grantee ,privilege ,admin_option FROM dba_sys_privs 2 WHERE grantee IN ( , ) 3 ORDER BY grantee ;GRANTEE PRIVILEGE ADM ROBINSON CREATE SESSION ------------------------------ ---------------------------------------- - NO ROBINSON CREATE TABLE NO ROBINSON EXECUTE ANY PROCEDURE NO SCOTT CREATE PUBLIC SYNONYM NO SCOTT CREATE SESSION NO SCOTT CREATE SYNONYM NO SCOTT CREATE TABLE NO SCOTT CREATE USER NO SCOTT CREATE VIEW NO SCOTT EXECUTE ANY PROCEDURE YES SCOTT UNLIMITED TABLESPACE NO e REVOKE {privilege role recycling system } FROM {user_name role_name PUBLIC} - the following The examples do not recover from the original Scott robisnon EXECUTE ANY PROCEDURE granted permission SQL > ;REVOKE EXECUTE ANY PROCEDURE FROM Scott ;Revoke succeeded .
SQL > ;select grantee ,privilege ,admin_option from dba_sys_privs 2 where grantee in ( , ) and privilege = ANY PROCEDURE by grantee ;GRANTEE PRIVILEGE ADM ROBINSON EXECUTE ANY PROCEDURE ------------------------------ ---------------------------------------- - NO note: for the use of with admin option to grant a user system privileges ,so for the user is granted to the same access for all users, the user system privileges and not cascade cancel these users the same rights of 2 different objects with object privileges the different object object owner owns all the rights object owner can assign permissions to the ORACL outward E has a object permission object permission table view sequence process modification (Alter ) &radic ;&radic ;delete (delete) &radic ;&radic ;&radic ;implementation ( execute ) index ( index ) &radic ;insert (insert) &radic ;&radic ;&radic ;&radic Association ( references ) ;select ( select ) &radic ;&radic ;&radic ;update (update) &radic ;&radic ;a GRANT object_priv object ALL ( columns ) ON object TO {userrolePUBLIC } WITH GRANT ;ALL :PUBLIC :all object permissions granted to all users in WITH GRANT OPTION :allows the user to give other user authorization grant system privilege and B.
Granted permission on the object grammar differences :granted permission on the object when the need to specify the keyword ON ,so as to be able to determine permissions applied object .For tables and views can be specified columns to authorization .
- SQL > object example ;SHOW USER ;USER is SCOTT SQL > SELECT ON EMP TO ;GRANT Robinson ;Grant succeeded .SQL > ;GRANT UPDATE ( SAL ,Mgr ) ON EMP TO Robinson WITH GRANT OPTION ;Grant succeeded .
- create a user John ,use the Robinson account to update scott.emp ( SAL ,Mgr SQL > CREATE permissions ) ;USER John IDENTIFIED BY John ;User created .SQL > GRANT CREATE SESSION ;TO John ;Grant succeeded .
SQL > ;CONN ROBINSON / LION Connected .SQL > ;GRANT UPDATE ( SAL ,Mgr ) ON Scott .EMP TO John ;scott.emp ( SAL ,Mgr - grant the update permissions ) Grant succeeded .SQL > ;UPDATE Scott .
EMP SET Sal = Sal + 100 WHERE ename = SCOTT row updated updated successfully ;- 1 - to all users in the database .The assigning authority SQL > ;GRANT SELECT ON Dept TO PUBLIC ;Grant succeeded .
C query access distribution data dictionary view describes the ROLE_SYS_PRIVS role with system privileges ROLE_TAB_PRIVS role with object permissions granted out USER_TAB_PRIVS_MADE query object permissions ( usually belongs to the Lord himself .
) USER_TAB_PRIVS_RECD user object permissions USER_COL_PRIVS_MADE users distribution out of the column object permissions USER_COL_PRIVS_RECD users have of the column object permission USER_SYS_PRIVS user system privileges USER_TAB_PRIVS user object permissions USER_ROLE_PRIVS user role -- query has been delegated to the object (i.
e., a user on which tables to which users open object privileges ) SQL > ;SELECT * FROM user_tab_privs_made ;- the following is Scott user open object permission GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE -------------------- ------------------------ ------------------------- -------------------- --- PUBLIC DEPT SCOTT SELECT NO NO ROBINSON EMP SCOTT SELECT NO NO -- Query column open object permission SQL > ;SELECT * FROM user_col_privs_made GRANTEE TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE ;GRA -------------------- -------------------- --------------------- -------------------- - ROBINSON EMP SAL SCOTT on the UPDATE YES JOHN EMP MGR ROBINSON UPDATE NO ROBINSON EMP MGR SCOTT UPDATE YES JOHN EMP SAL ROBINSON UPDATE NO - a query has to accept the object privilege (i.
e.,Coach Bags, a user is given which tables on which object privilege ) SQL > ;SELECT * FROM user_tab_privs_recd OWNER TABLE_NAME GRANTOR PRIVILEGE GRA ;HIE -------------------- - - - - - - - ------------- ------------------------------ -------------------- --- SCOTT EMP SCOTT SELECT NO NO -- query the user has to receive the column object permissions, SQL > ;SELECT * FROM user_col_privs_recd OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE ;GRA -------------------- this ---------------- -------------------- -------------------- - SCOTT EMP MGR SCOTT UPDATE YES SCOTT EMP SAL SCOTT UPDATE YES D.
To recover the object using the REVOKE statement to recover the permission to use the WITH the GRANT OPTION clause assigned permissions were also recovered REVOKE {privilege privilege ALL } ,.
.. ON object FROM {user user rolePUBLIC } ,... ;CASCADE CONSTRAINTS for processing the referential integrity to recover authority example SQL > ;conn Scott / tiger ;Connected SQL > ;REVOKE .
SELECT ON EMP FROM Robinson ;Revoke succeeded .SQL > ;REVOKE UPDATE ( SAL ,Mgr ) ON EMP FROM Robinson ;- a note here the tip revoke is the entire table, rather than a column of REVOKE UPDATE ( SAL ,Mgr ) ON EMP FROM Robinson * ERROR at line 1 :ORA 01750 :UPDATE / REFERENCES may only be REVOKEd from the whole table ,not by column SQL > ;REVOKE UPDATE ON EMP FROM Robinson ;Revoke succeeded .
- user Robinson update permissions are revoke ,was given permission to cascade John have also been recovered ,the following tips - table ,view does not exist ,user_col_privs_recd SQL > CONN recorded ;John / John ;Connected .
SQL > ;UPDATE Scott .EMP SET Sal = Sal 100 WHERE ename = Scott EMP SET ;UPDATE .Sal = Sal 100 WHERE ename = SCOTT ERROR at line 1 :ORA - 00942 :Table or view does not exist SQL > ;SELECT * FROM user_col_privs_recd ;no rows selected note: if the cancellation of a user object privileges ,for the users to use with grant option grant other users in the same permissions ,cascading delete the user permissions e.
Other check DBA user select * from dba_role_privs where granted_role = DBA ;see user system privileges :SELECT * FROM session_privs ;four ,sum 1 using create user create user ,alter user alter user ,its syntax roughly the same drop user username will delete the user has all the objects and data of 2 system allows the user to perform specific operations in the database the DDL statement is executed ,such as .
With admin option so that the user has their own access permissions to other users of the function but revoke system privilege ,not from other accounts of cascaded cancellation was awarded the same permissions 3 object allows a user to database objects to perform specific operations ,such as the execution of the DML statement .
With grant option makes the user has their own access to the object permissions to other users of the function but recovered object permission ,from other accounts of cascaded cancellation
was awarded the same permissions permissions permissions are granted 4 system with the object when the object grammar differences for permission to use the ON object_name clause 5 PUBLIC
Related articles：
没有评论:
发表评论