schema ownership/permissions in MS-SQL vs Oracle

Question

Hopefully I'm using the write terms here. I'm pretty good with concepts usually but don't always know the technical terms to describe it correctly. Here goes...

I'm used to working in MS SQL Server for companies where separate schemas haven't really been defined outside of dbo. I'm working with an Oracle database now and my user isn't the schema owner so in order to see the tables, I have to go to "Other Users" in Oracle SQL Developer and find the schema owner to view the tables under them. In other SQL IDEs such as TablePlus, I haven't figured out how to view the tables at all. I am able to query data from these tables once I know them but I don't see them listed. Without really having worked in a MS SQL system with different schemas and permissions, I'm trying to wrap my head around what's going on as far as schema definitions/permissions vs what's unique to Oracle. For example, how would this be setup equivalently in MS-SQL and if it was setup that way in MS. SQL, would I be able to view the table structure in SQL Server Management Studio as I normally do? TIA!

Answer 1

Roughly speaking an Oracle schema is analogous to a SQL Server database and login, combined. An Oracle user may own schema objects or not, similar to the way an OS user may own files and directories or not, based on which CREATE privileges it has been assigned. By default, a user's schema objects are only visible to that user (the owner), or to other users with system level DML "ANY" privileges like SELECT ANY TABLE. In all other cases, access to a schema object must be explicitly granted to another user or role.

In MS SQLServer, multiple logins would potentially be given access to the same database using schema-level defined roles or groups like "db_owner" or "db_datareader". These roles allow the possibility of a many-to-many relationship between schema ownership roles and logins, where in Oracle that ownership relationship is always 1:1.

Whether or not you can see table structure in SQL Developer may depend on the specific permissions of the user you are logged in as: i.e. whether it is the object owner, has the SELECT ANY DICTIONARY privilege, etc. Access to table content with something like a basic SELECT privilege does not guarantee access to data dictionary-level details.