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

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 ...


3

If you work with Windows Authentication, you can add your domain users to a domain group and add this group as a login to SQL Server. Then give that login the desired permissions on the relevant databases.


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

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' ...


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 ...


2

I will add though that allowing root from remote clients (especially allowing root from '%' ) is VERY BAD security wise


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

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

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

For MySQL in addition to the GRANT command, there is also a REVOKE command. However, REVOKE has limitations. If you GRANT ALL ON mydb.* TO 'someuser'@'somehost' you cannot remove one table by running REVOKE ALL ON mydb.mytbl3 TO 'someuser'@'somehost'. This is because REVOKE will only remove rights as they are granted. That is, you could: REVOKE ALL ON ...


2

If you were using SQL Server, what you are looking for is something like: USE tempdb; CREATE TABLE MenuHeaders ( MenuHeaderID INT NOT NULL CONSTRAINT PK_MenuHeader PRIMARY KEY CLUSTERED IDENTITY(1,1) , MenuText VARCHAR(255) ); CREATE TABLE MenuSubHeaders ( MenuSubHeaderID INT NOT NULL CONSTRAINT PK_MenuSubHeader PRIMARY KEY ...


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

Google's MySQL tools provide support for this: https://code.google.com/p/google-mysql-tools/source/browse/#svn%2Ftrunk%2Fpermissions_lib It's a really complex solution; you have to transform your SQL permissions into a domain-specific Python language, but then it offers flexibility like revoking databases/tables/columns, templating, etc. You run the Python ...


1

I've had to solve this specific problem and realized there's not way to say "all tables except these". What I did to work around this was create an internal database, restricted_tables. It had a couple of simple table schema: CREATE TABLE `tables` ( `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', `TABLE_NAME` varchar(64) NOT NULL DEFAULT '' ) ...


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

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

If you have a set group of users that would access the database or just one or two application accounts that make the database connection you could simply disable the login(s) and kill all connections, and then just re-enable the login(s) when you are done.


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

You have to run the FLUSH PRIVILEGES; command.


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