user SYN_CONS: provide access to other user/schema Now lets grant another user access to the synonym and view. user SYN_CONS: use synonym in viewĬreate view CONS_VIEW as select field1 from SYN1 Lets create a view on a synonym and consume the view, both in schema SYN_CONS. This is exactly the same behavior, as if the view would be created directly on the base object. When a user created a view based on a synonym and wants to grant access to this view to a second user or role, the first user needs the GRANT OPTION on the base object. When you grant a synonym privilege to a user, behind the scenes access to the base object is granted. Even a schema privilege for the synonym’s schema is not sufficient to use a synonym. There is no such thing as a privilege to access the synonym itself. > row UDF1 Using a Synonym and Granting Accessįor using a synonym a user needs the corresponding privilege for accessing the base object. user SYN_PROV: grant access for creating synonym Lets create two synonyms in schema SYN_CONS.
Access to the synonyms would result in an error in both cases. This means a synonym remains existing even if its creator has no privilege to access the base object anymore or even after the base object was deleted. After creation, the synonym exists independently of the base object. Return select 'row UDF1' field1 from SYS.DUMMY įor creating a synonym a user needs the “SELECT” privilege for tables, views and sequences as base object, and the “EXECUTE” privilege for procedures and functions as base object. Insert into TAB1 (field1) values ('row TAB1') user SYN_PROV: create some base object: a table and an UDFĬreate column table TAB1 (field1 nvarchar(20)) a view for tables and views, a table function for a table functions, a procedure for procedures, a scalar function for scalar functions, not possible for sequences).įirst, I will create some base objects for synonyms in schema SYN_PROV. If you want/need to point synonyms to other synonyms, you have to put a real DB object in between (i.e. Synonyms cannot be used as base objects for other synonyms. Also, you can use a synonym to give an object a more convenient name, possibly in your own schema. There is no measurable performance penalty for using synonyms instead of their base objects. Currently, synonyms can be created for tables (this includes virtual tables), views, procedures, table functions, scalar functions and sequences. You can use synonyms to hide the concrete object names from consumers, allowing you to exchange the concrete objects later, without the need of changing their consumers.
A synonym belongs to its own schema, which is independent of the schema the DB object belongs to (of course, a synonym and its base object can have the same schema). Whenever you use a synonym, try to mentally replace it by its base object, that’s pretty much the same what the DB is doing. Synonyms are aliases for DB objects, but are no real DB objects by themselves (even though you can find them in view SYS.OBJECTS) .
SONONYM TO USE PASSWORD
with some Admin-User: create user that PROVides the base object, and two CONSumer usersĬreate user SYN_PROV PASSWORD "Init1234" NO FORCE_FIRST_PASSWORD_CHANGE Ĭreate user SYN_CONS PASSWORD "Init1234" NO FORCE_FIRST_PASSWORD_CHANGE Ĭreate user SYN_CONS2 PASSWORD "Init1234" NO FORCE_FIRST_PASSWORD_CHANGE The easiest way to do this is to open three SQL consoles, one for each user. That could be an end user, that is not allowed to access the replicated ERP Schema directlyįor simplicity reasons we will create three users and work directly with those users and their default schema, which has the same name as the user. When you read something like “–user SYN_PROV:” in the examples, this means, that the coding has to be executed by this user and the current schema is the default schema of the user. A third user (SYN_CONS2) shall only consume objects of the second user/schema, but never directly the objects of the first user.A second user (SYN_CONS) consumes the objects of the first User, directly or via synonym.This could be a schema we develop in, create DB views etc.One user (SYN_PROV) creates/owns DB objects like tables, and provides access to its objects via synonyms.In this blog post I try to explain what synonyms are and how they work in HANA SQL, but most of it will apply to other databases as well. When I started to write a document on the usage of synonyms in XS Advanced, I noticed, that even though most people I talked to knew how synonyms work in principle, most people (including me) had their problems when it came to details. Whereas in classical SQL usage of synonyms is optional, in XS Advanced they will be the only way to access objects of a different schema.
0 kommentar(er)
