Tag Info

Hot answers tagged

7

I'd rather have the environment be the suffix assuming I had to pick one convention. Otherwise, it's too difficult to navigate through lists of login names that all use the same first five characters. Of course, if given my choice, I would prefer to use neither the prefix nor the suffix. I'd rather keep the same username in all environments and vary my ...


6

If you put the database into Restricted User mode, then only members of the fixed server roles sysadmin or dbcreator, or members of the fixed database role db_owner can access the database: ALTER DATABASE my_app_db SET RESTRICTED_USER If you want to force existing connections to be closed: ALTER DATABASE my_app_db SET RESTRICTED_USER WITH ROLLBACK ...


5

There are couple of ways that you can restrict access to a database : Using LOGON Trigger - but only temporarily (enable before upgrade and then disable it). Refer to my answer here for more details including a script. You can shutdown IIS on the webservers so that no connections are made using the application. This is called "Applicaiton downtime" Keep ...


3

Database roles are security principals that are wholly contained within their respective database and are not shared or visible to other databases. So any roles and users that are in database X have no knowledge of database Y. To accomplish your goal, you'll need to recreate the role in database Y and add all the appropriate users to this database and ...


3

I did something like this a long time ago. Yes it is possible. No it isn't too hard on the technical side. I don't think you will have problems in the areas you are asking about. Really this is pretty basic stuff once you figure out how acls are supposed to behave. The issue you will run into has to do with consistently enforcing the acls. It is very ...


3

You can get some information about the last change to a table with xmin, eg: select max(xmin::text::bigint) from t; But you need to be aware of many caveats including modulo and wraparound and frozen xids. testbed: set role dba; create role stack; grant stack to dba; create schema authorization stack; set role stack; -- create or replace function ...


3

Almost as soon as I posted the question, I saw the dumb mistake I made -- I forgot to include the permission to be granted in the acl entry. This entry is tested and works for users as owners, as well as groups and nested groups as owners (with a break-through so non-owners aren't rejected outright): to dn.children="dc=Groups,dc=example,dc=com" by ssf=128 ...


3

You might want to verify this in SQLPlus. If it still doesn't disconnect you then verify your assumptions by running the following after the trigger finishes: SELECT 'Check This' FROM dual WHERE sys_context('USERENV','SESSION_USER') = 'xx'; SELECT 'Check This' FROM dual WHERE sys_context('USERENV','IP_ADDRESS') <> '10.0.30.219'; SELECT 'Check This' ...


2

From this answer to a similar question on ServerFault: The trigger will not deny access if A member of the DBA group can't be disconnected with an ON LOGON trigger. The owner of the trigger is the user logging in. The user has the privilege Administer Database Trigger


2

A vanilla PostgreSQL installation does not log access to tables. If you need that you have to implement it yourself. I would use triggers for that. I use a setup like this for many of my tables. I add a column named log_up to tables I want to track updates for: log_up timestamp(0) without time zone DEFAULT (now())::timestamp(0) without time zone ...


2

You can use the function fn_my_permissions and impersonate the user to see which objects they have access to. You may want to expand the types to procedures and the various types of functions... USE Your_Database; GO EXECUTE AS USER = N'the_user_name'; GO SELECT SCHEMA_NAME(o.[schema_id]), o.name, p.[permission_name] FROM sys.objects AS o ...


2

Short version: SELECT a.oid FROM pg_authid a WHERE pg_has_role('maxwell', a.oid, 'member'); Here we use a version of pg_has_role that takes a role name as the subject and role oid to test for membership, passing member mode so we test for inherited memberships. The advantage of using pg_has_role is that it uses PostgreSQL's internal caches of role ...


2

You can query the system catalog with a recursive query, in particular pg_auth_members: WITH RECURSIVE cte AS ( SELECT oid FROM pg_roles WHERE rolname = 'maxwell' UNION ALL SELECT m.roleid FROM cte JOIN pg_auth_members m ON m.member = cte.oid ) SELECT oid FROM cte; BTW, INHERIT is the default behavior of CREATE ROLE and doesn't have to ...


2

I don't know MongoDB too well and I assume you have you seen the information set out on the MongoDB System Users page as there would seem to be one or two differences across different DB mangement procedures. As a suggestion have you set the database you wish to work on/from by adding the 'use ....' before the command. It would seem that you might try:- ...


1

If you don't want to specify the schema explicitly, you can choose one of several options, for example: Modify the default schema in the session, e.g. by issuing the statement SET CURRENT SCHEMA USERX. Create an alias for the table USERX.MYTABLE in the schema MYUSER. Before importing data, edit the file db2move.lst and replace all occurrences of USERX with ...


1

Like most things it's all about trade-offs. You can denormalize, maintain materialized views, etc, but all that has its own costs. It really depends on workload, query patterns, response time priorities, maintenance burden you're willing to bear, etc. In general I'd recommend starting with the properly normalized design and join chain, but doing it via ...


1

Suggest you not to remove people but disable their login or just revoke access. Then after a full business cycle, if no one complains then remove those users. Is there a way to find out who has accessed certain reports during past month? Below is the query that will give the report you need. There are more report queries available at this link : ...


1

It depends upon how sophisticated you want to be with your user management. In MySQL I would create a small selection of "temporary" users with no privileges, not even to log in. When a user requests access to some part of the database, I would have a script run GRANT statements to allow them access to the resources they need, and have a cron (or other ...



Only top voted, non community-wiki answers of a minimum length are eligible