Differences between sys and system User Permissions in oracle

Differences between sys and system User Permissions in oracle the most important difference between oracle sys and system users, sysdba and sysoper system permissions, and sysdba and dba roles is the most important difference between sys and system users www.2cto.com 1, the importance of stored data is different. The base tables and views of all oracle data dictionaries are stored in sys users. These base tables and views are crucial for oracle operation, the database is maintained by itself, and cannot be changed manually by any user. Sys users have the role or permissions such as dba, sysdba, and sysoper, and are the users with the highest oracle permissions. The system user is used to store the second-level internal data, such as the Management Information of some features or tools of oracle. System users have common dba role permissions. 2) The second difference is that different system users with permissions can only log on to em as normal, unless you grant sysdba or syspoer system permissions to it. The sys user has system permissions of "SYSDBA" or "SYSOPER". You can only use these two identities to log on to em, but cannot use normal. Log on to Oracle as a sys user and run select * from V _ $ PWFILE_USERS. Users with sysdba permissions can be queried, for example, SQL> select * from V _ $ PWFILE_USERS; username sysdba sysopersys true Sysdba and sysoper system permission difference normal, sysdba, sysoper what is the difference between normal is the other two normal users, if you check their permissions, you will know that sysdba has the highest system permissions. After logging on, syssysoper is mainly used to start and close the database. After logging on to sysoper, users are public sysdba and sysoper belong to system privilege, this is also known as administrative privilege. For example, you have some system management-level permissions such as enabling or disabling a database. For details about the permissions of sysdba and sysoper, see the following table: w Ww.2cto.com system permission sysdba sysoper difference Startup (start database) startup Shutdown (close database) shutdown alter database open/mount/backup change character set none create database (create database) None cannot create database drop database (delete database) none create spfile alter database archivelog (archive log) alter database archivelog alter database recover (recover database) can only be completely restored, cannot execute Incomplete recovery has restricted session (session limit) permission for restricted sess Ion permission allows you to perform some basic operations as sys users, but you cannot view User Data. After logon, the user is sys and the user is public www.2cto.com. If the user is logged on normally, it is actually a common dba user, but if you log on as sysdba, it actually serves as a sys user, which is similar to the sudo in Linux, we can see from the logon information. Therefore, after the as sysdba connects to the database, the created objects are actually generated in sys. The same applies to other users. If you log on as sysdba as a sys user, see the following experiment: SQL> create user strong identified by strong; the user has been created. SQL> conn strong/strong @ magick as sysdba; connected. SQL> show user; USER: "SYS" SQL> create table test (a int); table created. SQL> select owner from dba_tables where table_name = 'test'; unselected rows // because oracle is automatically converted to uppercase when creating the table, it does not exist when querying in lower case; SQL> select owner from dba_tables where table_name = 'test '; OWNER ------------------------------ www.2cto.com difference between dba and sysdba what is the difference between dba and sysdba? Before explaining this, I need to talk about the process of creating an oracle service instance → start an instance → · Create a database (system tablespace is required) during the startup process, the instance starts → loads the database → opens the database sysdba, which manages the oracle instance. Its existence does not depend on the full startup of the entire database. As long as the instance starts, it already exists, log in as sysdba To load and open the database. The dba role has a foundation only when the database is opened or the entire database is fully started.