Oracle Basic Statement (chapter fourth, database Security management)

1. Log in to SQL Plus with SYS user as SYSDBA and use Dba_users view to view user information:

SELECT USERNAME, Account_status, CREATED from dba_users;--user name, account status, date of creation of the user

2. Create user, delete user

CREATE User < username > identified by< password > default tablespace < defaults table space > temporary tablespace < temp table space >;

--If default, System is the default Tablespace and temp is the temporary table space.

Drop user < user name >;--delete users

3. Modify user information:

ALTER User < username > identified by < new password >;

ALTER User < username > PASSWORD expire;--set the password to expire immediately

ALTER User < username > account lock;--lock User, locked user cannot log in to database

ALTER User < username > account unlock;--Unlocked

4. Allocate table space quotas for users

ALTER User < user name > QUOTA < tablespace quota size > on < table space >;

5. Viewing user session Information

SELECT USERNAME, process, program, status FORM v$session;--user name, Progress ID, procedure name, session state

6. Manage User Rights

Grant < system permissions > to < user name >;--granting system permissions

Grant < Data object permissions > on < Data Objects > to < user name >;--Grant data object permissions

REVOKE < permissions or roles > from < user name >;--revoke user roles or permissions

Note: System permissions: Create TABLE, ALTER TABLE, DROP table,create session (create sessions), creation SEQUENCE, create user, ALTER user, drop user, SY SDBA is the highest system privilege.

Common data Object permissions: Add Insert, delete delete, change update, check select.

7. Viewing user rights information through view Dba_tab_privs

SELECT GRANTEE, TABLE_NAME, PRIVILEGE from Dba_tab_privs WHERE rownum<=10;--user Name, object name, permissions granted on object

8. Create a role

CREATE roles < role names > identified by < verify passwords >;

Drop role < name >;--Delete

9. Modify Role Properties

ALTER role < role name > not identified;--cancel password verification

10, the role of the specified user

GRANT < role name > to < user name >;

REVOKE < role name > from < user name >;--revocation

Note: role name: CONNECT

11. Viewing role information through view Dba_roles

SELECT * from dba_roles;--role name, whether the role needs to be started with a password

12. View the user's role information through the system view Dba_role_privs

Setect * from dba_role_privs;--user name, permission name, etc.

13. password file

14. View profile information through view Dba_profiles

SELECT * from Dba_profiles WHERE profiles= ' default ';--View the contents of the profile DEFAULT

Show PARAMETER resource_limit;--View The value of the Resource_limit parameter

ALTER SYSTEM Set resource_limit=true;--sets the value of Resource_limit

Slightly